Dependencies

One of the fundamentals of an RDBMS is the R. R for relational – meaning there are often multiple objects within a database schema that relate to, or are dependent on other objects. The first one that will spring to mind for anyone who’s restored a database onto new server in it’s entirety is of course users and logins. But I’m talking here about how items within the schema (database scoped) relate to one another. Here a few useful queries.

Firstly there are two DMFs: sys.dm_sql_referencing_entities & sys.dm_sql_referenced_entities. These have to be used in the database context, sys.dm_sql_referencing_entities() takes an object name and a referencing class (OBJECT | TYPE | XML_SCHEMA_COLLECTION | PARTITION_FUNCTION ) and returns one row for each class that references the object. The function sys.dm_sql_referenced_entities takes similar arguments (refernced classes are OBJECT | DATABASE_DDL_TRIGGER | SERVER_DDL_TRIGGER ) and works the other way around.

Here a couple of quick examples that work with the standard Adventure Works databases and should give the general idea:

use AdventureWorks;
go
select * from sys.dm_sql_referencing_entities('Production.Product','OBJECT');

select * from sys.dm_sql_referenced_entities('HumanResources.uspUpdateEmployeeHireInfo','OBJECT')

Of course foreign keys are king and here are two approaches to finding them – both need to be executed within the database context:

SELECT f.name AS ForeignKey,
OBJECT_SCHEMA_NAME(f.parent_object_id) + '.' + OBJECT_NAME(f.parent_object_id) AS TableName,
COL_NAME(fc.parent_object_id,
fc.parent_column_id) AS ColumnName,
OBJECT_SCHEMA_NAME(f.referenced_object_id) + '.' + OBJECT_NAME (f.referenced_object_id) AS ReferenceTableName,
COL_NAME(fc.referenced_object_id,
fc.referenced_column_id) AS ReferenceColumnName
FROM sys.foreign_keys AS f
INNER JOIN sys.foreign_key_columns AS fc
ON f.[object_id] = fc.constraint_object_id
SELECT
    rc.CONSTRAINT_NAME,
    ccu.TABLE_SCHEMA +'.'+ ccu.TABLE_NAME 'Referencing Table',
    ccu.COLUMN_NAME 'Referencing Column',
    ccu.TABLE_SCHEMA +'.'+ ccu.TABLE_NAME 'Referenced Table',
    ccu.COLUMN_NAME 'Referenced Column'
FROM
    INFORMATION_SCHEMA.REFERENTIAL_CONSTRAINTS rc
INNER JOIN
    INFORMATION_SCHEMA.CONSTRAINT_COLUMN_USAGE ccu
      ON rc.CONSTRAINT_CATALOG = ccu.CONSTRAINT_CATALOG
         AND rc.CONSTRAINT_NAME = ccu.CONSTRAINT_NAME

The first uses the system catalog views while the second uses the information schema. Horses for courses.

Advertisements
This entry was posted in Database Theory, T-SQL. Bookmark the permalink.

One Response to Dependencies

  1. dazpoc says:

    Brilliant – make’s me want to be a better DBA.

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s