Obtain column names of referenced objects

Posted on

Question :

I used a script from here: https://stackoverflow.com/questions/13757387/getting-sql-server-cross-database-dependencies that Milica Medic posted and it works GREAT thank you 🙂 my question is how do I get the column name? so it finds table name from stored proc but can it also find the column name that is being used? thank you in advance

The query Referenced above is.

CREATE PROCEDURE [dbo].[get_crossdatabase_dependencies] AS

SET NOCOUNT ON;

CREATE TABLE #databases(
    database_id int, 
    database_name sysname
);

-- ignore systems databases
INSERT INTO #databases(database_id, database_name)
SELECT database_id, name FROM sys.databases
WHERE database_id > 4;  

DECLARE 
    @database_id int, 
    @database_name sysname, 
    @sql varchar(max);

CREATE TABLE #dependencies(
    referencing_database varchar(max),
    referencing_schema varchar(max),
    referencing_object_name varchar(max),
    referenced_server varchar(max),
    referenced_database varchar(max),
    referenced_schema varchar(max),
    referenced_object_name varchar(max)
);

WHILE (SELECT COUNT(*) FROM #databases) > 0 BEGIN
    SELECT TOP 1 @database_id = database_id, 
                 @database_name = database_name 
    FROM #databases;

    SET @sql = 'INSERT INTO #dependencies select 
        DB_NAME(' + convert(varchar,@database_id) + '), 
        OBJECT_SCHEMA_NAME(referencing_id,' 
            + convert(varchar,@database_id) +'), 
        OBJECT_NAME(referencing_id,' + convert(varchar,@database_id) + '), 
        referenced_server_name,
        ISNULL(referenced_database_name, db_name(' 
             + convert(varchar,@database_id) + ')),
        referenced_schema_name,
        referenced_entity_name
    FROM ' + quotename(@database_name) + '.sys.sql_expression_dependencies';

    EXEC(@sql);

    DELETE FROM #databases WHERE database_id = @database_id;
END;

SET NOCOUNT OFF;

SELECT * FROM #dependencies;

Answer :

As much as I’ve tried, I cannot get sys.sql_expression_dependencies to display any column details.

However, there is a Table-Valued-Function, sys.dm_sql_referenced_entities(), that can be employed to help us in our quest for referenced column details.

The following displays a single table denoting all referenced entities, with column details where appropriate, for the given SQL Server instance:

DECLARE @AllCmds nvarchar(max);
DECLARE @cmd nvarchar(max);
DECLARE cur CURSOR LOCAL FORWARD_ONLY
FOR
SELECT 
'SELECT ReferencingDatabaseName = ''' + d.name + '''
    , ReferencingSchemaName = s.name
    , ReferencingObjectName = o.name
    , ReferencedClass = sed.referenced_class_desc
    , ReferencedServerName = sed.referenced_server_name
    , ReferencedDatabaseName = ISNULL(sed.referenced_database_name, ''' + d.name + ''')
    , ReferencedSchemaName = sed.referenced_schema_name
    , ReferencedEntityName = sed.referenced_entity_name
    , ReferencedColumnNames = (STUFF((
        SELECT '', '' + COALESCE(dsre.referenced_schema_name, '''') + 
                ''.'' + COALESCE(dsre.referenced_entity_name, '''') + 
                ''.'' + COALESCE(dsre.referenced_minor_name, '''')
        FROM sys.dm_sql_referenced_entities(s.name + ''.'' + o.name, N''OBJECT'') dsre
        WHERE dsre.referenced_minor_id > 0
        FOR XML PATH ('''')
    ), 1, 2, ''''))
FROM ' + QUOTENAME(d.name) + '.sys.sql_expression_dependencies sed
    LEFT JOIN ' + QUOTENAME(d.name) + '.sys.objects o ON sed.referencing_id = o.object_id
    LEFT JOIN ' + QUOTENAME(d.name) + '.sys.schemas s ON o.schema_id = s.schema_id
'
FROM sys.databases d
ORDER BY d.name;
OPEN cur;
FETCH NEXT FROM cur INTO @cmd;
WHILE @@FETCH_STATUS = 0
BEGIN
    SET @AllCmds = CASE WHEN @AllCmds IS NOT NULL THEN @AllCmds + 'UNION ALL
' ELSE '' END + @cmd;
    FETCH NEXT FROM cur INTO @cmd;
END
CLOSE cur;
DEALLOCATE cur;
PRINT @AllCmds;
EXEC sys.sp_executesql @AllCmds;

The output contains a single row per referencing object. If that referencing object includes column references, those column names will be listed in comma-delimited form under the ReferencedColumnNames column.

Leave a Reply

Your email address will not be published.