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 

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

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

                  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 


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.

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

I expect the results of this function to be:

  1. dbo.Invoices
  2. dbo.Invoices.CashDiscounts
  3. dbo.Invoices.Cost

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

  1. 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?


2 Answers 2


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”.

  • Microsoft connect seems to be retired now. Did they move this item to somewhere else? Commented Jun 14, 2021 at 15:29
  • 1
    @JensWagemaker it is in the documentation now learn.microsoft.com/en-us/sql/relational-databases/…. Dependency information is not created or maintained for rules, defaults, temporary tables, temporary stored procedures, or system objects. Commented Jun 15, 2021 at 5:41

Thanks for the answer. Just before I posted a question here I did a search Microsoft Connect bug list however nothing came up here and I used dm_sql_referenced_entities as keyword. I've posted the same bug and that's available at https://connect.microsoft.com/SQLServer/feedback/details/1859356.

Let's say I understand Microsoft policy about that. I wonder however if there is a way to modify and maybe fix sys.dm_sql_referenced_entities function at least on my local instance of SQL Server?

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.