Mapeando Dependências entre Objetos no SQL Server

Visualizações: 5.654 views

Neste artigo

Tempo de Leitura: 8 minutos

Olá pessoal,
Bom dia!

Neste post vou mostrar a vocês como encontrar dependências entre objetos de vários níveis de hierarquia no SQL Server utilizando queries Transact-SQL, simulando um comportamento semelhante ao que nos é apresentado na interface do Management Studio.

Utilizando o SQL Server Management Studio

Essa é a forma mais fácil de realizar esse mapeamento, pois não exige nenhum conhecimento técnico, basta utilizar a interface do Management Studio. Um contra dessa implementação é que você não pode trabalhar com os dados retornados para criar algum levantamento ou mapeamento em massa por exemplo, ou definição de nível de hierarquia para a busca. Além disso, essa tela não mostra as dependências cross-databases, ou seja, dependências entre objetos de databases diferentes.

Para visualizar as dependências, basta abrir o Object Explorer, selecionar o objeto que deseja visualizar as dependências (no exemplo, escolhi a tabela Clientes), clicar com o botão direito e selecionar a opção “View Dependencies”

Uma vez que a tela é aberta, você pode escolher visualizar:
– Objetos que dependem do objeto em questão (Objects that depend on [Clientes])
– Outros objetos que o objeto em questão possui dependência (Objects on which [Clientes] depends)

Essa tela lista tanto dependências direta (nível 1), quando um objeto depende diretamente de outro quanto dependências indiretas (quando um objeto depende de outro objeto e esse outro objeto é que possui a dependência). Quando mais níveis aparecem entre o objeto inicial e o final, maior é a hierarquia deles.

Utilizando Transact-SQL

Nos exemplos abaixo, vou demonstrar como listar os objetos e suas dependências utilizando queries T-SQL, fazendo uso das DMV’s sys.dm_sql_referenced_entities e sys.dm_sql_referencing_entities e da view de catálogo sys.sql_expression_dependencies.

Utilizando a sp_depends

Com o uso dessa SP de sistema, pode-se rapidamente listar os dependências de um objeto (apenas 1º nível e não é cross-database)

EXEC sp_depends @objname = N'dbo.Clientes'

1	EXEC sp_depends @objname = N'dbo.Clientes'

Exemplo:

Utilizando a sp_MSdependencies

Uma outra SP de sistema que pode ajudar nessa situação, é a sp_MSdependencies. Apesar de não ser cross-database, ela permite visualizar os objetos dependentes do objeto X (Flag 1315327) e os que o objeto X depende (Flag 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

-- 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

Exemplos:

Utilizando a view de catálogo syscomments

Utilizando essa view de catálogo, pode-se facilmente realizar uma busca textual (é busca de texto, não é por objeto) entre os objetos para tentar entrar uma string específica em objetos como views, functions, procedures, triggers, etc. Esse recurso não é cross-database e retorna apenas dependências de 1º nível.

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

SELECT DISTINCT

B.name

FROM

syscomments A

INNER JOIN sysobjects B ON A.id = B.id

WHERE

CHARINDEX('Clientes', text) > 0

Exemplo:

Utilizando a view de catálogo INFORMATION_SCHEMA.ROUTINES

Com a query abaixo, podemos fazer uma busca textual em procedures e functions que possuem uma string no nome, como o nome do objeto que estamos buscando dependências. Essa solução não é cross-database e é uma busca textual.

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

SELECT

ROUTINE_CATALOG,

ROUTINE_SCHEMA,

ROUTINE_NAME,

ROUTINE_TYPE,

ROUTINE_DEFINITION

FROM

INFORMATION_SCHEMA.ROUTINES

WHERE

ROUTINE_DEFINITION LIKE '%Clientes%'

Dependências Cross Database

Com a query abaixo, é possível selecionar todas as dependências cross-database, onde os objetos do banco atual da conexão possuem dependências para outros databases.

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

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

Exemplo:

Dependências de schema-bound

Com a query abaixo, é possível identificar a mapear as dependências do tipo schema-bound, como views indexadas (criadas com o hint SCHEMABINDING), colunas calculadas e check constraints:

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

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

Exemplo de Retorno:

Demonstração da dependência:

Mostrando dependências em vários níveis

Com a query abaixo é possível listar as dependências em vários níveis hierárquicos, da mesma forma que a interface do SQL Server Management Studio nos mostra

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

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

Exemplo:

Encontrando dependências por tipo de dado

Como você deve saber, os tipos de dados TEXT, NTEXT e IMAGE serão descontinuados e não mais suportados em futuras versões do SQL Server. Se você planeja realizar o upgrade da sua aplicação e substituir esses tipos, a query abaixo pode ser um bom ponto de partida. A query abaixo vai mostrar todos os objetos que utilizam esses tipos de dados e suas dependências:

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

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

Relatório completo de dependências

A query abaixo vai mostrar uma linha para cada objeto do database que possua dependências, com os objetos dependentes separados por vírgula.

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

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

Exemplo:

Procedure de Dependências Diretas

Após demonstrar todas essas utilizações, vou compartilhar uma stored procedure que eu uso sempre que preciso listar as dependências cross-database rapidamente. Existe um pré-requisito para criar essa SP, que é a fncSplit, que deve ser criada antes.

Essa procedure lista todas as dependências diretas de um objeto, de modo cross-database:
Visualizar código-fonte

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'

100

101

102

103

104

105

106

107

108

109

110

111

112

113

114

115

116

117

118

119

CREATE PROCEDURE dbo.stpVerifica_Dependencias_Diretas (

@Ds_Objeto_Completo VARCHAR(255),

@Ds_Tabela_Destino VARCHAR(100) = NULL

)

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'

Exemplo:

Procedure Cross-database e multi-nível

Com a procedure abaixo, que utiliza CTE e recursividade, é possível listar todos os objetos dependentes com vários níveis de hierarquia no banco de origem e as dependências diretas (1º nível) e cross-database.

Visualizar código-fonte

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'

100

101

102

103

104

105

106

107

108

109

110

111

112

113

114

115

116

117

118

119

120

121

122

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)

(

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

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

Exemplo:

Como identificar, apagar e recriar Foreign Keys (FK)

Caso você queira identificar ou recriar dependências a nível de chaves Foreign Key’s, leia o artigo Como identificar, apagar e recriar Foreign Keys (FK) de uma tabela no SQL Server

É isso aí, pessoal!
Até o próximo post!

Other Languages

Assinar blog por e-mail

Visualizações do Blog

Power Alerts

Curso Adm de Bancos no Azure

Curso Segurança de SQL Server

Minhas Certificações

Arquivo de Posts

Categorias

Posts recentes

Mapeando Dependências entre Objetos no SQL Server

Utilizando o SQL Server Management Studio

Utilizando Transact-SQL

Utilizando a sp_depends

Utilizando a sp_MSdependencies

Utilizando a view de catálogo syscomments

Utilizando a view de catálogo INFORMATION_SCHEMA.ROUTINES

Dependências Cross Database

Dependências de schema-bound

Mostrando dependências em vários níveis

Encontrando dependências por tipo de dado

Relatório completo de dependências

Procedure de Dependências Diretas

Procedure Cross-database e multi-nível

Como identificar, apagar e recriar Foreign Keys (FK)

sql server tsql query árvore dependência dependências dependency tree nest nested level walker

You may also like...

Deixe uma resposta Cancelar resposta

Utilizando o SQL Server Management Studio

Utilizando Transact-SQL

Utilizando a sp_depends

Utilizando a sp_MSdependencies

Utilizando a view de catálogo syscomments

Utilizando a view de catálogo INFORMATION_SCHEMA.ROUTINES

Dependências Cross Database

Dependências de schema-bound

Mostrando dependências em vários níveis

Encontrando dependências por tipo de dado

Relatório completo de dependências

Procedure de Dependências Diretas

Procedure Cross-database e multi-nível

Como identificar, apagar e recriar Foreign Keys (FK)

sql server tsql query árvore dependência dependências dependency tree nest nested level walker

You may also like...

Como recuperar o maior valor entre múltiplas colunas em uma tabela do SQL Server

SQL Server – Como ler, importar e exportar dados de arquivos XML

SQL Server – Como consultar informações do Active Directory (AD) utilizando Linked Server (ADSI)

Deixe uma resposta Cancelar resposta