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:
However, when I do a join to temp #t table function sys.dm_sql_referenced_entities returns only
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?