Tags

, ,

I have one user defined function , but i couldn’t find where the function is and where the function is using.. anyone pls help me to overcome this one.?

The below script will help you find all the references within each database.

DECLARE @ObjectName NVARCHAR(100)
SET @ObjectName = N'split' --Give your function/object name
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

References

sys.sql_expression_dependencies
sys.dm_sql_referenced_entities

Note

The system procedure sp_MSforeachdb is neither documented nor officially supported by Microsoft.
Hope this helps…!

Advertisements