我的一个朋友 ,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执行一次,以确保对象名称正确更新的对象体。