Mapeando dependencias entre objetos en SQL Server

Hola, chicos,
¡Buen día!

En esta publicación, le mostraré cómo encontrar dependencias entre objetos en varios niveles de jerarquía en SQL Server usando consultas Transact-SQL, simulando un comportamiento similar al que se presenta en la interfaz de Management Studio.

Usando SQL Server Management Studio

Esta es la forma más sencilla de realizar este mapeo, ya que no requiere ningún conocimiento técnico, basta con utilizar la interfaz de Management Studio. Un inconveniente de esta implementación es que no se puede trabajar con los datos devueltos para crear alguna encuesta o mapeo masivo, por ejemplo, o definir un nivel de jerarquía para la búsqueda. Además, esta pantalla no muestra dependencias entre bases de datos, es decir, dependencias entre objetos de diferentes bases de datos.

Para ver las dependencias, simplemente abra el Explorador de objetos, seleccione el objeto cuyas dependencias desea ver (en el ejemplo, elegí la tabla Clientes), haga clic derecho y seleccione la opción "Ver dependencias".

SQL Server - Dependencias Management Studio — SQL Server - Estudio de gestión de dependencias

Una vez que se abre la pantalla, puede elegir ver:
– Objetos que dependen de [Clientes])
– Otros objetos de los que el objeto en cuestión tenga dependencias (Objetos de los que [Clientes] depende)

SQL Server - Dependencias Management Studio - Niveis — SQL Server - Estudio de gestión de dependencias - Niveles

Esta pantalla enumera tanto las dependencias directas (nivel 1), cuando un objeto depende directamente de otro, como las dependencias indirectas (cuando un objeto depende de otro objeto y ese otro objeto tiene la dependencia). Cuanto más niveles aparecen entre los objetos inicial y final, mayor será su jerarquía.

Usando Transact-SQL

En los ejemplos siguientes, demostraré cómo enumerar objetos y sus dependencias mediante consultas T-SQL, utilizando DMV. sys.dm_sql_referenced_entities y sys.dm_sql_referencing_entities y la vista de catálogo sys.sql_expression_dependencies.

Usando sp_depends

Con este SP del sistema, puede enumerar rápidamente las dependencias de un objeto (solo el primer nivel y no entre bases de datos)

EXEC sp_depends @objname = N'dbo.Clientes'

Ejemplo:

SQL Server- sp_depends — Servidor SQL: sp_depends

Usando sp_MSdependencias

Otro SP del sistema que puede ayudar en esta situación es sp_MSdependencies. Aunque no es una base de datos cruzada, le permite ver los objetos que dependen del objeto X (Indicador 1315327) y aquellos de los que depende el objeto X (Indicador 1053183).

-- Lista os objetos que dependem da tabela dbo.Clientes
EXEC sp_MSdependencies N'dbo.Clientes', null, 1315327

-- Lista os objetos que a tabela dbo.Clientes depente
EXEC sp_MSdependencies N'dbo.Clientes', null, 1053183

Ejemplos:

SQL Server - sp_MSdependencies — SQL Server - dependencias sp_MS

Usando la vista del catálogo de sycomments

Al utilizar esta vista de catálogo, puede realizar fácilmente una búsqueda textual (es una búsqueda de texto, no por objeto) entre objetos para intentar ingresar una cadena específica en objetos como vistas, funciones, procedimientos, activadores, etc. Esta característica no es entre bases de datos y solo devuelve dependencias de primer nivel.

SELECT DISTINCT
    B.name
FROM
    syscomments A
    INNER JOIN sysobjects B ON A.id = B.id
WHERE
    CHARINDEX('Clientes', text) > 0

Ejemplo:

SQL Server - Dependency syscomments — SQL Server: comentarios del sistema de dependencia

Usando la vista de catálogo INFORMACIÓN_SCHEMA.ROUTINES

Con la siguiente consulta podemos realizar una búsqueda textual en procedimientos y funciones que tengan una cadena en su nombre, como por ejemplo el nombre del objeto del que buscamos dependencias. Esta solución no es entre bases de datos y es una búsqueda textual.

SELECT 
    ROUTINE_CATALOG,
    ROUTINE_SCHEMA,
    ROUTINE_NAME,
    ROUTINE_TYPE,
    ROUTINE_DEFINITION
FROM	
    INFORMATION_SCHEMA.ROUTINES
WHERE 
    ROUTINE_DEFINITION LIKE '%Clientes%'

Dependencias cruzadas de bases de datos

Con la consulta siguiente, es posible seleccionar todas las dependencias entre bases de datos, donde los objetos de la base de datos actual de la conexión tienen dependencias en otras bases de datos.

SELECT
    OBJECT_NAME(referencing_id) AS referencing_object,
    referenced_database_name,
    referenced_schema_name,
    referenced_entity_name
FROM
    sys.sql_expression_dependencies
WHERE
    referenced_database_name IS NOT NULL
    AND is_ambiguous = 0

Ejemplo:

SQL Server - Cross database dependency — SQL Server: dependencia de bases de datos cruzadas

Dependencias vinculadas al esquema

Con la consulta siguiente, es posible identificar y asignar dependencias vinculadas al esquema, como vistas indexadas (creadas con la sugerencia SCHEMABINDING), columnas calculadas y restricciones de verificación:

SELECT
    OBJECT_NAME(d.referencing_id) AS referencing_name,
    o.type_desc referencing_object_type,
    d.referencing_minor_id AS referencing_column_id,
    cc2.name AS referencing_column_name,
    d.referenced_entity_name,
    d.referenced_minor_id AS referenced_column_id,
    cc.name AS referenced_column_name
FROM
    sys.sql_expression_dependencies d
    JOIN sys.all_columns cc ON d.referenced_minor_id = cc.column_id AND d.referenced_id = cc.[object_id]
    JOIN sys.objects o ON d.referencing_id = o.[object_id]
    LEFT JOIN sys.all_columns cc2 ON d.referencing_minor_id = cc2.column_id AND d.referencing_id = cc2.[object_id]
WHERE
    d.is_schema_bound_reference = 1
    AND d.referencing_minor_id > 0

Ejemplo de devolución:

SQL Server Schema-Bound Dependency ResultSet — Conjunto de resultados de dependencia vinculado al esquema de SQL Server

Demostración de dependencia:

SQL Server Schema-Bound Dependency — Dependencia vinculada al esquema de SQL Server

Mostrar dependencias en múltiples niveles

Con la consulta a continuación es posible listar dependencias en varios niveles jerárquicos, de la misma forma como nos muestra la interfaz de SQL Server Management Studio

WITH Arvore_Dependencias ( referenced_id, referenced_name, referencing_id, referencing_name, NestLevel )
AS (
    SELECT
        A.[object_id] AS referenced_id,
        A.name AS referenced_name,
        A.[object_id] AS referencing_id,
        A.name AS referencing_name,
        0 AS NestLevel
   FROM
        sys.objects A
   WHERE
        A.name = 'Clientes'
   
   UNION ALL
   
   SELECT
        A.referenced_id,
        OBJECT_NAME(A.referenced_id),
        A.referencing_id,
        OBJECT_NAME(A.referencing_id),
        NestLevel + 1
   FROM
        sys.sql_expression_dependencies		A
        JOIN Arvore_Dependencias		B	ON A.referenced_id = B.referencing_id
)
SELECT DISTINCT
    referenced_id,
    referenced_name,
    referencing_id,
    referencing_name,
    NestLevel
FROM
    Arvore_Dependencias
WHERE
    NestLevel > 0
ORDER BY
    NestLevel,
    referencing_id

Ejemplo:

SQL Server - Dependency Tree — SQL Server: árbol de dependencias

Encontrar dependencias por tipo de datos

Como sabrá, los tipos de datos TEXT, NTEXT e IMAGE quedarán obsoletos y ya no serán compatibles con versiones futuras de SQL Server. Si planea actualizar su aplicación y reemplazar estos tipos, la siguiente consulta puede ser un buen punto de partida. La siguiente consulta mostrará todos los objetos que utilizan estos tipos de datos y sus dependencias:

WITH Arvore_Dependencias
AS (
    SELECT DISTINCT
        A.name,
        A.[object_id] AS referenced_id,
        A.name AS referenced_name,
        A.[object_id] AS referencing_id,
        A.name AS referencing_name,
        0 AS NestLevel
    FROM
        sys.objects						A
        JOIN sys.columns					B	ON	A.[object_id] = B.[object_id]
    WHERE
        A.is_ms_shipped = 0 
        AND B.system_type_id IN ( 34, 99, 35 ) -- TEXT, NTEXT e IMAGE
    
    UNION ALL
    
    SELECT
        B.name,
        A.referenced_id,
        OBJECT_NAME(A.referenced_id),
        A.referencing_id,
        OBJECT_NAME(A.referencing_id),
        NestLevel + 1
    FROM
        sys.sql_expression_dependencies		A
        JOIN Arvore_Dependencias		B	ON	A.referenced_id = B.referencing_id
 )
SELECT
    name AS parent_object_name,
    referenced_id,
    referenced_name,
    referencing_id,
    referencing_name,
    NestLevel
FROM
    Arvore_Dependencias t1
WHERE
    NestLevel > 0
ORDER BY
    name,
    NestLevel

Informe de dependencia completo

La siguiente consulta mostrará una línea para cada objeto de la base de datos que tenga dependencias, con los objetos dependientes separados por una coma.

SELECT
    DB_NAME() AS dbname,
    o.type_desc AS referenced_object_type,
    d1.referenced_entity_name,
    d1.referenced_id,
    STUFF((
            SELECT
                ', ' + OBJECT_NAME(d2.referencing_id)
            FROM
                sys.sql_expression_dependencies d2
            WHERE
                d2.referenced_id = d1.referenced_id
            ORDER BY
                OBJECT_NAME(d2.referencing_id)
            FOR XML PATH('')
          ), 1, 1, '') AS dependent_objects_list
FROM
    sys.sql_expression_dependencies d1
    JOIN sys.objects o ON d1.referenced_id = o.[object_id]
GROUP BY
    o.type_desc,
    d1.referenced_id,
    d1.referenced_entity_name
ORDER BY
    o.type_desc,
    d1.referenced_entity_name

Ejemplo:

SQL Server - Dependency Report — SQL Server: informe de dependencia

Procedimiento de Dependencias Directas

Después de demostrar todos estos usos, compartiré un procedimiento almacenado que uso siempre que necesito enumerar rápidamente las dependencias entre bases de datos. Existe un requisito previo para crear este SP, que es el fncDividir, que debe crearse primero.

Este procedimiento enumera todas las dependencias directas de un objeto, de forma cruzada:
Ver código fuente

CREATE PROCEDURE dbo.stpVerifica_Dependencias_Diretas (
    @Ds_Objeto_Completo VARCHAR(255),
    @Ds_Tabela_Destino VARCHAR(100) = NULL
)
AS
BEGIN


    SET NOCOUNT ON


    -- DECLARE @Ds_Objeto_Completo SYSNAME = 'Dacasa..Cliente', @Ds_Tabela_Destino VARCHAR(100) = '##Teste'
    
    
    DECLARE 
        @Ds_Database VARCHAR(255),
        @Ds_Schema VARCHAR(255),
        @Ds_Objeto VARCHAR(255),
        @Query NVARCHAR(MAX),
        @Tabela_Temp VARCHAR(100) = '##Lista_Dependencias_Objeto_' + CAST(CAST(RAND() * 999999 AS INT) AS VARCHAR(100)),
        @Tabela_Destino VARCHAR(100)


    SET @Tabela_Destino = (CASE WHEN @Ds_Tabela_Destino IS NULL THEN @Tabela_Temp ELSE @Ds_Tabela_Destino END)

    
    SELECT
        @Ds_Database = dbo.fncSplit(@Ds_Objeto_Completo, '.', 1),
        @Ds_Schema = dbo.fncSplit(@Ds_Objeto_Completo, '.', 2),
        @Ds_Objeto = dbo.fncSplit(@Ds_Objeto_Completo, '.', 3)
    

    
    SET @Query = N'
IF (OBJECT_ID(''tempdb..' + @Tabela_Destino + ''') IS NOT NULL) DROP TABLE ' + @Tabela_Destino + ';
CREATE TABLE ' + @Tabela_Destino + ' (
    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)
);'
    
    EXEC sp_executesql @Query
    
    
    IF (OBJECT_ID('tempdb..#Databases') IS NOT NULL) DROP TABLE #databases
    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	WITH(NOLOCK)
    WHERE database_id > 4;  


    DECLARE 
        @database_id int, 
        @database_name sysname


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


        SET @Query = '
INSERT INTO ' + @Tabela_Destino + ' 
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	WITH(NOLOCK)
WHERE
    referenced_entity_name = ''' + @Ds_Objeto + ''';'

        
        EXEC sys.sp_executesql @Query


        DELETE FROM #databases WHERE database_id = @database_id;

        
    END	
    
    
    
    IF (@Ds_Tabela_Destino IS NULL)
    BEGIN

        SET @Query = '
SELECT * FROM ' + @Tabela_Destino + ';
IF (OBJECT_ID(''tempdb..' + @Tabela_Destino + ''') IS NOT NULL) DROP TABLE ' + @Tabela_Destino + ';'

        EXEC sp_executesql @Query


    END
    
    
END;

-- EXEC dbo.stpVerifica_Dependencias_Diretas 'Testes.dbo.Clientes'

Ejemplo:

SQL Server - Dependency Procedure Crossbrowser — SQL Server: navegador cruzado de procedimientos de dependencia

Procedimiento entre bases de datos y multinivel.

Con el procedimiento siguiente, que utiliza CTE y recursividad, es posible enumerar todos los objetos dependientes con varios niveles de jerarquía en la base de datos de origen y dependencias directas (primer nivel) y entre bases de datos.

Ver código fuente

CREATE PROCEDURE [dbo].[stpVerifica_Dependencias] (
    @Ds_Objeto_Completo VARCHAR(255),
    @Ds_Tabela_Destino VARCHAR(100) = NULL
)
AS BEGIN


    SET NOCOUNT ON


    -- DECLARE @Ds_Objeto_Completo SYSNAME = 'Dacasa..Cliente', @Ds_Tabela_Destino VARCHAR(100) = '##Teste'
    
    
    DECLARE 
        @Ds_Database VARCHAR(255),
        @Ds_Schema VARCHAR(255),
        @Ds_Objeto VARCHAR(255),
        @Query NVARCHAR(MAX),
        @Tabela_Temp VARCHAR(100) = '##Lista_Dependencias_Objeto_' + CAST(CAST(RAND() * 999999 AS INT) AS VARCHAR(100)),
        @Tabela_Destino VARCHAR(100)


    SET @Tabela_Destino = (CASE WHEN @Ds_Tabela_Destino IS NULL THEN @Tabela_Temp ELSE @Ds_Tabela_Destino END)

    
    SELECT
        @Ds_Database = dbo.fncSplit(@Ds_Objeto_Completo, '.', 1),
        @Ds_Schema = dbo.fncSplit(@Ds_Objeto_Completo, '.', 2),
        @Ds_Objeto = dbo.fncSplit(@Ds_Objeto_Completo, '.', 3)



    SET @Query = N'
IF (OBJECT_ID(''tempdb..' + @Tabela_Destino + ''') IS NOT NULL) DROP TABLE ' + @Tabela_Destino + ';
CREATE TABLE ' + @Tabela_Destino + ' (
    database_name VARCHAR(255) NULL,
    referenced_id INT NULL,
    referenced_name VARCHAR(255) NULL,
    referencing_id INT NULL,
    referencing_name VARCHAR(255) NULL,
    NestLevel INT NULL
);'
    
    EXEC sp_executesql @Query



    SET @Query = '
USE [?];
    
WITH Arvore_Dependencias (referenced_id, referenced_name, referencing_id, referencing_name, NestLevel)
AS
(
    SELECT
        o.[object_id] AS referenced_id,
        CAST(NULL AS VARCHAR(255)) AS referenced_name,
        o.[object_id] AS referencing_id,
        CAST(NULL AS VARCHAR(255)) AS referencing_name,
        0 AS NestLevel
    FROM
        sys.objects o	WITH(NOLOCK)
    WHERE
        o.name = ''' + @Ds_Objeto + '''

    UNION ALL
    
    SELECT
        d1.referenced_id,
        CAST(d1.referenced_entity_name AS VARCHAR(255)) AS referenced_entity_name,
        d1.referencing_id,
        CAST(OBJECT_NAME(d1.referencing_id) AS VARCHAR(255)) AS referencing_name,
        1 AS NestLevel
    FROM
        sys.sql_expression_dependencies d1		WITH(NOLOCK)
    WHERE
        d1.referenced_id IS NULL
        AND d1.referenced_database_name = ''' + @Ds_Database + '''
        AND d1.referenced_schema_name = ''' + @Ds_Schema + '''
        AND d1.referenced_entity_name = ''' + @Ds_Objeto + '''
        
    UNION ALL

    SELECT
        d1.referenced_id,
        CAST(d1.referenced_entity_name AS VARCHAR(255)) AS referenced_entity_name,
        d1.referencing_id,
        CAST(OBJECT_NAME(d1.referencing_id) AS VARCHAR(255)) AS referencing_name,
        NestLevel + 1
    FROM
        sys.sql_expression_dependencies d1		WITH(NOLOCK)
        JOIN Arvore_Dependencias r ON d1.referenced_id = r.referencing_id
)
INSERT INTO ' + @Tabela_Destino + '
SELECT DISTINCT DB_NAME() AS database_name, referenced_id, referenced_name, referencing_id, referencing_name, NestLevel
FROM Arvore_Dependencias
WHERE NestLevel > 0
ORDER BY NestLevel, database_name, referencing_id
OPTION (MAXRECURSION 32);'
    
    
    EXEC sys.sp_MSforeachdb
        @command1 = @Query
    


    IF (@Ds_Tabela_Destino IS NULL)
    BEGIN

        SET @Query = '
SELECT * FROM ' + @Tabela_Destino + ' ORDER BY NestLevel, database_name, referencing_id;
IF (OBJECT_ID(''tempdb..' + @Tabela_Destino + ''') IS NOT NULL) DROP TABLE ' + @Tabela_Destino + ';'

        EXEC sp_executesql @Query


    END


END


-- EXEC dbo.stpVerifica_Dependencias 'Testes.dbo.Clientes'

Ejemplo:

Cómo identificar, eliminar y recrear claves foráneas (FK)

Si desea identificar o recrear dependencias a nivel de clave externa, lea el artículo Cómo identificar, eliminar y recrear claves externas (FK) de una tabla en SQL Server

¡Eso es todo, amigos!
¡Hasta el próximo post!

servidor sql árbol de consultas tsql dependencia dependencias árbol de dependencia nido caminante de nivel anidado

Dirceu Resende

Arquitecto de Bases de Datos y BI · Microsoft MVP · MCSE, MCSA, MCT, MTA, MCP.

WhatsApp Telegram Ver mis certificaciones

Comentários (0)

Carregando comentários…