Get a list of tables and views referenced by sproc in SQL Server

sproc_dependencies.sql

(Generated by Claude 3.5 Sonnet)

-- Notes:
-- It won't catch tables referenced in dynamic SQL.
-- It may not catch deeply nested dependencies (e.g., views referencing other views).
-- It doesn't distinguish between read and write operations.

CREATE OR ALTER PROCEDURE sp_GetTableNames
    @ProcedureName NVARCHAR(128)
AS
BEGIN
    SET NOCOUNT ON;

    -- Table to store direct dependencies
    CREATE TABLE #DirectDependencies (
        SchemaName NVARCHAR(128),
        ObjectName NVARCHAR(128),
        ObjectType CHAR(2)
    )

    -- Insert direct dependencies
    INSERT INTO #DirectDependencies (SchemaName, ObjectName, ObjectType)
    SELECT DISTINCT
        OBJECT_SCHEMA_NAME(referenced_id) AS SchemaName,
        OBJECT_NAME(referenced_id) AS ObjectName,
        CASE 
            WHEN OBJECTPROPERTY(referenced_id, 'IsTable') = 1 THEN 'T'
            WHEN OBJECTPROPERTY(referenced_id, 'IsView') = 1 THEN 'V'
        END AS ObjectType
    FROM sys.sql_expression_dependencies
    WHERE referencing_id = OBJECT_ID(@ProcedureName)
        AND referenced_entity_name IS NOT NULL
        AND referenced_id IS NOT NULL
        AND (OBJECTPROPERTY(referenced_id, 'IsTable') = 1 OR OBJECTPROPERTY(referenced_id, 'IsView') = 1)

    -- Table to store view dependencies
    CREATE TABLE #ViewDependencies (
        ViewSchema NVARCHAR(128),
        ViewName NVARCHAR(128),
        ReferencedSchema NVARCHAR(128),
        ReferencedTable NVARCHAR(128)
    )

    -- Get dependencies for each view
    INSERT INTO #ViewDependencies (ViewSchema, ViewName, ReferencedSchema, ReferencedTable)
    SELECT 
        dd.SchemaName AS ViewSchema,
        dd.ObjectName AS ViewName,
        OBJECT_SCHEMA_NAME(sed.referenced_id) AS ReferencedSchema,
        OBJECT_NAME(sed.referenced_id) AS ReferencedTable
    FROM #DirectDependencies dd
    INNER JOIN sys.objects o ON dd.SchemaName = SCHEMA_NAME(o.schema_id) AND dd.ObjectName = o.name AND o.type = 'V'
    INNER JOIN sys.sql_expression_dependencies sed ON o.object_id = sed.referencing_id
    WHERE OBJECTPROPERTY(sed.referenced_id, 'IsTable') = 1

    -- Display results
    SELECT 
        dd.SchemaName + '.' + dd.ObjectName AS FullObjectName,
        dd.ObjectType,
        STUFF((
            SELECT ', ' + ReferencedSchema + '.' + ReferencedTable
            FROM #ViewDependencies vd
            WHERE vd.ViewSchema = dd.SchemaName AND vd.ViewName = dd.ObjectName
            FOR XML PATH(''), TYPE).value('.', 'NVARCHAR(MAX)'), 1, 2, '') AS ReferencedTables
    FROM #DirectDependencies dd
    ORDER BY dd.ObjectType, dd.SchemaName, dd.ObjectName

    -- Clean up
    DROP TABLE #DirectDependencies
    DROP TABLE #ViewDependencies
END