德瑞克：SQL Server 學習筆記: 請勿使用 sp_depends 查詢物件相依性的資訊，改用：sys.sql_expression_dependencies 或 sys.dm_sql_referenced

適用版本：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)

http://msdn.microsoft.com/zh-tw/library/ms189487.aspx

sys.dm_sql_referencing_entities (Transact-SQL)