Identify references of an object in SQL Server

Problem Statement:

Today, there was a requirement from one of my colleague “How to identify a function references in SQL Server?”.

Infact, He was looking for a script to identify the reference places(objects) where the function is being used in SQL Server. Please find the script below that we used to identify the references.


SET @ObjectName = N'split' --Give your function/proc name

--Table variable to hold the results from different databases
Declare @ResultTable Table(SourceSchema sysname, Sourceobject sysname, ReferencedDB sysname, ReferencedSchema sysname, ReferencedObject sysname)

Declare @MyQuery NVARCHAR(MAX) = N'
USE [?]
	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,
	,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 + '%''';

Insert into @ResultTable
EXEC sp_MSforeachdb  @MyQuery

Select * From @ResultTable

The above script uses sp_MSforeachdb to execute the query in all databases available in the SQL Server.

Please note that sp_MSforeachdb is an undocumented procedure, Microsoft may change the functionality or definition of this Stored Procedure at any time.

See Also:

You may be interested to know more on sp_MSforeachdb usage, please visit the below links


2 thoughts on “Identify references of an object in SQL Server

Leave a Reply

Fill in your details below or click an icon to log in: Logo

You are commenting using your account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s