question

-----------Find the object References-------- SELECT DISTINCT o.name AS ObjectName, CASE o.xtype WHEN 'C' THEN 'CHECK constraint' WHEN 'D' THEN 'Default or DEFAULT constraint' WHEN 'F' THEN 'FOREIGN KEY constraint' WHEN 'FN' THEN 'Scalar function' WHEN 'IF' THEN 'In-lined table-function' WHEN 'K' THEN 'PRIMARY KEY or UNIQUE constraint' WHEN 'L' THEN 'Log' WHEN 'P' THEN 'Stored procedure' WHEN 'R' THEN 'Rule' WHEN 'RF' THEN 'Replication filter stored procedure' WHEN 'S' THEN 'System table' WHEN 'TF' THEN 'Table function' WHEN 'TR' THEN 'Trigger' WHEN 'U' THEN 'User table' WHEN 'V' THEN 'View' WHEN 'X' THEN 'Extended stored procedure' ELSE o.xtype END AS ObjectType, ISNULL( p.Name, 'Location') AS Location FROM syscomments c INNER JOIN sysobjects o ON c.id= o.id LEFT JOIN sysobjects p ON o.Parent_obj= p.id WHERE c.text LIKE '%supportKBCats_ToProduct%' ORDER BY Location, ObjectName or sp_depends 'dbo.supportKBCats_ToProduct' Hope this helps

sys.sql_expression_dependencies has the info you need. Here is what I would use: USE [D_ReportData] -- <--- MUST use SP's Host DB GO /**************************************************************************** Description: Get SP Object Dependencies For more info on dependencies / referenced objects: http://msdn.microsoft.com/en-us/library/bb677315.aspx Tables Used: sys.sql_expression_dependencies sys.objects NOTE: Access to system tables depends on your permissions ****************************************************************************/ -- Config Parameters -------------------------------------------------------------------------- DECLARE @SP varchar(max) = '%margin%'; -- PROC name REGEX -- Get Dependencies -------------------------------------------------------------------------- SELECT DISTINCT @@SERVERNAME AS [Srv] , db_name() AS [DB] , OBJECT_NAME(referencing_id) AS [SP] -- Description of the class of referencing entity. Is not nullable -- OBJECT_OR_COLUMN | DATABASE_DDL_TRIGGER | SERVER_DDL_TRIGGER , referenced_class_desc -- Won't work since reference_db is likely different from current db -- unless you dynamic SQL on a second pass --, [objects].type_desc -- Populated for cross-server dependencies that are made by specifying a valid -- four-part name. NULL for non-schema-bound entities for which the entity was -- referenced without specifying a four-part name. NULL for schema-bound entities -- because they must be in the same database and therefore can only be defined -- using a two-part (schema.object) name. -- Therefore, I conclude it should be same as SP [Server] if null. , COALESCE(NULLIF(referenced_server_name, ''), @@SERVERNAME) AS referenced_server_name -- Populated for cross-database or cross-server references that are made by -- specifying a valid three-part or four-part name. NULL for non-schema-bound -- references when specified using a one-part or two-part name. NULL for schema -- bound entities because they must be in the same database and therefore can -- only be defined using a two-part (schema.object) name. -- Therefore, I conclude it should be same as SP [Database] if null. , COALESCE(NULLIF(referenced_database_name, ''), db_name()) AS referenced_database_name -- NULL for non-schema-bound references in which the entity was referenced without -- specifying the schema name. Never NULL for schema-bound references because schema -- bound entities must be defined and referenced by using a two-part name. , referenced_schema_name -- Name of the referenced entity. Is not nullable. , referenced_entity_name FROM [sys].[sql_expression_dependencies] -- Objects 1:1 (name is unique at db level) -- Dont use reference_id coz its NULL for cross-server and cross-database references -- BUT, you can only get the Object Type if it is in the same DB -- LEFT JOIN [sys].[objects] -- ON [sql_expression_dependencies].referenced_entity_name = [objects].name WHERE -- Search Objects OBJECT_NAME(referencing_id) LIKE @SP ORDER BY [Srv] , [DB] , [SP] , referenced_server_name , referenced_database_name , referenced_schema_name , referenced_entity_name ;