Referenced Entities in a Stored Procedure

Question

Trying to get the objects referenced within stored procedure dynamically. Below code doesn't work when it's dynamic

DECLARE @procname VARCHAR(50) = 'GetTempBondInformation'
SELECT  QUOTENAME('dbo.' + @procname ,'''')
SELECT  DISTINCT referenced_entity_name  FROM    sys.dm_sql_referenced_entities(QUOTENAME('dbo.' + @procname ,''''), 'OBJECT')

Whereas below code works fine.

SELECT  DISTINCT referenced_entity_name  FROM    sys.dm_sql_referenced_entities('dbo.GetTempBondInformation', 'OBJECT')

Any clues?

Answer 1

Try this:

(remove the quoting)

DECLARE @procname VARCHAR(50) = 'GetTempBondInformation'
SELECT  DISTINCT 
     referenced_entity_name  
FROM    
     sys.dm_sql_referenced_entities('dbo.' + @procname, 'OBJECT')