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.
Step 2: By executing below query ,you can get column dependency
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: 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.
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.
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
No comments:
Post a Comment