No references in [sys].[dm_SQL_referenced_entities]

See more:

SQL

database

The background of the code: I wanted to have something which is collecting the used tables of the procedures, but my problem is that if the code reach a procedure which doesn't have any kind of reference it is crashed. How can I insert the procedures into the table with "N/A" value? I can solve it with Updates, but the main problem is that as I said that the "empty" procedures are killing the code running.

What I have tried:

<pre> DECLARE @SQL_Procedures NVARCHAR(MAX) = 
    (
    SELECT
    STRING_AGG(CAST( 
    'SELECT DISTINCT
    referenced_database_name AS [Database], 
    referenced_schema_name AS [Schema],
    referenced_entity_name AS [Table],'
    +rtrim(name)+' AS [Procedure]'+','+
    +'GETDATE() AS [Extract_Timestamp]'+
    ' FROM [INFO].[sys].[dm_sql_referenced_entities]''
    (' AS nvarchar(MAX))+QUOTENAME(schema_name(),'')+'.'+QUOTENAME(name,'') +N', ''OBJECT'')
    ', NCHAR(10) + N' UNION ALL ' + NCHAR(10)
    ) + NCHAR(10) + N'ORDER BY 1,2,3,4,5;'
     FROM dbo.sysobjects
     WHERE (type = 'P')
     AND name like 'p%'
     )
    
    PRINT(@SQL_Procedures)
    INSERT INTO [ABC].[dbo].[INFO_Procedures_Tables]
    EXEC sys.sp_executesql @SQL_Procedures 

Error message:


     <pre>...UNION ALL 
    SELECT DISTINCT
    referenced_database_name AS [Database], 
    referenced_schema_name AS [Schema],
    referenced_entity_name AS [Table],p_DET_INV_008 AS [Procedure],GETDATE() AS [Extract_Timestamp] FROM [I
    Msg 102, Level 15, State 1, Line 138
    Incorrect syntax near '
    ([dbo].[p_DET_INV_004], '.