Getting foreign key columns metadata from view

Question

Question is in the title :). I need to know if it is possible or not.

Here is how to do it for table

SELECT
    OBJECT_NAME(f.parent_object_id) as tbl, 
    OBJECT_NAME(fc.referenced_object_id) as refTable, 
    COL_NAME(f.parent_object_id, fc.parent_column_id) as foreignKeyColumnName 
FROM sys.foreign_keys f 
INNER JOIN sys.foreign_key_columns AS fc ON f.OBJECT_ID = fc.constraint_object_id
WHERE f.parent_object_id = OBJECT_ID('dbo.TableName')

marc_s · Accepted Answer · 2011-03-10 15:30:16Z

1

Views are typically just "pre-stored" SELECT statements based on tables.

Views themselves don't have any physical representation in your database, and therefore, you don't have foreign keys on views - you have them on the underlying base tables

answered Mar 10, 2011 at 15:30

marc_s

760k186 gold badges1.4k silver badges1.5k bronze badges

Sign up to request clarification or add additional context in comments.

2 Comments

Danil Over a year ago

Thanks Marc, maybe it is possible to get it from assoc tables through view.

marc_s Over a year ago

@Danil: sure - you just need to know what tables the view relies on and then use your code you've posted..

Collectives™ on Stack Overflow

Getting foreign key columns metadata from view

1 Answer 1

2 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

2 Comments

Your Answer

Sign up or log in

Post as a guest

Related