SQL Server Dependencies in Other Databases

Question

What I am trying to do: I am trying to get a list of views, stored procedures, etc and their dependencies.

For example, I have a stored procedure named [sp_1] and to make [sp_1], I need table_1, view_1, and view_2.

I am using the query below and it appears to work for the most part; however, if my [referenceing_object_name] (aka [sp_1]) uses objects in another database, they do not appear in my query.

So if I have

[db_1].[dbo].[sp_1]

and it is made up of the following

[db_1].[dbo].[table_1]
[db_1].[dbo].[view_1]
[db_2].[dbo].[view_2]

It will only show me [db_1].[dbo].[table_1] and [db_1].[dbo].[view_1] in my results.

Any suggestions on how to get objects that appear in other databases?

select top 100
    [sed_objects_depend_on].[referencing_id]
    ,[s].[name] as [schema_name]
    ,[o_referencing].[name] as [referencing_object_name]
    ,[o_referencing].[type_desc] as [referencing_type_desc]
    ,[sed_objects_depend_on].[Referenced_Database_Name]
    ,[sed_objects_depend_on].[referenced_schema_name]
    ,[sed_objects_depend_on].[Referenced_Entity_Name]
    ,[sed_objects_depend_on].[Referenced_ID]
    ,[o_referenced].[name] as [referenced_object_name]
    ,[o_referenced].[type_desc] as [referenced_type_desc]
from
    [sys].[sql_expression_dependencies] as [sed_objects_depend_on]
------------------------
inner join
    [sys].[objects] as o_referencing
    on
        o_referencing.[object_id] = [sed_objects_depend_on].[referencing_id]
------------------------
inner join
    [sys].[objects] as o_referenced
    on
        o_referenced.[object_id] = [sed_objects_depend_on].[referenced_id]
------------------------
inner join
    [sys].[schemas] as s
    on
        o_referencing.[schema_id] = s.[schema_id]
------------------------
where
    [sed_objects_depend_on].[Referencing_ID] IN 
    (
        '657437416'
        -----
        ,'1823514471'
        ,'1467672822'
        ,'233468306'
    ) 
order by
    --[sed_objects_depend_on].[Referenced_Entity_Name]
    [o_referencing].[name]
    ,[sed_objects_depend_on].[Referenced_Entity_Name];

This perhaps is what you're looking for? https://stackoverflow.com/questions/720339/how-to-select-data-of-a-table-from-another-database-in-sql-server — Vlam, Apr 15 '21 at 01:30
Nope, I'm trying to view dependencies, not query across servers. — Chicken Sandwich No Pickles, Apr 15 '21 at 01:31
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. — Vlam, Apr 15 '21 at 01:41
That seems to have done the trick, I'll have to adjust accordingly. — Chicken Sandwich No Pickles, Apr 15 '21 at 01:57
I have 2 objects with the same object_id when I query the same object_id in 2 different databases. This makes joining the referencing_id difficult to get the object_name. Any suggestions? — Chicken Sandwich No Pickles, Apr 15 '21 at 02:00
hmmm....I wonder if there is some function I can use to grab the schema name for the referencing object....schema_name(referencing_id) doesn't seem to work, but it would help out. — Chicken Sandwich No Pickles, Apr 15 '21 at 02:08
ah, that did the trick, it seems that if I joined using the referencing tables and not the referenced, I was able to solve my problem with the missing referencing information. — Chicken Sandwich No Pickles, Apr 15 '21 at 02:22
This solved my problem, post it as the answer and I'll mark it as so. — Chicken Sandwich No Pickles, Apr 15 '21 at 02:22

score 1 · Accepted Answer · answered Apr 15 '21 at 02:46

1

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.

answered Apr 15 '21 at 02:46

Vlam

1,622
1
8
17

SQL Server Dependencies in Other Databases

1 Answers1