Sql Server - Object dependencies


Dependencies by creating Primary & Foreign keys:

 

CREATE DATABASE TEST_DEPENDENCY_DB1
GO 
USE TEST_DEPENDENCY_DB1
GO
CREATE TABLE TEST_DEPENDENCY_PRIMARY(EMPID INT PRIMARY KEY,NAME VARCHAR(10))
INSERT INTO TEST_DEPENDENCY_PRIMARY SELECT 1 ,'Sathya'
INSERT INTO TEST_DEPENDENCY_PRIMARY SELECT 2 ,'Deepak'
CREATE TABLE TEST_DEPENDENCY_SECONDARY(EMPID INT FOREIGN KEY REFERENCES TEST_DEPENDENCY_PRIMARY (EMPID),[ROLE] VARCHAR(10))
INSERT INTO TEST_DEPENDENCY_SECONDARY SELECT 1 ,'Developer'
INSERT INTO TEST_DEPENDENCY_SECONDARY SELECT 2 ,'DBA'
Under Object Explorer, right click Table - > View Dependencies
After creating foreign key constraint, you can see dependencies created for tables  TEST_DEPENDENCY_PRIMARY & TEST_DEPENDENCY_SECONDARY

 

Cross-database dependencies:

Example:  


CREATE DATABASE TEST_DEPENDENCY_DB2
GO 

USE TEST_DEPENDENCY_DB2
GO
CREATE VIEW TEST_DEPENDENCY_VIEW1
AS 
SELECT a.EMPID,a.NAME,b.[ROLE] 
FROM TEST_DEPENDENCY_DB1.dbo.TEST_DEPENDENCY_PRIMARY a,
TEST_DEPENDENCY_DB1.dbo.TEST_DEPENDENCY_SECONDARY b
WHERE a.EMPID = b.EMPID
In SSMS,you can see dependencies created for view created in one database & tables  in another database on which view refers ,as shown in below image.

You can also see dependencies created for view created in one database & tables  in another database on which view refers, by executing below query :


USE TEST_DEPENDENCY_DB2
GO
 SELECT OBJECT_NAME (referencing_id),referenced_database_name,
referenced_schema_name, referenced_entity_name
FROM sys.sql_expression_dependencies
WHERE referenced_database_name IS NOT NULL;

COLUMN DEPENDENCY:


Step 1: Create table with one column referencing two other column in same table.


CREATE TABLE TEST_DEPENDENCY_COLUMN (a INT, b INT,C AS a * b );
GO

Step 2: By executing below query ,you can get column dependency


SELECT referenced_schema_name ,referenced_entity_name,referenced_minor_name ,
COALESCE(COL_NAME(OBJECT_ID(N'dbo.TEST_DEPENDENCY_COLUMN'),referencing_minor_id), 'N/A') 
FROM sys.dm_sql_referenced_entities ('dbo.TEST_DEPENDENCY_COLUMN', 'OBJECT');
GO

 Note: referenced_minor_id  =  Column ID when the referenced entity is a column; otherwise, 0.
Note: sys.dm_sql_referenced_entities system function will report any column-level dependency only for schema-bound references.

 

What happens when referenced object is not found :


Step 1: Create procedure with table that does not exists.


CREATE PROCEDURE TEST_DEPENDENCY_PROCEDURE 
AS
SELECT * FROM TABLE_NOTIN_DB  

Step 2: Execute below queries to check referencing & referenced objects by using sys.dm_sql_referencing_entities & sys.dm_sql_referenced_entities functions.




SELECT referencing_schema_name, referencing_entity_name, referencing_id, 
referencing_class_desc, is_caller_dependent
FROM sys.dm_sql_referencing_entities ('dbo.TABLE_NOTIN_DB', 'OBJECT');
 
SELECT referenced_schema_name, referenced_entity_name, referenced_minor_name,
referenced_minor_id, referenced_class_desc, is_caller_dependent, is_ambiguous
FROM sys.dm_sql_referenced_entities ('dbo.TEST_DEPENDENCY_PROCEDURE', 'OBJECT');




You will get below error message, because table does not exists in the database.


Msg 2020, Level 16, State 1, Line 1

The dependencies reported for entity "dbo.TEST_DEPENDENCY_PROCEDURE" do not include references to columns. This is either because the entity references an object that does not exist or because of an error in one or more statements in the entity. Before rerunning the query, ensure that there are no errors in the entity and that all objects referenced by the entity exist.


Also in SSMS, under View Dependencies, you can notice Type as “unresolved entity”, Which means object does not exists.
 






After creating table, check referencing & referenced objects by using sys.dm_sql_referencing_entities & sys.dm_sql_referenced_entities functions.



Step 3: Create table that does not exist in database but referenced inside procedure


CREATE TABLE TABLE_NOTIN_DB(COL INT)





Step 4: Execute below queries to check referencing & referenced objects by using
sys.dm_sql_referencing_entities & sys.dm_sql_referenced_entities functions.




SELECT referencing_schema_name, referencing_entity_name, 
referencing_id, referencing_class_desc, is_caller_dependent
FROM sys.dm_sql_referencing_entities ('dbo.TABLE_NOTIN_DB', 'OBJECT');


 SELECT referenced_schema_name, referenced_entity_name, referenced_minor_name,
referenced_minor_id,referenced_class_desc, is_caller_dependent, is_ambiguous
FROM sys.dm_sql_referenced_entities ('dbo.TEST_DEPENDENCY_PROCEDURE', 'OBJECT'); 

You can also get dependency information using :



1.) using sys.sql_expression_dependencies



Query 1: To get referenced entity using referencing entity
 
SELECT OBJECT_NAME(referencing_id) AS referencing_entity_name, 
o.type_desc AS referencing_desciption, 
COALESCE(COL_NAME(referencing_id, referencing_minor_id), '(n/a)') AS referencing_minor_id, 
referencing_class_desc, referenced_class_desc,
referenced_server_name, referenced_database_name, referenced_schema_name,
referenced_entity_name, 
COALESCE(COL_NAME(referenced_id, referenced_minor_id), '(n/a)') AS referenced_column_name,
is_caller_dependent, is_ambiguous
FROM sys.sql_expression_dependencies AS sed
INNER JOIN sys.objects AS o 
ON sed.referencing_id = o.object_id
WHERE referencing_id = OBJECT_ID('TEST_DEPENDENCY_PROCEDURE');



 

2.) INFORMATION_SCHEMA.ROUTINES  - for routine such as the stored procedure, trigger, etc...


SELECT routine_name, routine_type  
FROM INFORMATION_SCHEMA.ROUTINES  
WHERE ROUTINE_DEFINITION LIKE '%TEST_DEPENDENCY_PROCEDURE%'



3.)      EXEC SP_DEPENDS 'DBO.TABLE_NOTIN_DB'



4.)     EXEC sp_MSdependencies 'DBO.TABLE_NOTIN_DB'

-- undocumented system stored procedure
 

See Also:




No comments: