我的一個(gè)朋友 ，sqlServer MVP寫過一篇很棒的文章關(guān)于提醒人們?cè)谥孛鎯?chǔ)過程,視圖，函數(shù)等應(yīng)注意的問題。文章地址? 這里 ：
?
我很奇怪為什么我在using Sql Server Management Studio 重命名存儲(chǔ)過程去能看到正確的定義。我做了一些調(diào)查研究，發(fā)現(xiàn)如下。
?創(chuàng)建一個(gè)存儲(chǔ)過程
1.CREATE PROCEDURE TestProc
2.AS
3.SELECT 'Hello'
4.GO
現(xiàn)在在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.*/
這表明這個(gè)存儲(chǔ)過程對(duì)象重命名成功，我們來查看一下這個(gè)改過的存儲(chǔ)過程的文本。
1.sp_helptext NewTestProc
2./*
3.Text
4.---------------------------
5.CREATE PROCEDURE TestProc
6.AS
7.SELECT 'Hello'
8.*/
注意代碼塊中存儲(chǔ)過程的名字，依然是“Test Proc”? 另外一種查看存儲(chǔ)過程，視圖，觸發(fā)器定義的方法是使用OBJECT_DEFINITION()函數(shù)
1.SELECT OBJECT_DEFINITION(OBJECT_ID('NewTestProc')) AS body
2./*
3.body
4.------------------------------------------------------------
5.CREATE PROCEDURE TestProc
6.AS
7.SELECT 'Hello'
8.*/
結(jié)果是一樣的，我們直接從系統(tǒng)的目錄視圖看看
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.*/
那么,如果存儲(chǔ)過程代碼提中的名字沒有更新的話，SQl Server怎么會(huì)執(zhí)行正確的存儲(chǔ)過程呢？ 我們看到字系統(tǒng)元數(shù)據(jù)中過程的名字已經(jīng)改了，但是存儲(chǔ)過程的定義卻沒有改。，當(dāng)執(zhí)行存儲(chǔ)過程的時(shí)候，Sql Server會(huì)首先找到存儲(chǔ)過程的對(duì)象ID，然后用對(duì)象ID 得到代碼體，然后執(zhí)行代碼體中的定義。
重明明過程中問題在哪？
?我們看到重命名存儲(chǔ)過程，視圖，函數(shù)之后，Sql Server能夠正確識(shí)別和執(zhí)行存儲(chǔ)過程的定義，那么問題在哪？
我看到這種方法的一個(gè)問題。我身邊大部分的開發(fā)人員使用Management Studio 去修改存儲(chǔ)過程 視圖等。在management studio中找到修改的對(duì)象，點(diǎn)擊右鍵選擇“Modify”或則“Generate alter script” 菜單，在一個(gè)新的查詢窗口中修改然后執(zhí)行保存。
但是在更早之前，我遇到一些開發(fā)人員，他們不愿意是用management studio 來做更改，他們則用sp_helptext 來快速獲取對(duì)象的代碼體，修改以及執(zhí)行保存等。我覺得采用這種方式應(yīng)該很小心，因?yàn)檫@樣修改保存以后新的存儲(chǔ)過程不會(huì)被更新，取而代之的是，一個(gè)新的存儲(chǔ)過程會(huì)被創(chuàng)建，他的名字是原來存儲(chǔ)過程的名稱，代碼體卻是新的定義。
?
幾年前，我經(jīng)常跟人說不要使用sp_helptext去編輯修改存儲(chǔ)過程，視圖等對(duì)象， 其原因是， 在SQL Server 2000 的版本 sp_helptext的 TSQL 代碼 沒有保留 格式 ， 所以 后 ， 你將失去 所有 的格式 作出 修改 等。 但是 ， 在SQL Server 2005 的版本 sp_helptext的能夠 保存 格式 ， 所以我 不再堅(jiān)持 人 不 使用sp_helptext 。
但 現(xiàn)在我有 更多的理由 ， 建議不使用sp_helptext 進(jìn)行修改 的 過程，視圖 ， 觸發(fā)器和函數(shù) 等.
Management Studio 的處理過程
調(diào)查Management Studio 是處理過程很有意思，如果使用management studio生成存儲(chǔ)過程的腳本能夠得到正確的定義，可以使用“Modify”選項(xiàng)生成“create/alter”腳本
注意，生成的腳本包含正確的存儲(chǔ)過程的名稱
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是如何獲取正確的對(duì)象定義的？我們用Sqlserver Profiler 分析器來看一下當(dāng)我們?cè)趍anagement studio中點(diǎn)擊“Modify” 生成腳本時(shí)候執(zhí)行什么樣的sql語句。
你會(huì)發(fā)現(xiàn)SSMS 執(zhí)行了如下的查詢語句去獲取對(duì)象的定義
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很智能修改了定義呈現(xiàn)給我們，還有一點(diǎn)有意思的是，在SSMS中保存ALTER PROCEDURE的腳本后，對(duì)象的名稱得到了更新
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'
執(zhí)行上述更新代碼，再次執(zhí)行sp_helptext時(shí)就會(huì)獲得新的對(duì)象定義。
1.sp_helptext NewTestProc
2./*
3.Text
4.---------------------------------------
5.CREATE PROCEDURE [dbo].[NewTestProc]
6.AS
7.SELECT 'Hello'
8.*/
可以看到執(zhí)行的更新腳本后，存儲(chǔ)過程的名稱在對(duì)象定義中也得到更新。 因此，不推薦去重命名存儲(chǔ)過程，視圖，觸發(fā)器等對(duì)象。如果我想需要改名的話，把原先的刪掉再重新創(chuàng)建。如果這個(gè)對(duì)象已經(jīng)被復(fù)制了，是不允許刪除重建的，否者會(huì)遇到下列錯(cuò)誤
1.Msg 3724, Level 16, State 2, Line 1
2.Cannot drop the procedure 'NewTestProc' because it is being used for replication.
遇到這種情況，可以采用如下兩種辦法
1 從 文章 中 刪除該 對(duì)象 ， 創(chuàng)建它 并將其重新添加 到 復(fù)制 文章 列表 。
2 使用sp_rename 重 命名 對(duì)象 。 重命名 對(duì)象 后 ， 從 Management Studio 中 生成 ALTER 腳本 并執(zhí)行它 ，以確保 過程 ， 觸發(fā)器 ， 函數(shù) ， 視圖 等 對(duì)象的名稱 正確 更新
總結(jié)
1 避免重命名 對(duì)象 。
2 如果你想 重命名對(duì)象 ， 刪除該對(duì)象 ， 然后重新創(chuàng)建。
3. 不要 使用sp_helptext 檢索對(duì)象 的定義和 修改 。
4. 只有當(dāng)對(duì)象未被復(fù)制時(shí)， 使用 SP_RENAME 重命名對(duì)象 。 在這種 情況 下 ， 你 重命名 對(duì)象 后 ， 生成 ALTER 腳本 從SSMS 執(zhí)行 一次 ，以確保 對(duì)象名稱 正確 更新 的對(duì)象 體 。
?
?
?
?
?
?
?
?
更多文章、技術(shù)交流、商務(wù)合作、聯(lián)系博主
微信掃碼或搜索：z360901061
微信掃一掃加我為好友
QQ號(hào)聯(lián)系： 360901061
您的支持是博主寫作最大的動(dòng)力，如果您喜歡我的文章，感覺我的文章對(duì)您有幫助，請(qǐng)用微信掃描下面二維碼支持博主2元、5元、10元、20元等您想捐的金額吧，狠狠點(diǎn)擊下面給點(diǎn)支持吧，站長非常感激您！手機(jī)微信長按不能支付解決辦法：請(qǐng)將微信支付二維碼保存到相冊(cè)，切換到微信，然后點(diǎn)擊微信右上角掃一掃功能，選擇支付二維碼完成支付。
【本文對(duì)您有幫助就好】元
