In SQL Server 2008 new Dynamic Management Functions and a System View introduced to keep track of Object Dependencies.
DMFs in SQL Server 2008 to keep track of object dependencies
sys.dm_sql_referenced_entities
sys.dm_sql_referencing_entities
System View in SQL Server 2008 to keep track of object dependencies
sys.sql_expression_dependencies
sys.sql_expression_dependencies: You can use the sys.sql_expression_dependencies catalog view to report dependency information for a given database. Cross-database entities are returned only when a valid four-part or three-part name is specified.
sys.dm_sql_referenced_entities: Return one row for each user-defined entity referenced by name in the definition of the specified referencing entity. The result set is limited to the entities that are referenced by the specified referencing entity.
sys.dm_sql_referencing_entities: You can use the sys.dm_sql_referencing_entities dynamic management function to return one row for each user-defined entity in the current database that references another user-defined entity by name.
Execute the below queries and check the output.
--create a sample database create database db_pool use db_pool --create a sample table create table tb1 ( id int, name varchar(10) ) insert into tb1 values(10,'Jugal') go 50; --create a procedure referencing tb1 table CREATE procedure sp1 as begin select * from DB_POOL.DBO.tb1 end exec sp1 SELECT @@SERVERNAME LocalServer, referenced_server_name, referenced_database_name, referenced_schema_name, referenced_entity_name FROM sys.dm_sql_referenced_entities ('dbo.sp1','OBJECT') SELECT * FROM sys.dm_sql_referencing_entities ('dbo.TB1','OBJECT') SELECT @@SERVERNAME LocalServer, referenced_server_name, referenced_database_name, referenced_schema_name, referenced_entity_name FROM sys.dm_sql_referencing_entities ('dbo.sp1','OBJECT') SELECT OBJECT_NAME(referencing_id) AS referencing_entity_name, referenced_database_name AS database_name, referenced_schema_name, referenced_entity_name FROM sys.sql_expression_dependencies WHERE referenced_entity_name = 'tb1' select * FROM sys.sql_expression_dependencies SELECT @@SERVERNAME LocalServer, OBJECT_NAME (referencing_id) referencing_object_name, referenced_server_name, referenced_database_name, referenced_schema_name, referenced_entity_name FROM sys.sql_expression_dependencies where referenced_entity_name = 'tb1' create procedure sp2 as begin exec sp1 end SELECT @@SERVERNAME LocalServer, OBJECT_NAME (referencing_id) referencing_object_name, referenced_server_name, referenced_database_name, referenced_schema_name, referenced_entity_name FROM sys.sql_expression_dependencies where referenced_entity_name = 'sp1'