獲取儲存過程的列依賴項的完整列表的方法？

我已經建構了一個使用和的儲存過程（我們可以稱之為sproc_deps）。我希望它列出使用者選擇的儲存過程使用的**所有表和列。**此儲存過程的名稱將作為參數傳遞給.sys.sql_expression_dependencies``sys.dm_sql_referenced_entities``sproc_deps

問題是我得到了當我組合sys.sql_expression_dependencies和時儲存過程實際上並沒有使用的列sys.dm_sql_referenced_entities。為了獲得我想要的資訊，我還JOIN編輯了其他一些內容：

• sys.objects（對於對象 ID 和type_desc）
• sys.tables（與 中包含的表匹配sys.sql_expression_dependencies）
• sys.views（因為我對視圖和表格都感興趣）
• sys.columns（為涉及的每個表​​或視圖提取列）

這是實際的JOIN：

sys.sql_expression_dependencies AS sed  
INNER JOIN sys.objects AS o ON sed.referencing_id = o.object_id  
LEFT OUTER JOIN sys.tables t on sed.referenced_entity_name = t.name
LEFT OUTER JOIN sys.views v on sed.referenced_entity_name = v.name
LEFT OUTER JOIN sys.columns c on (c.object_id = t.object_id OR c.object_id = v.object_id)
INNER JOIN sys.dm_sql_referenced_entities (N'dbo.DummySprocName', 'OBJECT') s
ON s.referenced_entity_name = sed.referenced_entity_name

僅使用sys.sql_expression_dependencies網路我無法真正破譯的一小部分錶，並使用sys.dm_sql_referenced_entities產生過程使用的表和列的部分列表。

是否可以僅使用 T-SQLsproc_deps返回過程使用的正確表和列列表？如果是這樣，怎麼做？

我會從相反的方向來。

   select c.table_name, c.column_name, sp.name 
   from INFORMATION_SCHEMA.columns c
   inner join sys.procedures sp on object_definition(sp.object_id) like '%' + c.TABLE_NAME + '%' 
   and object_definition(sp.object_id) like '%' + c.column_name + '%'

這是我要求的完整程式碼（仍在研究缺少依賴項的可重現範例）：

CREATE PROCEDURE [dbo].[usp_v9_SprocDocInfo_FullDependency_SingleSproc]
@SprocName NVARCHAR(150) = ''

AS
BEGIN

DECLARE @ObjName NVARCHAR(128) = NULL
DECLARE @rowCount INT = 0
DECLARE @HasNulls BIT = 0
DECLARE @DepExists BIT = 0

--temp table to hold output
--match this against view!!!
CREATE TABLE #TempData
(
FullName NVARCHAR(300) not null,
ShortName NVARCHAR(128) not null,
TableName NVARCHAR(128),
ObjectName NVARCHAR(128),
column_name NVARCHAR(128),
[definition] NVARCHAR(MAX),
LastUpdated DATETIME,
[Type] NVARCHAR(60),
[object_id] INT,
SprocNo BIGINT
)

--temp table to hold pure dependencies
CREATE TABLE #Sproc_FullTableCols
(
RefEntity NVARCHAR(256),
TableName NVARCHAR(256),
ColName NVARCHAR(256),
TypeDesc NVARCHAR(256)
)

--first, grab known dependency data for this particular sproc from the correct view
INSERT INTO #TempData
SELECT FullName,
ShortName,
TableName,
ObjectName,
column_name,
[definition],
LastUpdated,
[Type],
[OBJECT_ID],
SprocNo
FROM v9_Sproc_DocInfo
WHERE ShortName = @SprocName

--next grab any data not covered in the previous query
--this will be tables/views and ALL columns for objects found in sproc
INSERT INTO #Sproc_FullTableCols
--https://www.sqlrx.com/using-sys-sql_expression_dependencies-as-a-single-source-to-find-referenced-and-referencing-objects/
SELECT DISTINCT OBJECT_NAME(referencing_id) AS referencing_entity_name, 
   CASE WHEN t.name is null then V.name when V.name is null THEN t.name ELSE NULL END,  
   c.name,
   case when t.name is not null then t.type_desc 
       when v.name is not null then v.type_desc 
       else o.type_desc end AS referencing_desciption
FROM sys.sql_expression_dependencies AS sed  
INNER JOIN sys.objects AS o ON sed.referencing_id = o.object_id  
LEFT OUTER JOIN sys.tables t on sed.referenced_entity_name = t.name
LEFT OUTER JOIN sys.views v on sed.referenced_entity_name = v.name
LEFT OUTER JOIN sys.columns c on (c.object_id = t.object_id OR c.object_id = v.object_id)
INNER JOIN sys.dm_sql_referenced_entities (N'dbo.' + @SprocName, 'OBJECT') s
ON s.referenced_entity_name = sed.referenced_entity_name
WHERE referencing_id = OBJECT_ID(N'dbo.' + @SprocName)

--clean up
DELETE FROM #Sproc_FullTableCols
WHERE (TableName IS NULL OR ColName IS NULL)-- OR MinorRef IS NULL)

WHILE @HasNulls = 0
BEGIN

   --pull the first row of junk data from the v9 results
   SET @ObjName = (SELECT TOP 1 ObjectName FROM #TempData WHERE (TableName IS NULL AND column_name IS NULL))

   SET @DepExists = CASE WHEN @ObjName IN (SELECT TableName FROM #TempData) THEN 1 ELSE 0 END

   --see if pull was successful; if so update flag accordingly
   IF (@ObjName IS NOT NULL) SET @HasNulls = 1
   ELSE BREAK

   IF @HasNulls = 1 AND @DepExists = 0
   BEGIN

       INSERT INTO #TempData
       SELECT DISTINCT (N'dbo.' + @SprocName) AS FullName,
           @SprocName AS ShortName,
           @ObjName AS TableName,
           '-' AS ObjectName,
           z.ColumnName AS column_name,
           z.Description AS [definition],
           GETDATE() AS LastUpdated,
           '*' + (N'' + c.TypeDesc) AS [Type],
           OBJECT_ID(N'dbo.' + @SprocName) AS [object_id],
           0 AS SprocNo
       FROM z9_BaseTables_Columns z
       INNER JOIN #Sproc_FullTableCols c 
           ON (z.[Table] = c.TableName collate Latin1_General_CI_AI
           AND z.ColumnName = c.ColName collate Latin1_General_CI_AI)
       WHERE z.[Table] = @ObjName collate Latin1_General_CI_AI

   END

   --clean out row used for input
   DELETE TOP (1) FROM #TempData
   WHERE ObjectName = @ObjName
   AND TableName IS NULL
   AND column_name IS NULL

   SET @HasNulls = 0
   SET @ObjName = ''

END

--finally, print results then discard the temp table
SELECT * FROM #TempData

order by [Type] asc, FullName asc, ObjectName asc, TableName asc, column_name asc, LastUpdated asc

DROP TABLE #TempData

DROP TABLE #Sproc_FullTableCols

END

這將創建一個過程，該過程從僅使用和的視圖 ( v9_Sproc_DocInfo) 中獲取過程的已知依賴關係數據。然後，我提取該過程使用的任何表的完整列列表，過濾掉已包含在 中的任何內容，並將此數據與視圖數據組合作為輸出。sys.sql_expression_dependencies``sys.dm_sql_referenced_entities``v9_Sproc_DocInfo

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