Query to get database references in original defined format

Rohit Bhagwani

2y873SQL

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)

For eg : A view has below SELECT Query :

SELECT * From db1.ABC.table1 AS t1
LEFT JOIN db2.[ABC].table2 AS t2 ON t1.Col1 = t2.Col2
LEFT JOIN db1.[ABC].[table1] AS t3 ON t2.Col1 = t3.Col

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.

4 Replies

Know the answer? Post it — somebody with the same question will find it here.

Rohit Bhagwani2y ago
Thankyou Nadnan, Namish, Jayraj for your repsonse.

Just to add, We have extracted the code from the database in visual studio, where we want to identify all the references (in the original format) in order to replace them with new objects.

So the desired output would be as below :

db1.ABC.table1
db2.[ABC].table2
db1.[ABC].[table1]

Nandan Hegde2y ago

A slight correction to the query shared by Jayraj :

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

Naimish Makwana2y ago

You can try below query.

SELECT DISTINCT
    OBJECT_SCHEMA_NAME(referencing_id) + '.' + OBJECT_NAME(referencing_id) AS referencing_object,
    dep.referenced_entity_name AS referenced_object
FROM sys.sql_expression_dependencies AS dep
CROSS APPLY (
    SELECT QUOTENAME(referenced_database_name) + '.' + QUOTENAME(referenced_schema_name) + '.' + QUOTENAME(referenced_entity_name)
) AS Q(referenced_object_name)
WHERE referencing_database_id = DB_ID() -- Filter for dependencies within the current database
AND dep.referenced_database_name <> DB_NAME() -- Exclude references within the same database
ORDER BY referencing_object, referenced_object;

Thanks

Jayraj Chhaya2y ago
To retrieve the object references in their original format, you can use the following T-SQL query:
```
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
    referencing_database_name = 'YourDatabaseName' -- Replace with the name of your database
    AND referencing_entity_name = 'YourEntityName' -- Replace with the name of your entity
```
In this query, you need to replace 'YourDatabaseName' with the name of your database and 'YourEntityName' with the name of the entity you want to retrieve the object references for.

The sys.sql_expression_dependencies system view contains information about the dependencies between database objects. By joining this view with the necessary columns and using the DISTINCT keyword, you can retrieve the distinct object references in their original format.

For example, if you want to retrieve the object references for a view named 'YourViewName' in the 'YourDatabaseName' database, you would modify the query as follows:
```
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
    referencing_database_name = 'YourDatabaseName'
    AND referencing_entity_name = 'YourViewName'
```
This query will return the distinct object references in their original format, such as 'db1.ABC.table1', 'db2.[ABC].table2', and 'db1.[ABC].[table1]'.