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?
Best Answer
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: