重命名数据库存储过程/函数/视图/触发器应注意的问题

我的一个朋友 ，sqlServer MVP写过一篇很棒的文章关于提醒人们在重命名存储过程,视图，函数等应注意的问题。文章地址  这里：

 

我很奇怪为什么我在using Sql Server Management Studio 重命名存储过程去能看到正确的定义。我做了一些调查研究，发现如下。

 创建一个存储过程

1.CREATE PROCEDURE TestProc 
2.AS
3.SELECT 'Hello'
4.GO

现在在manage studio 中把它改名为 "NewTestProc".

看一下名字是否更新成功

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.*/

这表明这个存储过程对象重命名成功，我们来查看一下这个改过的存储过程的文本。

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

注意代码块中存储过程的名字，依然是“Test Proc”  另外一种查看存储过程，视图，触发器定义的方法是使用OBJECT_DEFINITION()函数

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

结果是一样的，我们直接从系统的目录视图看看

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

那么,如果存储过程代码提中的名字没有更新的话，SQl Server怎么会执行正确的存储过程呢？ 我们看到字系统元数据中过程的名字已经改了，但是存储过程的定义却没有改。，当执行存储过程的时候，Sql Server会首先找到存储过程的对象ID，然后用对象ID 得到代码体，然后执行代码体中的定义。

重明明过程中问题在哪？

 我们看到重命名存储过程，视图，函数之后，Sql Server能够正确识别和执行存储过程的定义，那么问题在哪？
我看到这种方法的一个问题。我身边大部分的开发人员使用Management Studio 去修改存储过程 视图等。在management studio中找到修改的对象，点击右键选择“Modify”或则“Generate alter script” 菜单，在一个新的查询窗口中修改然后执行保存。

但是在更早之前，我遇到一些开发人员，他们不愿意是用management studio 来做更改，他们则用sp_helptext 来快速获取对象的代码体，修改以及执行保存等。我觉得采用这种方式应该很小心，因为这样修改保存以后新的存储过程不会被更新，取而代之的是，一个新的存储过程会被创建，他的名字是原来存储过程的名称，代码体却是新的定义。

 

几年前，我经常跟人说不要使用sp_helptext去编辑修改存储过程，视图等对象，其原因是，在SQL Server 2000的版本sp_helptext的TSQL代码没有保留格式，所以后，你将失去所有的格式作出修改等。但是，在SQL Server 2005的版本sp_helptext的能够保存格式，所以我不再坚持人不使用sp_helptext。

但现在我有更多的理由，建议不使用sp_helptext进行修改的过程，视图，触发器和函数等.

Management Studio 的处理过程

调查Management Studio 是处理过程很有意思，如果使用management studio生成存储过程的脚本能够得到正确的定义，可以使用“Modify”选项生成“create/alter”脚本

注意，生成的脚本包含正确的存储过程的名称

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

management studio是如何获取正确的对象定义的？我们用Sqlserver Profiler 分析器来看一下当我们在management studio中点击“Modify” 生成脚本时候执行什么样的sql语句。

你会发现SSMS 执行了如下的查询语句去获取对象的定义

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.AS
21.SELECT 'Hello'
22.*/

，修改
可以看到SSMS找到相同的定义(旧的名称)，但是management studio很智能修改了定义呈现给我们，还有一点有意思的是，在SSMS中保存ALTER PROCEDURE的脚本后，对象的名称得到了更新

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

执行上述更新代码，再次执行sp_helptext时就会获得新的对象定义。

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

可以看到执行的更新脚本后，存储过程的名称在对象定义中也得到更新。 因此，不推荐去重命名存储过程，视图，触发器等对象。如果我想需要改名的话，把原先的删掉再重新创建。如果这个对象已经被复制了，是不允许删除重建的，否者会遇到下列错误

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

遇到这种情况，可以采用如下两种办法

1从文章中删除该对象，创建它并将其重新添加到复制文章列表。

2 使用sp_rename重命名对象。重命名对象后，从Management Studio中生成ALTER脚本并执行它，以确保过程，触发器，函数，视图等对象的名称正确更新

总结

1 避免重命名对象。

2 如果你想重命名对象，删除该对象，然后重新创建。

3.不要使用sp_helptext检索对象的定义和修改。

4. 只有当对象未被复制时，使用SP_RENAME重命名对象。在这种情况下，你重命名对象后，生成ALTER脚本从SSMS执行一次，以确保对象名称正确更新的对象体。