Getting SQL Server Cross Database Dependencies

Getting SQL Server Cross database Dependencies

You can compare your results to the ones that the following script finds.
Here is the full article

CREATE PROCEDURE [dbo].[get_crossdatabase_dependencies] AS

SET NOCOUNT ON;

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

INSERT INTO #databases(database_id, database_name)
SELECT database_id, [name]
FROM sys.databases
WHERE 1 = 1
    AND [state] <> 6 /* ignore offline DBs */
    AND database_id > 4; /* ignore system DBs */

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

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

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

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

    EXEC(@sql);

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

SET NOCOUNT OFF;

SELECT * FROM #dependencies;

SQL Server - CROSS DEPENDENCIES DATABASES

The system views (e.g., sys.object) are views, effectively under the schema sys.

So you should be able to get object types etc from statements like

SELECT type_desc
FROM yourOtherDatabaseName.sys.Objects
WHERE name = 'yourObjectToCheck'

Note - Information_schema is also similar - you can use them like

SELECT TOP 10 * from
yourOtherDatabaseName.INFORMATION_SCHEMA.tables

SQL Server Dependencies in Other Databases

Try this

 SELECT Object_name (referencing_id),
       referenced_database_name,
       referenced_schema_name,
       referenced_entity_name
FROM   sys.sql_expression_dependencies
WHERE  referenced_database_name IS NOT NULL;  

and see what you get.

How to find out the dependencies of stored procedure using sql

I use this script in a similar situation (don't forget to use the schema name):

--
DECLARE
    @sp nvarchar(100)
SET @sp = N'dbo.usp_Constant_Get_Pvt'

-- Objects that depends on [@sp]
SELECT 
    referencing_schema_name, 
    referencing_entity_name
FROM sys.dm_sql_referencing_entities(@sp, 'OBJECT')

-- Objects on which [@sp] depends
SELECT 
    referenced_schema_name, 
    referenced_entity_name
FROM sys.dm_sql_referenced_entities(@sp, 'OBJECT')

SELECT
    referenced_schema_name, 
    referenced_entity_name
FROM sys.sql_expression_dependencies
WHERE referencing_id = OBJECT_ID(@sp)

How to find all the dependencies of a table in sql server

The following are the ways we can use to check the dependencies:

Method 1: Using sp_depends

 sp_depends 'dbo.First'
 GO

Method 2: Using information_schema.routines

 SELECT *
 FROM information_schema.routines ISR
 WHERE CHARINDEX('dbo.First', ISR.ROUTINE_DEFINITION) > 0
 GO

Method 3: Using DMV sys.dm_sql_referencing_entities

 SELECT referencing_schema_name, referencing_entity_name,
 referencing_id, referencing_class_desc, is_caller_dependent
 FROM sys.dm_sql_referencing_entities ('dbo.First', 'OBJECT');
 GO

Tree of all dependencies in a SQL Server database

There are paid tools like redgate but if you want, you can always right click an object and select "view dependencies".

---

Related Topics