Obtaining Foreign Key Table-Column References in SQL Server

Borrowing from what others have put together, this query give you the details of foreign keys:

  • The referencing schema, table, and column names
  • The referenced schema, table, and column names
SELECT 
        f.parent_object_id, 
        SCHEMA_NAME(f.schema_id) SchemaName,
        OBJECT_NAME(f.parent_object_id) TableName,
        COL_NAME(fc.parent_object_id,fc.parent_column_id) ColName,
        SCHEMA_NAME(ref.schema_id) ReferencedSchemaName,
        OBJECT_NAME(f.referenced_object_id) ReferencedTableName,
        COL_NAME(fc.referenced_object_id, fc.referenced_column_id) ReferencedColumnName
        FROM sys.foreign_keys AS f
        INNER JOIN sys.foreign_key_columns AS fc ON f.OBJECT_ID = fc.constraint_object_id
        INNER JOIN sys.tables t ON t.OBJECT_ID = fc.referenced_object_id
        INNER JOIN sys.tables ref ON ref.object_id = f.referenced_object_id
        WHERE OBJECT_NAME (f.referenced_object_id) = 'Employee'

This example queries the foreign key relationships of the table “Employee” in the AdventureWorks sample database that Microsoft provides.  Here’s the results of the query:

awfk_exampleThis is an important part of the dynamic data explorer that I’m writing as an application example in my upcoming book Imperative to Function Programming for SyncFusion‘s “Succinctly” e-book series.

Advertisements

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