搜尋本站文章

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 版本