Way to obtain full list of column dependencies for stored procedure? 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, and type_desc)
sys.tables(to match against tables contained in sys.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


The code posted 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.