Tag: SQL Script

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'