假設我有兩個表：
CREATE TABLE Customers
(
Id BIGINT IDENTITY(1,1) PRIMARY KEY,
FullName varchar(100) NOT NULL,
);
CREATE TABLE Employees
(
Id BIGINT IDENTITY(1,1) PRIMARY KEY,
FullName varchar(100) NOT NULL,
);
如果我在任何存盤程序中使用了表的FullName列Customers，那么我想通過使用ColumnName = 'FullName'和進行搜索來獲取所有這些存盤程序名稱TableName = 'Customers'。
我不需要那些使用表中FullName列的存盤程序名稱Employees。
是否可以在 SQL Server 中為此撰寫腳本？
注意：在我的搜索條件中，我想使用列名和表名進行搜索。
uj5u.com熱心網友回復：
您不需要使用sys.sql_dependencies哪個已被棄用，您可以使用較新的sys.dm_sql_referenced_entities代替。
不幸的是，經過一些測驗，事實證明它
sys.sql_expression_dependencies并sys.dm_sql_referencing_entities沒有回傳有關列級依賴項的資訊。
SELECT OBJECT_SCHEMA_NAME(o.object_id),
OBJECT_NAME(o.object_id)
FROM sys.objects o
JOIN sys.schemas s ON s.schema_id = o.schema_id
CROSS APPLY sys.dm_sql_referenced_entities(QUOTENAME(s.name) '.' QUOTENAME(o.name), 'OBJECT') d
INNER JOIN sys.columns c
ON c.object_id = d.referenced_id
AND c.column_id = d.referenced_minor_id
AND c.object_id = OBJECT_ID('dbo.Customers')
AND c.name = 'FullName'
WHERE o.type IN ('FN','TF','IF','P','V','TR'); -- scalar, multi-line and inline funcs, procs, views, triggers
uj5u.com熱心網友回復：
sys.sql_dependencies 已棄用，但雖然它仍然存在，但您可以使用
SELECT OBJECT_SCHEMA_NAME(d.object_id),
OBJECT_NAME(d.object_id)
FROM sys.sql_dependencies d
INNER JOIN sys.columns c
ON c.object_id = d.referenced_major_id
AND c.column_id = d.referenced_minor_id
WHERE d.class = 0
AND d.referenced_major_id = object_id('dbo.Customers')
AND c.name = 'FullName'
與文本搜索相比，這有很多優點，因為您不會遇到程序既包含字串Customers又包含FullName注釋或其他一些會產生誤報的背景關系的問題。它還處理程序包含*而不是明確提及名稱的情況。
如果您使用動態 SQL，您可能需要退回到更痛苦的文本搜索。
我可能最終會使用混合方法來覆寫所有角度，如下所示，這樣我就可以無需實際查看 中找到的確定依賴項的文本，sys.sql_dependencies而只需手動查看“可能性”
WITH Candidates AS
(
SELECT d.object_id, 0 AS Source
FROM sys.sql_dependencies d
INNER JOIN sys.columns c
ON c.object_id = d.referenced_major_id
AND c.column_id = d.referenced_minor_id
WHERE d.class = 0
AND d.referenced_major_id = object_id('dbo.Customers')
AND c.name = 'FullName'
UNION ALL
SELECT m.object_id, 1 AS Source
FROM sys.sql_modules m
WHERE m.definition LIKE '%Customers%' AND (m.definition LIKE '%FullName%' OR m.definition LIKE '%*%')
)
SELECT SchemaName = OBJECT_SCHEMA_NAME(object_id),
ModuleName = OBJECT_NAME(object_id),
Confidence = IIF(MIN(Source) = 0, 'High', 'Possible')
FROM Candidates
GROUP BY object_id
轉載請註明出處，本文鏈接：https://www.uj5u.com/gongcheng/399485.html
標籤：sql sql-server
上一篇：如何解決嵌套聚合函式錯誤？