Find the Database Where User Defined Object Located and Where it is Being Referred
Introduction
This article is based on a recent forum post asking whether there is any way to list the databases where a particular user defined function is located and also user wanted to know where this function is being referred. In this article I'm trying to put together all the suggestions and code samples received through various replies to arrive at a solution.
Performing the test
The first solution was to use an undocumented system stored procedure sp_MSforeachdb.
 Caution |
|---|
| This article uses the undocumented system stored procedure sp_MSforeachdb. Undocumented means it is safe to use in production but any support related to this command cannot be claimed from Microsoft.It also means that any change can be made to this command without officially notifying end user so unexpected results might come if used on same environment after some time. Although their usage is safe, keep in mind to use it in test environments only to avoid any impact to production processes. Syntax for this undocumented procedure is EXEC sp_MSforeachdb @command (Where @command is a variable-length string) . Refer this for more information. |
exec sp_MSforeachdb 'use ? if exists(SELECT * from sys.objects where type = ''fn'' and name = ''split'' ) begin; print ''?''; end;';
A limitation of this method is that it only lists the database names where it is located, but it doesn't list other objects referring this one.
Alternative method was to use catalog view sys.sql_expression_dependencies along with dynamic management function sys.dm_sql_referenced_entities as shown below.
DECLARE @ObjectName NVARCHAR(100)
SET @ObjectName = 'split' --Give your function
SELECT DISTINCT
SourceSchema = OBJECT_SCHEMA_NAME(sed.referencing_id)
,SourceObject = OBJECT_NAME(sed.referencing_id)
,ReferencedDB = ISNULL(sre.referenced_database_name, DB_NAME())
,ReferencedSchema = ISNULL(sre.referenced_schema_name,
OBJECT_SCHEMA_NAME(sed.referencing_id))
,ReferencedObject = sre.referenced_entity_name
FROM sys.sql_expression_dependencies sed
CROSS APPLY sys.dm_sql_referenced_entities(OBJECT_SCHEMA_NAME(sed.referencing_id)
+ '.' + OBJECT_NAME(sed.referencing_id), 'OBJECT') sre
WHERE sed.referenced_entity_name = @ObjectName
AND sre.referenced_entity_name = @ObjectName
A limitation with this approach is that the sys.sql_expression_dependencies catalog view contains information in the current database. We need to scroll through each and every database to get the information. To scroll through either you need to run the above command against each database or you need to use a cursor.
To overcome above limitations, use above code in conjunction with MS_foreachdb as below.
DECLARE @ObjectName NVARCHAR(100)
SET @ObjectName = N'split' --Give your function
Declare @MyQuery NVARCHAR(MAX) = N'
USE [?]
SELECT DISTINCT
SourceSchema = OBJECT_SCHEMA_NAME(sed.referencing_id)
,SourceObject = OBJECT_NAME(sed.referencing_id)
,ReferencedDB = ISNULL(sre.referenced_database_name, DB_NAME())
,ReferencedSchema = ISNULL(sre.referenced_schema_name,
OBJECT_SCHEMA_NAME(sed.referencing_id))
,ReferencedObject = sre.referenced_entity_name
FROM sys.sql_expression_dependencies sed
CROSS APPLY sys.dm_sql_referenced_entities(OBJECT_SCHEMA_NAME(sed.referencing_id) + ' '.' ' + OBJECT_NAME(sed.referencing_id), ' 'OBJECT' ') sre
WHERE sed.referenced_entity_name like ' '%' + @ObjectName + '%'' AND sre.referenced_entity_name like ''%' + @ObjectName + '%''
' ;
EXEC sp_MSforeachdb @MyQuery
Below is the sample result set.
Note
Refer sys.sql_expression_dependencies and sys.dm_sql_referenced_entities to know more.
Please find the link to download the full code from the Gallery - Link
Conclusion
As demonstrated above, one can make use of sys.sql_expression_dependencies catalog view and sys.dm_sql_referenced_entities DMF in conjunction with undocumented system stored procedure sp_MSforeachdb to search any objects in any databases in a SQL Instance with all the objects referring to it.