cancel
Showing results for 
Search instead for 
Did you mean: 

Re-Resolution of procedures

Former Member
0 Kudos

We've come across a couple of instances where we've had to reload a number of stored procedures even though there has been no change to the code.

Does Sybase have any method of forcing a re-resolution of a procedure ?

Use-case 1: We had a corrupt table which we couldn't drop. We renamed the table and created a new one to replace it.

We then had to hunt around all the procedures across many databases to find any procedures referring to the table and reload them.

Use-case 2: We have some debug statements if @debug =1 select * from table.

When we change the structure of the table we then have to reload all the procs. No we can't easily drop and re-create the table - too much data.

In both cases, we just want to force a re-resolution of the procs rather than we load all the procs. I'd rather not take our production system down just to spend time reloading a large number of procs. Its also concerning that its not always easy to ensure you can find every procedure that accesses a table if its in another database.

Is there any way of doing this ?

Any features to do this in the pipeline ?

Accepted Solutions (0)

Answers (1)

Answers (1)

former_member188958
Active Contributor
0 Kudos

You can use either DBCC UPGRADE_OBJECT or EXECUTE ... WITH UPGRADE to force procedure query trees to be rebuilt (including object resolution) from the source text in syscomments.   The DBCC does not actually execute the procedure, while EXECUTE WITH UPGRADE does.   The DBCC can be used to rebuild all procedures in a database with a single command.

dbcc upgrade_object

Syntax Examples for Upgrading Compiled Objects