Loop Through DB, get table dependencies inquiry

Question

Goal
Use Table Cursor to loop through all tables within schema and get table dependencies.

I want to use it like this

    DECLARE @SchemaName nvarchar(MAX)
    DECLARE @TableName nvarchar(MAX)
    DECLARE @SQL nvarchar(MAX)
    SET @SchemaName = 'dbo'
    
    DECLARE TableCursor CURSOR FOR
    SELECT TABLE_NAME FROM INFORMATION_SCHEMA.TABLES WHERE TABLE_TYPE='BASE TABLE' AND TABLE_SCHEMA = @SchemaName
    OPEN TableCursor
    FETCH NEXT FROM TableCursor INTO @TableName
    WHILE @@FETCH_STATUS = 0
    BEGIN
        SET @SQL = 
            '
            IF OBJECT_ID(''tempdb..#temp'',''U'') IS NOT NULL
                DROP TABLE #temp;
            SELECT * INTO #temp FROM sys.dm_sql_referencing_entities(' + @SchemaName +'.'+ @TableName + ', ''OBJECT'')
            '
        EXEC sp_executesql @SQL
        FETCH NEXT FROM TableCursor INTO @TableName
    END
    CLOSE TableCursor
    DEALLOCATE TableCursor

Tried

    DECLARE @table NVARCHAR(MAX)
    DECLARE @SQL nvarchar(MAX)
    SET @table = 'dbo.InventTable'
    SET @sql = 
        '
        SELECT * FROM sys.dm_sql_referencing_entities(' + @table + ', ''OBJECT'')
        '
    EXEC sp_executesql @SQL

Expecting
Just the result of sys.dm_sql_referencing_entities enter image description here

Answer 1

I'm not sure if you have some requirement to use a cursor, but it's typically not the best way to go, there are exceptions of course.

Instead, you can use CROSS APPLY with the function and pass in an object name derived from columns. This functions like an INNER JOIN (with the OUTER version being OUTER APPLY). In this case you'll get a 0-many response, joined to your existing data set.

SELECT t.name AS TableName, s.name AS SchemaName, QUOTENAME(s.name)+'.'+QUOTENAME(t.Name) AS ObjectName,
       re.referencing_schema_name, referencing_entity_name, QUOTENAME(re.referencing_schema_name)+'.'+QUOTENAME(referencing_entity_name) AS ReferencingObjectName, 
       o.type_desc AS ReferencingObjectType
  FROM sys.tables t
    INNER JOIN sys.schemas s
      ON t.schema_id = s.schema_id
    CROSS APPLY sys.dm_sql_referencing_entities(s.name+'.'+t.name, 'OBJECT') re
    INNER JOIN sys.objects o
      ON referencing_id = o.object_id
    INNER JOIN sys.schemas s2
      ON re.referencing_schema_name = s2.name
 WHERE s.name = 'dbo'
 --AND t.Name = 'InventTable'

I left the table name commented out, so the query as it is will return rows for everything within the specified schema, but you could uncomment it to get specific. I also modified the columns to return to best fit what I think you wanted.