搜尋本站文章

2013-03-28

使用 sp_refreshsqlmodule 更新資料庫物件的中繼資料


示範版本:SQL Server 2012

sp_refreshsqlmodule

針對目前資料庫中指定的非結構描述繫結預存程序、使用者自訂函數、檢視、DML 觸發程序、資料庫層級 DDL 觸發程序或伺服器層級 DDL 觸發程序,更新其中繼資料。

 這些物件的基礎物件變更之後,其保存中繼資料也可能會過期,例如參數的資料類型。

sp_refreshsqlmodule 應在模組的基礎物件變更而影響其定義時執行。

否則,在查詢或叫用模組時,可能會產生非預期的結果。

若要重新整理檢視,您可以使用 sp_refreshsqlmodule 或 sp_refreshview 來重新整理相同的結果。

sp_refreshsqlmodule 不會影響與物件相關聯的任何權限、擴充屬性或 SET 選項。

若要重新整理伺服器層級 DDL 觸發程序,請從任何資料庫的內容執行此預存程序。

當您執行 sp_refreshsqlmodule 時,會卸除與物件相關聯的任何簽章。




EX1. 重新整理使用者預存預存程序的中繼資料

下列範例會重新整理使用者預存預存程序的中繼資料。

這個範例會建立別名資料類型:uid,以及使用 uid 的預存程序:usp_udt01。
接著,uid 會重新命名為 uid_old,而且會建立有不同定義的新 uid。

所以,需要對 預存程序:usp_udt01 執行中繼資料的重新整理,以參考 uid 的新實作,而非舊實作。



-- 01_建立「別名資料類型(alias data type)」:uid,其資料類型是:char(3)。
USE Northwind_Dev
GO
IF  EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[usp_udt01]') AND type in (N'P', N'PC'))
DROP PROCEDURE [dbo].[usp_udt01]
GO
IF  EXISTS (SELECT * FROM sys.types st JOIN sys.schemas ss ON st.schema_id = ss.schema_id WHERE st.name = N'uid' AND ss.name = N'dbo')
DROP TYPE [dbo].[uid]
GO
CREATE TYPE [dbo].[uid] FROM [char](3) NOT NULL
GO


-- 圖01_建立「別名資料類型(alias data type)」



-- 02 僅查詢:別名資料類型、系統資料類型、最大長度(Byte)以及字元數

SELECT ty1.name N'資料類型', ty2.name N'系統資料類型',ty1.max_length N'資料行的最大長度(Byte)',
 CAST(CASE WHEN ty2.name IN (N'nchar', N'nvarchar') AND ty1.max_length <> -1 
  THEN ty1.max_length/2 ELSE ty1.max_length END AS int) N'字元數'
FROM sys.types ty1 LEFT OUTER JOIN sys.types ty2
ON (ty2.user_type_id = ty1.system_type_id and ty2.user_type_id = ty2.system_type_id) or ((ty2.system_type_id = ty1.system_type_id) 
 and (ty2.user_type_id = ty1.user_type_id) and (ty2.is_user_defined = 0) and (ty2.is_assembly_type = 1)) 
WHERE ty1.is_user_defined=1
GO


-- 圖02_查詢:別名資料類型、系統資料類型、最大長度(Byte)以及字元數



-- 03_建立預存程序:usp_udt01,使用此「別名資料類型」。

IF  EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[usp_udt01]') AND type in (N'P', N'PC'))
DROP PROCEDURE [dbo].[usp_udt01]
GO
CREATE PROCEDURE usp_udt01
 @cid uid 
AS
 SELECT @cid AS N'別名資料類型'
GO

-- 04_執行此預存程序:usp_udt01,輸入的資料長度是 3 個字元

EXEC usp_udt01 'A12'
GO


-- 圖03_執行此預存程序:usp_udt01



-- 05_若嘗試刪除此「別名資料類型」:uid,會遭遇到以下的錯誤訊息:

IF  EXISTS (SELECT * FROM sys.types st JOIN sys.schemas ss ON st.schema_id = ss.schema_id WHERE st.name = N'uid' AND ss.name = N'dbo')
DROP TYPE [dbo].[uid]
GO

/*
錯誤訊息:

訊息 3732,層級 16,狀態 1,行 2
無法卸除類型 'dbo.uid',因為物件 'usp_udt01' 正在參考它。可能還有參考此類型的其他物件。
*/


-- 圖04_若嘗試刪除此「別名資料類型」:uid,會遭遇到錯誤訊息



無法刪除已經被使用的「別名資料類型」:uid,但可以修改其名稱。

-- 06_先修改「別名資料類型」:uid的名稱為:uid_old

EXEC sp_rename @objname= 'uid', @newname = 'uid_old', @objtype= 'USERDATATYPE';
GO

/*
產生的訊息:

注意: 變更物件名稱的任何部分將會中斷指令碼和預存程序。
*/


-- 圖05_先修改「別名資料類型」:uid的名稱為:uid_old



-- 07_建立與原先同名的「別名資料類型(alias data type)」:uid,但其資料類型是:char(5)。

USE Northwind_Dev
GO
IF  EXISTS (SELECT * FROM sys.types st JOIN sys.schemas ss ON st.schema_id = ss.schema_id WHERE st.name = N'uid' AND ss.name = N'dbo')
DROP TYPE [dbo].[uid]
GO
CREATE TYPE [dbo].[uid] FROM [char](5) NOT NULL
GO

-- 08_再度查詢:別名資料類型、系統資料類型、最大長度(Byte)以及字元數

SELECT ty1.name N'資料類型', ty2.name N'系統資料類型',ty1.max_length N'資料行的最大長度(Byte)',
 CAST(CASE WHEN ty2.name IN (N'nchar', N'nvarchar') AND ty1.max_length <> -1 
  THEN ty1.max_length/2 ELSE ty1.max_length END AS int) N'字元數'
FROM sys.types ty1 LEFT OUTER JOIN sys.types ty2
ON (ty2.user_type_id = ty1.system_type_id and ty2.user_type_id = ty2.system_type_id) or ((ty2.system_type_id = ty1.system_type_id) 
 and (ty2.user_type_id = ty1.user_type_id) and (ty2.is_user_defined = 0) and (ty2.is_assembly_type = 1)) 
WHERE ty1.is_user_defined=1
GO


-- 圖06_有兩個別名資料類型



-- 查詢接受參數之物件的每個參數
SELECT *
FROM sys.parameters 

-- 08_檢查使用者預存程序:usp_udt01,卻是使用舊的「別名資料類型」:uid_old,其資料類型是:char(3)。
SELECT OBJECT_NAME(object_id) AS N'物件名稱', name AS N'參數名稱', TYPE_NAME(user_type_id) AS N'資料類型',
 max_length AS N'最大長度 (位元組)', precision AS N'有效位數', scale AS N'小數位數', is_output AS N'OUTPUT參數', 
 has_default_value AS N'是否有預設值', default_value AS N'預設值', is_readonly AS N'READONLY參數'
FROM sys.parameters 
ORDER BY 1 DESC
GO


-- 圖07_檢查使用者預存程序:usp_udt01,卻是使用舊的「別名資料類型」:uid_old,其資料類型是:char(3)



-- 圖08_使用 SSMS觀察使用者預存程序:usp_udt01,其系統資料沒有更新,仍是使用「別名資料類型」:uid_old,但其資料類型是:char(3)



-- 09_執行此預存程序:usp_udt01,輸入的資料長度是 5 個字元,遭遇錯誤

EXEC usp_udt01 'A12' -- 三個字元
GO
EXEC usp_udt01 'A1234' -- 五個字元
GO

/*
錯誤訊息:

訊息 496,層級 16,狀態 1,程序 usp_udt01,行 4
"@cid" 參數的類型和建立時的類型不同。請先卸除,再用兩段式類型名稱重新建立程序。或使用 sp_refreshsqlmodule 重新整理它的參數中繼資料。
訊息 496,層級 16,狀態 1,程序 usp_udt01,行 4
"@cid" 參數的類型和建立時的類型不同。請先卸除,再用兩段式類型名稱重新建立程序。或使用 sp_refreshsqlmodule 重新整理它的參數中繼資料。

*/


-- 圖09_輸入的資料長度是 3或是 5 個字元,遭遇錯誤



-- 10_使用 sys.sp_refreshsqlmodule 更新其中繼資料
EXEC sys.sp_refreshsqlmodule @name = 'dbo.usp_udt01' ;
GO

-- 11_檢查使用者預存程序:usp_udt01,已經改用新的「別名資料類型」:uid_old,其資料類型是:char(5)。
SELECT OBJECT_NAME(object_id) AS N'物件名稱', name AS N'參數名稱', TYPE_NAME(user_type_id) AS N'資料類型',
 max_length AS N'最大長度 (位元組)', precision AS N'有效位數', scale AS N'小數位數', is_output AS N'OUTPUT參數', 
 has_default_value AS N'是否有預設值', default_value AS N'預設值', is_readonly AS N'READONLY參數'
FROM sys.parameters 
ORDER BY 1 DESC
GO


-- 圖10_使用者預存程序:usp_udt01,已經改用新的「別名資料類型」



-- 圖11_使用 SSMS 觀察使用者預存程序:usp_udt01,已經改用新的資料類型:char(5)



-- 12_執行此預存程序:usp_udt01,輸入的資料長度是 5 個字元,正常執行

EXEC usp_udt01 'A1234'
GO

-- 圖12_執行此預存程序:usp_udt01,輸入的資料長度是 5 個字元,正常執行



-- 99_回復原狀

IF  EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[usp_udt01]') AND type in (N'P', N'PC'))
DROP PROCEDURE [dbo].[usp_udt01]
GO
IF  EXISTS (SELECT * FROM sys.types st JOIN sys.schemas ss ON st.schema_id = ss.schema_id WHERE st.name = N'uid' AND ss.name = N'dbo')
DROP TYPE [dbo].[uid]
GO
IF  EXISTS (SELECT * FROM sys.types st JOIN sys.schemas ss ON st.schema_id = ss.schema_id WHERE st.name = N'uid_old' AND ss.name = N'dbo')
DROP TYPE [dbo].[uid_old]
GO





參考資料


sp_refreshsqlmodule (Transact-SQL)
http://msdn.microsoft.com/zh-tw/library/bb326754.aspx

sp_refreshview (Transact-SQL)
http://msdn.microsoft.com/zh-tw/library/ms187821.aspx

sys.parameters (Transact-SQL)
http://msdn.microsoft.com/zh-tw/library/ms176074.aspx

2013-03-26

組態「操作員」來傳送給多個 Email 帳戶;Configuration operator to send to multiple Email


示範版本:SQL Server 2012。

若是要組態「操作員」來傳送給多個 Email 帳戶,可能使用的方法,例如:

1. 在 Mail 伺服器上,建立群組帳戶。

2. 在建立「操作員」時,利用「分號(semicolon)」來分隔多個電子郵件地址。




建立「操作員」,利用「分號(semicolon)」來分隔多個電子郵件地址

請參考以下的範例程式碼:

/*
sp_add_operator (Transact-SQL)

引數
[ @email_address= ] 'email_address'

操作員的電子郵件地址。這個字串會直接傳遞至電子郵件系統。 

email_address 是 nvarchar(100),預設值是 NULL。

您可以指定 email_address 的實體電子郵件地址或別名。
例如:'jdoe' 或 'jdoe@xyz.com'
*/

USE [msdb]
GO
EXEC msdb.dbo.sp_add_operator @name=N'MISGroup', 
  @enabled=1, 
  @pager_days=0, 
  @email_address=N'u99@mydba.com;u30@mydba.com'
GO


-- 圖01_組態「操作員」來傳送給多個 Email 帳戶







參考資料

sp_add_operator (Transact-SQL)
http://msdn.microsoft.com/zh-tw/library/ms186747.aspx

建立與組態 Database Mail,使用Transact-SQL為例 -- SQL Server 2012 版本
http://sharedderrick.blogspot.tw/2012/05/database-mailtransact-sql.html

實作練習:建立 Database Mail 與 SQL Agent Mail,以 SQL Server 2008 為例
http://sharedderrick.blogspot.tw/2011/01/database-mail-sql-agent-mail-sql-server.html

2013-03-25

SQL Server 預設可同時連接的使用者(user connections)數目上限是:32,767


示範版本:SQL Server 2012 版本。

在 SQL Server 執行個體上,可同時連接的使用者數目上限是:32,767



SQL Server 伺服器組態選項

user connections 選項會指定 SQL Server 執行個體上可同時連接的使用者數目上限。

實際允許的使用者連接數也取決於您所使用的 SQL Server 版本,以及應用程式的限制或應用程式和硬體的限制而定。

SQL Server 最多允許 32,767 個使用者連接。

因為 user connections 是動態的 (自我設定的) 選項,所以 SQL Server 會視需要自動調整最大使用者連接數,最多調整到允許的最大值。

例如,如果只有 10 個使用者登入,就配置 10 個使用者連線物件。

在大部分情況下,不需要變更這個選項的值。

預設值為 0,表示允許無限制的使用者連接。




EX1. 查詢 SQL Server  執行個體,可同時連接的使用者數目上限



-- EX1. 查詢 SQL Server  執行個體,可同時連接的使用者數目上限

-- 01_使用 sys.configurations
SELECT * FROM sys.configurations
WHERE name =N'user connections'
GO

-- 02_使用 sys.sp_configure
USE master;
GO
EXEC sp_configure 'show advanced option', '1';
GO
RECONFIGURE 
GO
EXEC sys.sp_configure N'user connections'
GO


-- 圖01_查詢 SQL Server 執行個體,可同時連接的使用者數目上限






EX2. 變更 SQL Server 執行個體,可同時連接的使用者數目上限

-- EX2. 變更 SQL Server  執行個體上可同時連接的使用者數目上限
-- 組態為可以接受:32,767 使用者數目

-- 01_先使用 sys.sp_configure 
USE master;
GO
EXEC sp_configure 'show advanced option', '1';
GO
RECONFIGURE 
GO
EXEC sys.sp_configure N'user connections', N'32767'
GO
RECONFIGURE WITH OVERRIDE
GO


-- 02_重新啟動此執行個體

執行個體必須重新啟動之後,設定才能生效。


-- 圖02_變更 SQL Server  執行個體,可同時連接的使用者數目上限



-- 圖03_SSMS2012_組態同時連接的使用者數目上限






刻意輸入超過同時連接的使用者數目之上限


/*
訊息 15129,層級 16,狀態 1,程序 sp_configure,行 161
'50000' 不是設定選項 'user connections' 的有效值。

Msg 15129, Level 16, State 1, Procedure sp_configure, Line 161
'50000' is not a valid value for configuration option 'user connections'.
*/


-- 圖04_刻意輸入超過同時連接的使用者數目之上限



經過測試:

SSMS 2012 管理工具,提供了防呆功能,無法輸入超過 32,767 的數值。




參考資料

SQL Server 2012 的產品規格
http://msdn.microsoft.com/zh-tw/library/ms143287.aspx

sp_configure (Transact-SQL)
http://msdn.microsoft.com/zh-tw/library/ms188787.aspx

伺服器組態選項
http://msdn.microsoft.com/zh-tw/library/ms189631.aspx

設定 user connections 伺服器組態選項
http://msdn.microsoft.com/zh-tw/library/ms187030.aspx

sys.configurations (Transact-SQL)
http://msdn.microsoft.com/zh-tw/library/ms188345.aspx

--


忘記 sa 密碼,也無法使用 Windows 認證的 Administrator 帳戶來登入 SQL Server。I forgot my SA password and cannot login as administrator using windows authentication.
http://sharedderrick.blogspot.tw/2009/11/sa-windows-administrator-sql-serveri.html


--

如何:利用 SQL Server Management Studio 使用專用管理員連接
http://msdn.microsoft.com/zh-tw/library/ms178068(v=sql.105).aspx

資料庫管理員的診斷連接
http://msdn.microsoft.com/zh-tw/library/ms189595.aspx

2013-03-21

如何移除「透明資料加密(Transparent Data Encryption,TDE)


示範版本:SQL Server 2012

如何移除「透明資料加密(Transparent Data Encryption,TDE)

(1) 如果資料庫經過加密,您必須先使用 ALTER DATABASE ... SET ENCRYPTION OFF 陳述式,先從資料庫移除加密。

(2) 等候解密完成,然後再使用 DROP DATABASE ENCRYPTION KEY  移除資料庫加密金鑰。




移除「透明資料加密(Transparent Data Encryption,TDE)

請參考以下的範例程式碼


-- 01_檢視關於資料庫加密狀態的資訊
/*
sys.dm_database_encryption_keys 
傳回關於資料庫加密狀態及其相關聯之資料庫加密金鑰的資訊。

--
資料行名稱:encryption_state 

指出資料庫已加密或未加密。

0 = 沒有資料庫加密金鑰存在,未加密
1 = 未加密
2 = 加密進行中
3 = 已加密
4 = 金鑰變更進行中
5 = 解密進行中
6 = 保護變更進行中 (正在變更用於加密資料庫加密金鑰的憑證或非對稱金鑰)。

資料行名稱:percent_complete
資料庫加密狀態變更的完成百分比。如果沒有狀態變更,這將會是 0。
*/
USE master
GO
SELECT DB_NAME(database_id) N'資料庫',encryption_state N'加密狀態',percent_complete N'完成百分比',
 key_algorithm N'演算法', key_length N'金鑰長度', encryptor_type N'加密程式的類型', encryptor_thumbprint N'加密程式的指模',create_date N'建立加密金鑰的日期'
FROM sys.dm_database_encryption_keys
GO


-- 圖01_檢視關於資料庫加密狀態的資訊



-- 02_先設定此資料庫移除加密功能
/*
如果資料庫經過加密,您必須使用 ALTER DATABASE 陳述式,先從資料庫移除加密。
等候解密完成,然後再移除資料庫加密金鑰。

ALTER DATABASE SET 選項

ENCRYPTION {ON | OFF}
設定資料庫要加密 (ON) 或是不要加密 (OFF)。 
在資料庫層級啟用加密時,所有的檔案群組都會加密。 任何新的檔案群組都會繼承加密的屬性。 如果資料庫內有任何檔案群組設定為 READ ONLY,則資料庫加密作業將會失敗。
*/

ALTER DATABASE Northwind_TDE 
SET ENCRYPTION OFF
GO

-- 03_移除資料庫加密功能後,檢視關於資料庫加密狀態的資訊
USE master
GO
SELECT DB_NAME(database_id) N'資料庫',encryption_state N'加密狀態',percent_complete N'完成百分比',
 key_algorithm N'演算法', key_length N'金鑰長度', encryptor_type N'加密程式的類型', encryptor_thumbprint N'加密程式的指模',create_date N'建立加密金鑰的日期'
FROM sys.dm_database_encryption_keys
GO


-- 圖02_移除資料庫加密功能後,檢視關於資料庫加密狀態的資訊




雖然資料庫 Northwind_TDE 的加密狀態是:1 (未加密)。

但此資料庫仍未完成解密,必須要再使用 DROP DATABASE ENCRYPTION KEY  移除資料庫加密金鑰。



-- 04_卸除資料庫透明加密中所使用的資料庫加密金鑰
/*
DROP DATABASE ENCRYPTION KEY 

卸除資料庫透明加密中所使用的資料庫加密金鑰。
如果資料庫經過加密,您必須使用 ALTER DATABASE 陳述式,先從資料庫移除加密。 等候解密完成,然後再移除資料庫加密金鑰。
*/
USE Northwind_TDE
GO
DROP DATABASE ENCRYPTION KEY;
GO

-- 05_卸除資料庫透明加密中所使用的資料庫加密金鑰後,檢視關於資料庫加密狀態的資訊
USE master
GO
SELECT DB_NAME(database_id) N'資料庫',encryption_state N'加密狀態',percent_complete N'完成百分比',
 key_algorithm N'演算法', key_length N'金鑰長度', encryptor_type N'加密程式的類型', encryptor_thumbprint N'加密程式的指模',create_date N'建立加密金鑰的日期'
FROM sys.dm_database_encryption_keys
GO



-- 圖03_卸除資料庫透明加密中所使用的資料庫加密金鑰後,檢視關於資料庫加密狀態的資訊





系統資料庫 tempdb 要等到下次重新啟動後,將自動移除資料庫透明加密功能。




參考資料

ALTER DATABASE SET 選項 (Transact-SQL)
http://technet.microsoft.com/zh-tw/library/bb522682.aspx

DROP DATABASE ENCRYPTION KEY (Transact-SQL)
http://msdn.microsoft.com/zh-tw/library/bb630256.aspx

新手學SQL Server 2008「透明資料加密(Transparent Data Encryption,TDE)」(1) : 技術文章
http://sharedderrick.blogspot.com/2008/10/1.html

新手學SQL Server 2008「透明資料加密(Transparent Data Encryption,TDE)」(2) : 技術文章
http://sharedderrick.blogspot.tw/2008/10/2.html


2013-03-13

彙整 SQL Server 2012 認證:以 MCSA + MCSE 為例



彙整 SQL Server 2012 認證:以 MCSA + MCSE 為例

1. 基礎認證:MCSA SQL Server 2012 認證

需要通過三門考試:70-461、70-462、70-463

2. 進階認證:MCSE

2-1 MCSE: Data Platform Solutions Expert -- SQL Server 2012

需要通過兩門考試:70-464、70-465

從 2014 年 4 月 24 日起,本測驗的問題範圍將包含 SQL Server 2014 的內容

2-2 MCSE: Business Intelligence Solutions Expert -- SQL Server 2012

需要通過兩門考試:70-466、70-467

從 2014 年 4 月 24 日起,本測驗的問題範圍將包含 SQL Server 2014 的內容



參考資料:
MCSA SQL Server 2012 認證
http://sharedderrick.blogspot.tw/2012/09/mcsa-sql-server-2012.html

MCSE: Data Platform Solutions Expert -- SQL Server 2012 認證
http://sharedderrick.blogspot.tw/2013/02/mcse-data-platform-solutions-expert-sql.html

MCSE: Business Intelligence Solutions Expert -- SQL Server 2012 認證
http://sharedderrick.blogspot.tw/2013/02/mcse-business-intelligence-solutions.html

新手學SQL Server 2012「SQL視窗函數(SQL Windows Function)」(4),以LAG()和LEAD()視窗相對位移函數為例


在前一篇文章中,介紹了認識視窗相對位移函數、認識FIRST_VALUE、認識LAST_VALUE等主題。

在本次文章中,將繼續討論視窗相對位移函數,包含有:認識ROWS或RANGE子句、ROWS或RANGE子句、LAG()以及LEAD()等主題。




完整文章,請參考以下的網址:

新手學SQL Server 2012「SQL視窗函數(SQL Windows Function)」(4),以LAG()和LEAD()視窗相對位移函數為例



2013-03-11

安裝 SQL Server 2012 Upgrade Advisor,缺少必要元件 Transact-SQL ScriptDom


示範環境:
Windows Server 2008 R2 Enterprise Edition。
SQL Server 2008 R2 Enterprise Edition。

安裝 SQL Server 2012 Upgrade Advisor 時,可能會遭遇到以下的錯誤訊息:


安裝程式缺少必要元件:
SQL Server 2012 Transact-SQL ScriptDom(未由 Upgrade Advisor 安裝程式安裝)。
...


-- 01_遭遇錯誤_未安裝_ScriptDom






可以在 SQL Server® 2012 功能套件中,單獨下載與安裝:

Microsoft® SQL Server® 2012 Transact-SQL ScriptDom

-- 02_下載_SQL_Server_2012 Transact-SQL ScriptDom






以下是 SQL Server 2012 Upgrade Advisor 安裝與執行畫面:





MICROSOFT SQL SERVER 2012 引擎功能套件元件

Microsoft® SQL Server® 2012 Transact-SQL ScriptDom

Microsoft SQL Server Transact-SQL ScriptDom 是 .NET Framework API,為 Transact-SQL 提供剖析和指令碼服務。

此 API 支援 SQL Server 2000、SQL Server 2005、SQL Server 2008、SQL Server 2008 R2 和 SQL Server 2012 版本。

X86 封裝 (SQLDOM.MSI)
http://go.microsoft.com/fwlink/?LinkID=239636&clcid=0x404

X64 封裝 (SQLDOM.MSI)
http://go.microsoft.com/fwlink/?LinkID=239635&clcid=0x404




參考資料

SQL Server® 2012 功能套件
http://www.microsoft.com/zh-tw/download/details.aspx?id=29065

SQL Server 2012 Upgrade Advisor won't install
http://social.msdn.microsoft.com/Forums/en-US/sqlsetupandupgrade/thread/42a565bf-2562-4fbd-a327-1487b7c17fa4/

2013-03-08

請勿使用 sp_depends 查詢物件相依性的資訊,改用:sys.sql_expression_dependencies 或 sys.dm_sql_referenced_entities


適用版本:SQL Server 2008 開始。

請勿使用 sp_depends 查詢物件相依性的資訊,改用:sys.sql_expression_dependencies 或 sys.dm_sql_referenced_entities

系統預存程序:sp_depends

顯示有關資料庫物件相依性的資訊,例如相依於資料表或檢視的檢視和程序,以及檢視或程序所相依的資料表或檢視。
但不會報告對於目前資料庫外之物件的參考。

重要事項:

未來的 Microsoft SQL Server 版本將移除這項功能。
請避免在新的開發工作中使用這項功能,並規劃修改目前使用這項功能的應用程式。

請改用 sys.dm_sql_referencing_entities 和 sys.dm_sql_referenced_entities。

請參考以下範例程式碼。



EX1. 更新「檢視表(VIEW)」的中繼資料(meta data)

-- EX1. 更新「檢視表(VIEW)」的中繼資料(meta data)

-- 00_建立範例資料表:TREF01,僅 2 個資料行
USE Northwind_Dev
GO
IF  EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[TREF01]') AND type in (N'U'))
DROP TABLE [dbo].[TREF01]
GO
CREATE TABLE TREF01
(SID INT, CDATA VARCHAR(200))
GO
INSERT dbo.TREF01 VALUES(1,'AB'),(2,'CD');
GO
-- 查詢範例資料表:TREF01,僅 2 個資料行
SELECT * FROM TREF01
GO


-- 圖01_查詢範例資料表:TREF01



-- 01_利用 SELECT * 來建立「檢視表(VIEW)」
IF  EXISTS (SELECT * FROM sys.views WHERE object_id = OBJECT_ID(N'[dbo].[vREF01]'))
DROP VIEW [dbo].[vREF01]
GO
CREATE VIEW vREF01
AS
 SELECT * FROM dbo.TREF01
GO

-- 02_查詢「檢視表(VIEW)」,僅 2 個資料行
SELECT * FROM vREF01
GO

-- 03_對「檢視表(VIEW)」,使用 sp_depends,顯示有關資料庫物件相依性的資訊
EXEC sp_depends 'dbo.vREF01'
GO


-- 圖02_使用 sp_depends



-- 04_對「檢視表(VIEW)」,使用 sql_expression_dependencies,傳回參考其他實體的相依性之資訊

SELECT OBJECT_NAME(referencing_id) AS N'參考實體的名稱', 
    o.type_desc AS N'參考實體的物件類型', 
    referencing_class_desc N'參考實體之類別的描述',
    referenced_server_name N'受參考實體之伺服器的名稱', 
    referenced_database_name N'受參考實體之資料庫的名稱', 
    referenced_schema_name N'受參考實體所屬的結構描述',
    referenced_entity_name N'受參考實體的名稱' 
FROM sys.sql_expression_dependencies AS sed INNER JOIN sys.objects AS o 
 ON sed.referencing_id = o.object_id
WHERE referencing_id = OBJECT_ID(N'dbo.vREF01');
GO


-- 圖03_使用 sql_expression_dependencies



-- 05_範例資料表:TREF01,增加一個資料行
ALTER TABLE TREF01
 ADD C3 INT
GO

-- 06_查詢資料表:已有 3 個資料行
SELECT * FROM TREF01
GO


-- 圖04_查詢資料表:已有 3 個資料行



-- 07_查詢「檢視表(VIEW)」:卻只有 2 個資料行
SELECT * FROM VREF01
GO


-- 圖05_查詢「檢視表(VIEW)」:卻只有 2 個資料行



-- 08_對「檢視表(VIEW)」,使用 sp_depends,顯示有關資料庫物件相依性的資訊
-- 卻只看到兩個資料行
EXEC sp_depends 'dbo.vREF01'
GO


-- 圖06_使用 sp_depends,卻只看到兩個資料行



-- 09_對「檢視表(VIEW)」,使用 sql_expression_dependencies,傳回參考其他實體的相依性之資訊

SELECT OBJECT_NAME(referencing_id) AS N'參考實體的名稱', 
    o.type_desc AS N'參考實體的物件類型', 
    referencing_class_desc N'參考實體之類別的描述',
    referenced_server_name N'受參考實體之伺服器的名稱', 
    referenced_database_name N'受參考實體之資料庫的名稱', 
    referenced_schema_name N'受參考實體所屬的結構描述',
    referenced_entity_name N'受參考實體的名稱' 
FROM sys.sql_expression_dependencies AS sed INNER JOIN sys.objects AS o 
 ON sed.referencing_id = o.object_id
WHERE referencing_id = OBJECT_ID(N'dbo.vREF01');
GO


-- 圖07_使用 sql_expression_dependencies



-- 10_刪除與重新建立此範例資料表:TREF01
DROP TABLE [dbo].[TREF01]
GO
CREATE TABLE TREF01
(SID INT, CDATA VARCHAR(200))
GO

-- 11_對「檢視表(VIEW)」,使用 sp_depends,顯示有關資料庫物件相依性的資訊
-- 找不到任何資訊
EXEC sp_depends 'dbo.vREF01'
GO

/*
物件並未參考任何物件,且沒有物件參考它。
*/


-- 圖08_使用 sp_depends,找不到任何資訊



-- 12_對「檢視表(VIEW)」,使用 sql_expression_dependencies,傳回參考其他實體的相依性之資訊
-- 仍是可以正確顯示資料

SELECT OBJECT_NAME(referencing_id) AS N'參考實體的名稱', 
    o.type_desc AS N'參考實體的物件類型', 
    referencing_class_desc N'參考實體之類別的描述',
    referenced_server_name N'受參考實體之伺服器的名稱', 
    referenced_database_name N'受參考實體之資料庫的名稱', 
    referenced_schema_name N'受參考實體所屬的結構描述',
    referenced_entity_name N'受參考實體的名稱' 
FROM sys.sql_expression_dependencies AS sed INNER JOIN sys.objects AS o 
 ON sed.referencing_id = o.object_id
WHERE referencing_id = OBJECT_ID(N'dbo.vREF01');
GO

-- 13_不影響:查詢「檢視表(VIEW)」
-- 雖然,使用 sp_depends,找不到任何資訊
SELECT * FROM dbo.vREF01
GO





若是要修正以下問題:

(1) 基礎資料表已經改為 3 個資料行,但查詢「檢視表(VIEW)」:卻只有 2 個資料行 。

(2) 使用 sp_depends,找不到任何資訊(資料庫物件相依性的資訊)之問題。

可以使用:
sys.sp_refreshsqlmodule 或 sys.sp_refreshview 來處理。



-- 14_sys.sp_refreshsqlmodule 更新其中繼資料
EXEC sys.sp_refreshsqlmodule @name = 'dbo.vREF01' ;
GO

-- 15_sys.sp_refreshview 更新指定非結構描述繫結檢視的中繼資料。 
EXEC sys.sp_refreshview 'VREF01';
GO




認識 sys.sp_refreshsqlmodule

適用版本:SQL Server 2005 已提供

針對目前資料庫中指定的非結構描述繫結預存程序、使用者自訂函數、檢視、DML 觸發程序、資料庫層級 DDL 觸發程序或伺服器層級 DDL 觸發程序,更新其中繼資料。

這些物件的基礎物件變更之後,其保存中繼資料也可能會過期,例如參數的資料類型。



認識 sys.sp_refreshview

適用版本:SQL Server 2000 已提供

更新指定非結構描述繫結檢視的中繼資料。

檢視所依賴的基礎物件變更之後,檢視的保存中繼資料也可能會過期。



參考資料

sp_depends (Transact-SQL)

sys.dm_sql_referencing_entities (Transact-SQL)

sys.dm_sql_referenced_entities (Transact-SQL)

sp_refreshsqlmodule (Transact-SQL)

sp_refreshview (Transact-SQL)

sp_refreshview -- SQL Server 2000 版本



2013-03-01

刪除所有連線(Kill All Connection),資料庫層級、執行個體層級。以 Cursors(資料指標) 與資料列集(Rowsets) 方式為例。


分成為刪除指定資料庫,以及刪除執行個體上的全部連線兩個部分來說明。

以下整理了 Cursors(資料指標) 版本,以及資料列集(Rowsets)方式來處理。



EX1. 刪除指定資料庫上的所有連線

若是要刪除指定資料庫上的所有連線,可以使用以下方式:

ALTER DATABASE 語法的部分引數:

1. SINGLE_USER 引數

指定每次只能有一位使用者存取資料庫。

如果指定了 SINGLE_USER,且沒有其他使用者連接到資料庫,就會封鎖 ALTER DATABASE 陳述式,直到所有使用者都中斷指定的資料庫為止。

資料庫會保留在 SINGLE_USER 模式中,即使是將選項記錄設為關閉的使用者也是如此。
此時其他使用者可以連接到這個資料庫,但只能有一位。

2. WITH ROLLBACK IMMEDIATE 引數

使用終止選項。所有未完成的交易都會回復,與指定資料庫的所有連接都會立即中斷。

若是要刪除指定資料庫上的所有連線,請參考以下的範例:



-- 01_刪除指定資料庫上的所有連線,並變更為單一使用者連線模式
USE master
GO
ALTER DATABASE [資料庫名稱]
SET SINGLE_USER
WITH ROLLBACK IMMEDIATE;
GO

-- 02_切換回多人使用者連線模式
ALTER DATABASE [資料庫名稱]
SET MULTI_USER;
GO





EX2. CURSOR 版本,刪除 SQL Server 上全部的連線,但保留本身這條連線不會刪除

-- EX1. CURSOR 版本,刪除 SQL Server 上全部的連線,但保留本身這條連線不會刪除

-- 01_適用 SQL Server 2005、2008、2008 R2、2012 等版本

USE master
GO
CREATE PROC KillUserConnections
AS
DECLARE @spid int, @SQLstr nvarchar(128)
 
DECLARE spids_cr CURSOR FOR
SELECT session_id FROM sys.dm_exec_connections
WHERE session_id<>@@SPID
FOR READ ONLY
 
OPEN spids_cr
 
FETCH spids_cr INTO @spid
 
WHILE (@@FETCH_STATUS=0)
BEGIN
SET @SQLstr = 'KILL ' + CAST(@spid AS varchar)
EXEC sp_executesql @SQLstr
FETCH spids_cr INTO @spid
END
 
CLOSE spids_cr
DEALLOCATE spids_cr
GO
 
-- 檢視 SQL Server 目前所有的連線 session ;所有目前的處理序
SELECT * FROM sys.dm_exec_connections
 
-- 刪除所有的處理序、連線、session
EXEC KillUserConnections
 
-- 再度檢視 SQL Server 目前所有的連線 session ;所有目前的處理序
SELECT * FROM sys.dm_exec_connections
GO


-- 02_以下是 SQL Server 2000 與 2005 通用的版本

USE master
GO
CREATE PROC KillUserConnections
AS
DECLARE @spid int, @SQLstr nvarchar(128)
 
DECLARE spids_cr CURSOR FOR
SELECT spid
FROM master..sysprocesses
WHERE spid<>@@SPID AND net_address<>''
FOR READ ONLY
 
OPEN spids_cr
 
FETCH spids_cr INTO @spid
 
WHILE (@@FETCH_STATUS=0)
BEGIN
SET @SQLstr = 'KILL ' + CAST(@spid AS varchar)
EXEC sp_executesql @SQLstr
FETCH spids_cr INTO @spid
END
 
CLOSE spids_cr
DEALLOCATE spids_cr
 
GO
 
-- 檢視 SQL Server 目前所有的連線 session ;所有目前的處理序
EXEC sp_who2
 
-- 刪除所有的處理序、連線、session
EXEC KillUserConnections
 
-- 再度檢視 SQL Server 目前所有的連線 session ;所有目前的處理序
EXEC sp_who2
GO


其中 sysprocesses 此為 SQL Server 2000 系統資料表。




EX3. 資料列集(Rowsets)方式:刪除 SQL Server 上全部的連線,但保留本身這條連線不會刪除

使用資料列集(Rowsets)方式,無須使用 CURSOR 方式。

適用 SQL Server 2005、2008、2008 R2、2012 等版本。

-- EX2. 資料列集(Rowsets)方式:刪除 SQL Server 上全部的連線,但保留本身這條連線不會刪除

-- 01_檢視 SQL Server 目前所有的連線 session ;所有目前的處理序
USE master
GO
SELECT * FROM sys.dm_exec_connections
GO

-- 02_使用 ISNULL() 函數:刪除 SQL Server 上全部的連線,但保留本身這條連線不會刪除
DECLARE @str1 nvarchar(MAX)

SELECT @str1 = ISNULL(@str1, '') + 'KILL ' + CAST(session_id AS nvarchar(10)) + ';'
FROM sys.dm_exec_connections
WHERE session_id<>@@SPID

-- PRINT @str1
EXEC sp_executesql @str1
GO

/*
PRINT 陳述式所回傳的結果:
KILL 52;KILL 51;KILL 53;KILL 54;KILL 56;KILL 57;KILL 58;KILL 59;KILL 60;KILL 61;KILL 62;KILL 63;KILL 64;KILL 65;
*/


-- 03_使用 COALESCE() 函數:刪除 SQL Server 上全部的連線,但保留本身這條連線不會刪除
DECLARE @str1 nvarchar(MAX)

SELECT @str1 = COALESCE(@str1, '') + 'KILL ' + CAST(session_id AS nvarchar(10)) + ';'
FROM sys.dm_exec_connections
WHERE session_id<>@@SPID

-- PRINT @str1
EXEC sp_executesql @str1
GO


若是要改為 SQL Server 2000 版本:

請置換資料表為:master..sysprocesses。

請置換資料行:spid。



參考資料


刪除所有連線; Kill All Connection
http://sharedderrick.blogspot.tw/2008/01/kill-all-connection.html

SQL Server:認識 COALESCE() 函數,補充範例
http://sharedderrick.blogspot.tw/2012/06/t-sql-coalesce_14.html

初探Cursors(資料指標) 與資料列集(Rowsets)
http://sharedderrick.blogspot.tw/2013/02/cursors-rowsets.html