Mapeando Dependências entre Objetos no SQL Server

Visualizações: 6.327 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!

Assinar blog por e-mail

Visualizações do Blog

Microsoft MVP

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

Prova de certificação Microsoft MCSE 70-776 (Performing Big Data Engineering on Microsoft Cloud Services) de graça (beta) até 08/09/2017

SQL Server Express – DESAFIO: É possível ultrapassar o limite de 10 GB de dados em uma base?

SQL Server – Quebrando strings em sub-strings utilizando separador (Split string)

Deixe uma respostaCancelar resposta