PRB: Renaming Stored Procedure, View or Trigger Does Not Update SYSCOMMENTS Table

ID: Q243198

The information in this article applies to:

Microsoft SQL Server versions 6.5, 7.0

SYMPTOMS

Renaming a Stored Procedure, View or Trigger object will not change the name of the corresponding Stored Procedure, View or Trigger object in the SYSCOMMENTS table. This may cause problems when a script is generated for the renamed object. The script for the renamed object is generated with the old name of the object in the CREATE statement.

For example, you can rename a stored procedure from the Enterprise Manager or SQL Server Query Analyzer with these steps:

From Enterprise Manager, expand the database folder and select Stored Procedures. Right-click the stored procedure and then select RENAME.

From SQL Server Query Analyzer, select the stored procedure's database name in the database window and type in sp_rename 'old name','new name'.

A renamed Stored Procedure, View or Trigger is upgraded to SQL Server 7.0 with the old name of the object. The Upgrade Wizard moves SQL Server 6.x databases to SQL Server 7.0. An upgrade script is created based on the syscomments entries in the 6.x database. It is recommended that you drop and recreate objects that have been renamed prior to upgrading SQL Server to version 7.0. An example of the warning you receive during the SQL Server 6.5 to SQL Server 7.0 upgrade process follows:


=============================================
The following syscomments entries are invalid on your 6.x SQL Server. It is
recommended that you fix these problems before you continue.
=============================================
********************************
mydb
********************************
********************************
Inconsistency Report For Database: mydb.
********************************

dbo.renamedmytest

Procedure

Object Renamed: Text does not match sysobjects.
--------------------------------

CAUSE

Internally, SQL Server uses the object_id, which remains the same even if the name of the object has changed through RENAME.

WORKAROUND

Do not rename Stored Procedures, Views, or Triggers. Instead, create a script of the existing object, change the name of the object in the CREATE statement of the script, and save the script. The script should now have a DROP statement with the old object name to be dropped. The script should also have the CREATE statement with the new object name. Run the script to create the object with the new name.

MORE INFORMATION

The CREATE statement for a renamed Stored Procedure or View can be accessed by double-clicking the renamed Stored Procedure or View in Microsoft SQL Server Enterprise Manager. To generate a script of the Stored Procedure, View or Trigger, right-click the database where the renamed Stored Procedure, View or Trigger was created and select ALL TASKS\GENERATE SQL SCRIPTS. The generated SQL script will have a CREATE statement with the previous name of the Stored Procedure, View or Trigger.

For example, create a stored procedure as follows:


CREATE PROCEDURE myrenametest AS
select * from pubs..authors

Now rename the stored procedure to "renamedmytest" by right-clicking the stored procedure and selecting RENAME or by using sp_rename 'myrenametest','renamedmytest'.

Next, double-click the stored procedure "renamedmytest" and note that the old stored procedure name still appears in the CREATE PROCEDURE statement.


CREATE PROCEDURE myrenametest AS
select * from pubs..authors

The stored procedure runs in a query window using the new name (exec renamedmytest). If you right-click the database where the renamed stored procedure was created and select ALL TASKS\GENERATE SQL SCRIPTS to generate a script of the renamed stored procedure, the CREATE PROCEDURE statement will have the old procedure name "myrenametest" in the CREATE PROCEDURE statement generated. For example:


CREATE PROCEDURE myrenametest AS
select * from pubs..authors

Additional query words:

Keywords : SSrvStProc kbSQLServ650 kbSQLServ700
Version : winnt:6.5,7.0
Platform : winnt
Issue type : kbprb