Query to get database dependencies

Rohit 231 Reputation points
2023-12-29T13:00:02.2033333+00:00

Hello,

We have an On-Premise database that we want to migrate to Azure via DMA, this database contains multiple objects which are dependent on objects from another database in the same server. We are trying to identify such dependencies.

Example of few of the references are as below:

databasename.schemaname.objectname
databasename.[schemaname].[objectname]

Is there a T-SQL query which can help us get the object references in the original format? (Edited)

User's image

Here the objects are defined in different valid ways, I want to get the distinct values for the references (as shown below) in their actual defined way, can this be done via t-sql query?

db1.ABC.table1

db2.[ABC].table2

db1.[ABC].[table1]

Thanks for the help in advance.

SQL Server
SQL Server
A family of Microsoft relational database management and analysis systems for e-commerce, line-of-business, and data warehousing solutions.
14,488 questions
Transact-SQL
Transact-SQL
A Microsoft extension to the ANSI SQL language that includes procedural programming, local variables, and various support functions.
4,690 questions
0 comments No comments
{count} votes

3 answers

Sort by: Most helpful
  1. Erland Sommarskog 117.2K Reputation points MVP
    2023-12-29T22:41:50.7366667+00:00

    You can use the view sys.sql_expression_dependencies to find such dependencies, but the view is not fully covering; typically if fails to record queries with temp tables.

    On my web site I have an article Where Is that Table Used? It's aiming at a different scenario from yours, but the information can still be useful to you. I discuss a few more alternatives in the article.


  2. LiHongMSFT-4306 30,751 Reputation points
    2024-01-02T01:40:55.02+00:00

    Hi @R-5499

    Try this query:

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

    Best regards,

    Cosmog Hong


    If the answer is the right solution, please click "Accept Answer" and kindly upvote it. If you have extra questions about this answer, please click "Comment".

    Note: Please follow the steps in our Documentation to enable e-mail notifications if you want to receive the related email notification for this thread.


  3. Nandan Hegde 34,171 Reputation points MVP
    2024-01-02T13:50:10.38+00:00

    The below query might help :

    SELECT DISTINCT
        referenced_database_name + '.' +
        CASE WHEN referenced_schema_name IS NULL THEN '' ELSE referenced_schema_name + '.' END +
        referenced_entity_name AS object_reference
    FROM
        sys.sql_expression_dependencies
    WHERE
        referenced_database_name = '<<databasename>>'
    
    0 comments No comments

Your answer

Answers can be marked as Accepted Answers by the question author, which helps users to know the answer solved the author's problem.