Renaming stored procedures, triggers, user-defined functions, or views

Could you explain why this is an issue? Can't you just drop the existing ones and create the new ones with a different name?

Thanks,

Sivakumar Rangasamy wrote:

If we use sp_rename means, it'll not affect the syscomments table..

Are you referring to the text column in sys.syscomments? By design, the name of the object will not get updated in the text column if it is renamed because the text column returns the actual, original text of the SQL definition statement. The text (and name of the object) will only get updated if you drop and then recreate the object. This is the same behavior as the definition column of the sys.sql_modules catalog view.

SQL Server maintains references in multiple forms:

1. By id (FK constraint, schema binding reference, index to table, table to trigger, view to base tables etc)
2. By name (SP reference (non-schema bound), function reference (non-schema bound), 3/4-part names etc). This type of non-schema bound reference is also tracked loosely by SQL Server and not enforced.

Now, when you use sp_rename we only change the name of the entity (table, view, module, constraint etc) in the metadata. We do not change the text that provides the definition for the object. This is because for compilation purposes like SPs or functions or triggers, the name in the text doesn't matter and it is just part of valid syntax for the CREATE PROCEDURE/FUNCTION/TRIGGER statement. The name is stored separately from the actual definition of the object and that is what gets used. The name in the definition is parsed at create time and that is pretty much it.

So when you modify the name of a SP you will have to go and fix the other modules that reference it. SQL Server cannot do this by itself because there is logical dependency that will be too expensive to evaluate and modify. And in cases where dynamic SQL is used it is not possible at all due to the runtime nature and control of flow logic or unknown values of variables.

SQL Server 2008 however has new object dependency reporting features that will help you identify most of the references easily. Start with the following topic in the new BOL:

http://msdn2.microsoft.com/en-us/library/bb677168(SQL.100).aspx

Understanding dependencies topic:
http://msdn2.microsoft.com/en-us/library/ms345449(SQL.100).aspx