sys.dm_sql_referenced_entities fails to provide used columns list when joining with temp table

I think I need some help as I run out of ideas.

In my database I have a simple table like this:

CREATE TABLE dbo.Invoices 
( 
        CashDiscounts MONEY, 
        Cost MONEY 
) 
GO

and a stored procedure that uses this table in SELECT context:

CREATE PROCEDURE Reporting.pLoadTest 
AS 
BEGIN 
        CREATE TABLE #t ( Test INT ); 

        SELECT  
                  Invoice.[CashDiscounts]       AS [CashDiscounts] 
                , Invoice.[Cost]                        AS [Cost] 
        FROM dbo.Invoices AS Invoice 
        LEFT OUTER JOIN #t -- if you remove this join, dm_sql_referenced_entities should return also CashDiscounts and Cost attributes
        ON 1=1 

END 
GO

Now, the problem I got is following: I am using system function sys.dm_sql_referenced_entities to get list of columns used by Reporting.pLoadTest.

SELECT * 
FROM sys.dm_sql_referenced_entities ('Reporting.pLoadTest', 'OBJECT')

I expect the results of this function to be:

• dbo.Invoices

• dbo.Invoices.CashDiscounts

• dbo.Invoices.Cost

However, when I do a join to temp #t table function sys.dm_sql_referenced_entities returns only

• dbo.Invoices

Everything in this case works fine if I use table variable instead of temp table. I tried to fiddle with permissions for tempdb as written in documentation


Requires SELECT permission on sys.dm_sql_referenced_entities and VIEW
DEFINITION permission on the referencing entity.

but with no positive result.

What should I do to get correct results of columns being used by a stored procedure that do a select and a join to temp table?

This is a bug in SQL Server and here is a Connect item to vote for if you want a change.

dm_sql_referenced_entities does not shows columnes when temporary tables are used in statement

Current status:


We believe it is unlikely that we will address this suggestion, and so
we are closing it as “won’t fix”.

StackExchange Database Administrators Q#116883, answer score: 8