Mapping Object Dependencies in SQL Server

Views: 5.635 views

In this article

Reading Time: 8 minutes

Hello people,
Good Morning!

In this post I will show you how to find dependencies between objects of various levels of hierarchy in SQL Server using Transact-SQL queries, simulating a behavior similar to what is presented in the Management Studio interface.

Using SQL Server Management Studio

This is the easiest way to do this mapping, as it requires no technical knowledge, just use the Management Studio interface. One drawback to this implementation is that you cannot work with the returned data to create some survey or mass mapping for example, or hierarchy level definition for the search. Also, this screen does not show cross-database dependencies, ie dependencies between different database objects.

To view the dependencies, just open the Object Explorer, select the object you want to view the dependencies on (in the example, I chose the Customers table), right-click and select the “View Dependencies” option

Once the screen opens, you can choose to view:
- Objects that depend on the object in question (Objects that depend on [Clients])
- Other objects that the object in question depends on (Objects on which [Clients] depends)

This screen lists both direct dependencies (1 level), when one object depends directly on another, and indirect dependencies (when one object depends on another object and that other object has the dependency). The more levels appear between the start and end objects, the greater their hierarchy.

Using Transact-SQL

In the examples below, I will demonstrate how to list objects and their dependencies using T-SQL queries, using DMV's sys.dm_sql_referenced_entities e sys.dm_sql_referencing_entities and from the catalog view sys.sql_expression_dependencies.

Using sp_depends

Using this system SP, you can quickly list an object's dependencies (1 level only and not cross-database)

EXEC sp_depends @objname = N'dbo.Clientes'

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

Example:

Using sp_MSdependencies

Another system SP that can help in this situation is sp_MSdependencies. Although not cross-database, it allows you to view objects dependent on object X (Flag 1315327) and those on which object X depends (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

Examples:

Using the syscomments catalog view

Using this catalog view, you can easily perform a text search (it's text search, not object search) between objects to try to enter a specific string into objects such as views, functions, procedures, triggers, etc. This feature is not cross-database and only returns 1 level dependencies.

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

Example:

Using the INFORMATION_SCHEMA.ROUTINES catalog view

With the query below, we can do a textual search on procedures and functions that have a string in the name, such as the name of the object we are looking for dependencies. This solution is not cross-database and is a textual search.

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

Cross Database Dependencies

With the query below, it is possible to select all cross-database dependencies, where the objects of the current connection database have dependencies for other 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

Example:

Schema-bound Dependencies

With the query below, you can identify how to map schema-bound dependencies, such as indexed views (created with hint SCHEMABINDING), calculated columns, and 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

Return Example:

Dependency Statement:

Showing dependencies at various levels

Using the query below, you can list dependencies at various hierarchical levels, just as the SQL Server Management Studio interface shows us.

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

Example:

Finding Dependencies by Data Type

As you may know, the TEXT, NTEXT, and IMAGE data types will be deprecated and no longer supported in future versions of SQL Server. If you plan on upgrading your application and replacing these types, the query below may be a good starting point. The query below will show all objects that use these data types and their dependencies:

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

Complete Dependency Report

The query below will show one row for each database object that has dependencies, with the dependent objects separated by commas.

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

Example:

Direct Dependencies Procedure

After demonstrating all these uses, I will share a stored procedure that I use whenever I need to list cross-database dependencies quickly. There is a prerequisite to create this SP, which is the fncSplit, which must be created before.

This procedure lists all direct dependencies of an object, cross-database:
View source

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'

Example:

Cross-database and multi-level procedure

With the procedure below, which uses CTE and recursion, it is possible to list all dependent objects with various levels of hierarchy in the source bank and the direct dependencies (1 level) and cross-database.

View source

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'

Example:

How to identify, delete and recreate Foreign Keys (FK)

If you want to identify or recreate dependencies at the level of Foreign Key's, read the article How to identify, delete, and recreate Foreign Keys (FK) from a table in SQL Server

That's it folks!
Until the next post!

Other Languages

Subscribe to a blog by email

Blog Views

Power Alerts

Azure Banking Admin Course

SQL Server Security Course

My Certifications

Posts Archive

Categories

Recent Posts

Mapping Object Dependencies in SQL Server

Using SQL Server Management Studio

Using Transact-SQL

Using sp_depends

Using sp_MSdependencies

Using the syscomments catalog view

Using the INFORMATION_SCHEMA.ROUTINES catalog view

Cross Database Dependencies

Schema-bound Dependencies

Showing dependencies at various levels

Finding Dependencies by Data Type

Complete Dependency Report

Direct Dependencies Procedure

Cross-database and multi-level procedure

How to identify, delete and recreate Foreign Keys (FK)

sql server tsql query tree dependencies dependencies tree nest nested level walker

You may also like ...

Leave a Comment Cancel reply

Using SQL Server Management Studio

Using Transact-SQL

Using sp_depends

Using sp_MSdependencies

Using the syscomments catalog view

Using the INFORMATION_SCHEMA.ROUTINES catalog view

Cross Database Dependencies

Schema-bound Dependencies

Showing dependencies at various levels

Finding Dependencies by Data Type

Complete Dependency Report

Direct Dependencies Procedure

Cross-database and multi-level procedure

How to identify, delete and recreate Foreign Keys (FK)

sql server tsql query tree dependencies dependencies tree nest nested level walker

You may also like ...

SQL Server - How to read and write events in the Windows Event Viewer using the CLR (C #)

SQL Server - Using STRING_SPLIT to transform strings from a row into columns

SQL Server - How to Track Orders and Mail Objects (After WEBSRO Deactivated)

Leave a Comment Cancel reply