Notes for renaming database stored procedures, functions, views, and triggers

One of my friends, MVP, wrote a great article about reminding people to pay attention to stored procedures, views, functions, and other issues. Article address
Here:

 

I am wondering why I can see the correct definition in the rename stored procedure in using SQL Server Management studio. I did some research and found that.

Create a stored procedure

1.CREATE PROCEDURE TestProc 2.AS3.SELECT 'Hello'4.GO

Now rename it "newtestproc" in manage studio ".

Check whether the name is successfully updated.

1.sp_helptext TestProc 2./* 3.Msg 15009, Level 16, State 1, Procedure sp_helptext, Line 54 4.The object 'TestProc' does not exist in database 'xmlworkshop' 5.or is invalid for this operation. 6.*/

This indicates that the Stored Procedure object is successfully renamed. Let's check the text of the modified stored procedure.

1.sp_helptext NewTestProc 2./* 3.Text 4.--------------------------- 5.CREATE PROCEDURE TestProc 6.AS7.SELECT 'Hello'8.*/

Note that the name of the stored procedure in the code block is still "test proc". Another way to view the stored procedure, view, and trigger definition is to use the object_definition () function.

1.SELECT OBJECT_DEFINITION(OBJECT_ID('NewTestProc')) AS body 2./* 3.body 4.------------------------------------------------------------ 5.CREATE PROCEDURE TestProc 6.AS7.SELECT 'Hello'8.*/

The results are the same. Let's look at the directory view of the system.

01.SELECT text FROM sys.syscomments 02.WHERE id = OBJECT_ID('NewTestProc') 03./* 04.text 05.------------------------------------- 06.CREATE PROCEDURE TestProc 07.AS08.SELECT 'Hello'09.*/

So, if the name in the Stored Procedure Code is not updated, how can SQL server execute the correct stored procedure? We can see that the name of the process in the metadata of the word system has been changed, but the definition of the stored procedure has not been changed ., When executing a stored procedure, SQL Server first finds the Object ID of the stored procedure, obtains the code body with the Object ID, and then executes the definition in the Code body.

What are the problems in the process of re-identification?

We can see that after renaming stored procedures, views, and functions, SQL Server can correctly identify and execute the definition of stored procedures. What is the problem?
I see a problem with this method. Most developers around me use management studio to modify the Stored Procedure view. Find the modified object in management studio, right-click and choose "modify" or "generate alter script", modify the modification in a new query window, and save the modification.

But earlier, I met some developers who didn't want to use management studio to make changes. They used sp_helptext to quickly obtain the object's code body, modify and save the object. I think we should be very careful when using this method, because the new stored procedure will not be updated after modification and storage. Instead, a new stored procedure will be created, his name is the name of the original stored procedure, but the code body is a new definition.

 

A few years ago, I often told people not to use sp_helptext to edit stored procedures, views, and other objects. The reason is that the tsql code of sp_helptext in SQL Server 2000 does not retain the format, therefore, you will lose all the formats to make modifications and so on. However
In Server 2005, sp_helptext can be saved in the format, so I do not insist that people do not use sp_helptext.

But now I have more reasons. We recommend that you do not use sp_helptext to modify the process, views, triggers and functions.

Process of Management Studio

Investigating that management studio is a processing process is very interesting. If the script for generating a stored procedure using management studio can be correctly defined, you can use the "modify" option to generate the "Create/alter" Script

Note that the generated script contains the correct name of the stored procedure

01.USE [xmlworkshop] 02.GO 03./****** Object:  StoredProcedure [dbo].[NewTestProc]     04.Script Date: 09/28/2008 11:56:31 ******/ 05.SET ANSI_NULLS ON06.GO 07.SET QUOTED_IDENTIFIER ON08.GO 09.ALTER PROCEDURE [dbo].[NewTestProc] 10.AS11.SELECT 'Hello'

How does management studio obtain the correct object definition? We use the sqlserver profiler analyzer to see what SQL statements are executed when we click "modify" in management studio to generate the script.

You will find that SSMs executes the following query statement to get the object definition.

01.exec sp_executesql N'SELECT 02.ISNULL(smsp.definition, ssmsp.definition) AS [Definition] 03.FROM 04.sys.all_objects AS sp 05.LEFT OUTER JOIN sys.sql_modules AS smsp ON smsp.object_id = sp.object_id 06.LEFT OUTER JOIN sys.system_sql_modules AS ssmsp ON ssmsp.object_id = sp.object_id 07.WHERE 08.(sp.type = @_msparam_0 OR sp.type = @_msparam_1 OR sp.type=@_msparam_2) 09.and(sp.name=@_msparam_3  10.and SCHEMA_NAME(sp.schema_id)=@_msparam_4)'11.,N'@_msparam_0 nvarchar(4000),@_msparam_1 nvarchar(4000), 12.@_msparam_2 nvarchar(4000),@_msparam_3 nvarchar(4000),@_msparam_4 nvarchar(4000)', 13.@_msparam_0=N'P',@_msparam_1=N'RF',@_msparam_2=N'PC', 14.@_msparam_3=N'NewTestProc',@_msparam_4=N'dbo'15.  16./* 17.Definition 18.------------------------------- 19.CREATE PROCEDURE TestProc 20.AS21.SELECT 'Hello'22.*/

, Modify
We can see that SSMs finds the same definition (old name), but management studio intelligently modifies the definition and presents it to us. What's more interesting is that, after saving the alter procedure script in SSMs, the object name is updated.

01.USE [xmlworkshop] 02.GO 03./****** Object:  StoredProcedure [dbo].[NewTestProc]     04.Script Date: 09/28/2008 11:56:31 ******/ 05.SET ANSI_NULLS ON06.GO 07.SET QUOTED_IDENTIFIER ON08.GO 09.ALTER PROCEDURE [dbo].[NewTestProc] 10.AS11.SELECT 'Hello'

Execute the above update code. When execute sp_helptext again, the new object definition will be obtained.

1.sp_helptext NewTestProc 2./* 3.Text 4.--------------------------------------- 5.CREATE PROCEDURE [dbo].[NewTestProc] 6.AS7.SELECT 'Hello'8.*/

After the updated script is executed, the name of the stored procedure is also updated in the object definition. Therefore, it is not recommended to rename stored procedures, views, triggers, and other objects. If you want to change the name, delete the original one and recreate it. If the object has been copied, it cannot be deleted or recreated. Otherwise, the following error occurs:

1.Msg 3724, Level 16, State 2, Line 1 2.Cannot drop the procedure 'NewTestProc' because it is being used for replication.

In this case, you can use the following two methods:

1. Delete the object from the article, create it, and add it to the copy article list.

2. Use sp_rename to rename the object. After Renaming an object
Studio generates and executes the alter script to ensure that the names of objects such as processes, triggers, functions, and views are correctly updated.

Summary

1. Avoid renaming objects.

2. If you want to rename an object, delete the object and recreate it.

3. Do not use sp_helptext to retrieve object definitions and modifications.

4. Use sp_rename to rename an object only when the object is not copied. In this case, after renaming an object, you generate an alter script to execute it once from SSMs to ensure that the object name is correctly updated.