6

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:

  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?

6

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

0

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, privacy policy and cookie policy

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