|
重命名数据库存储过程/函数/视图/触发器应注意的问题
我很奇怪为什么我在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执行一次，以确保对象名称正确更新的对象体。
|
