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
