I've built a stored procedure (which we can call sproc_deps) that uses sys.sql_expression_dependencies and sys.dm_sql_referenced_entities. I want it to list out all of the tables and columns used by a stored procedure of the user's choice. This stored procedure will have its name passed as an argument to sproc_deps.
The problem is that I'm getting columns that the stored procedure doesn't actually use when I combine sys.sql_expression_dependencies and sys.dm_sql_referenced_entities. In order to get the information I want, I've JOINed a few other things on as well:
sys.objects(for object IDs, andtype_desc)sys.tables(to match against tables contained insys.sql_expression_dependencies)sys.views(because I'm interested in both views and tables)sys.columns(to pull columns for each table or view involved)
Here is the actual 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
Using just sys.sql_expression_dependencies nets a small list of tables that I can't really decipher, and using sys.dm_sql_referenced_entities yields a partial list of tables and columns used by the procedure.
Is it possible for sproc_deps to return the correct list of tables and columns used by a procedure using just T-SQL? If so, how?
Here is my full code as requested (still working on reproducible example for missing dependencies):
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
This will create a procedure that grabs known dependency data for a procedure from a view (v9_Sproc_DocInfo) that uses only sys.sql_expression_dependencies and sys.dm_sql_referenced_entities. Then I pull the full list of columns for any table used by the procedure, filter out anything already contained in v9_Sproc_DocInfo, and combine this data with the view data as output.
