Getting foreign key columns metadata from view
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_i开发者_运维问答d, 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')
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
精彩评论