依賴於給定表和列的視圖列表

我需要一個腳本來檢查給定表中的給定列是否在任何視圖中使用。理想情況下，我也想獲得視圖的名稱。

我正在使用 T-SQL，我需要執行腳本以在我正在開發的應用程序中檢查這一點。

SQL Server 提供了一個系統視圖，sys.sql_dependencies它映射視圖等對象所使用的依賴關係。

因此，要查看與數據庫中的視圖關聯的所有表和列，請使用以下查詢：

;WITH deps
AS
(
   SELECT sd.object_id
       , sd.referenced_major_id
   FROM sys.sql_dependencies sd
   WHERE sd.class = 0
       OR sd.class = 1
   GROUP BY sd.object_id
       , sd.referenced_major_id
)
SELECT ViewName = v.name
   , ReferencedObjectName = o.name
   , ReferencedColumns = STUFF((SELECT N', ' + c.name 
                                FROM sys.columns c 
                                   INNER JOIN sys.sql_dependencies sd1 ON c.object_id = sd1.referenced_major_id
                                       AND c.column_id = sd1.referenced_minor_id 
                                WHERE c.object_id = sd.referenced_major_id 
                                ORDER BY c.object_id FOR XML PATH('')
                                ), 1, 2, N'')
FROM sys.views v
   INNER JOIN deps sd ON v.object_id = sd.object_id
   INNER JOIN sys.objects o ON sd.referenced_major_id = o.object_id
ORDER BY v.name;

考慮這個簡單的測試：

DROP VIEW dbo.my_view_2;
DROP VIEW dbo.my_view;
DROP TABLE dbo.my_table;
DROP TABLE dbo.my_table_2;
CREATE TABLE dbo.my_table (Column_I int NOT NULL, Column_J int NOT NULL);
CREATE TABLE dbo.my_table_2 (Column_J int NOT NULL, Column_K int NOT NULL, Column_L int NOT NULL);
GO
CREATE VIEW dbo.my_view AS SELECT mt.Column_I FROM dbo.my_table mt INNER JOIN dbo.my_table_2 mt2 ON mt.Column_J = mt2.Column_J;
GO
CREATE VIEW dbo.my_view_2 AS SELECT mt.Column_I FROM dbo.my_table mt;
GO

上面的查詢結果顯示：

╔═══════════╦══════════════════════╦══════════════════════════════╗
║ ViewName ║ ReferencedObjectName ║ ReferencedColumns ║
╠═══════════╬══════════════════════╬══════════════════════════════╣
║ my_view ║ my_table ║ Column_I, Column_I, Column_J ║
║ my_view ║ my_table_2 ║ Column_J ║
║ my_view_2 ║ my_table ║ Column_I，Column_I，Column_J ║
╚═══════════╩══════════════════════╩══════════════════════════════╝

這會將輸出限制為僅依賴於特定表的視圖：

;WITH deps
AS
(
   SELECT sd.object_id
       , sd.referenced_major_id
   FROM sys.sql_dependencies sd
   WHERE sd.class = 0
       OR sd.class = 1
   GROUP BY sd.object_id
       , sd.referenced_major_id
)
SELECT ViewName = v.name
   , ReferencedObjectName = o.name
   , ReferencedColumns = STUFF((SELECT N', ' + c.name 
                                FROM sys.columns c 
                                   INNER JOIN sys.sql_dependencies sd1 ON c.object_id = sd1.referenced_major_id
                                       AND c.column_id = sd1.referenced_minor_id 
                                WHERE c.object_id = sd.referenced_major_id 
                                ORDER BY c.object_id FOR XML PATH('')
                                ), 1, 2, N'')
FROM sys.views v
   INNER JOIN deps sd ON v.object_id = sd.object_id
   INNER JOIN sys.objects o ON sd.referenced_major_id = o.object_id
WHERE o.name = N'my_table_1'
ORDER BY v.name;

這可用於顯示依賴於數據庫中具有特定名稱的任何列的視圖column_j：

;WITH deps
AS
(
   SELECT sd.object_id
       , sd.referenced_major_id
   FROM sys.sql_dependencies sd
   WHERE sd.class = 0
       OR sd.class = 1
   GROUP BY sd.object_id
       , sd.referenced_major_id
)
SELECT ViewName = v.name
   , ReferencedObjectName = o.name
   , ReferencedColumns = STUFF((SELECT N', ' + c.name 
                                FROM sys.columns c 
                                   INNER JOIN sys.sql_dependencies sd1 ON c.object_id = sd1.referenced_major_id
                                       AND c.column_id = sd1.referenced_minor_id 
                                WHERE c.object_id = sd.referenced_major_id 
                                ORDER BY c.object_id FOR XML PATH('')
                                ), 1, 2, N'')
FROM sys.views v
   INNER JOIN deps sd ON v.object_id = sd.object_id
   INNER JOIN sys.objects o ON sd.referenced_major_id = o.object_id
WHERE EXISTS (
       SELECT 1 
       FROM sys.columns c 
       WHERE c.object_id = sd.referenced_major_id
       AND c.name = N'Column_J'
       )
ORDER BY v.name;

引用自：https://dba.stackexchange.com/questions/280616