What Was I Thinking?

Follies & Foils of .NET Development
posts - 94 , comments - 351 , trackbacks - 0

Get a list of all columns that participate in Foreign Key relationships

The following query returns all the column information for columns in the specified table that participate in a FK relationship.

You can modify the query to return PK information by changing the constriant_type filter.

 

select * from information_schema.columns
where table_name = <TableName> AND table_schema=<Schema>and column_name not in (SELECT Col.Column_Name  from
    INFORMATION_SCHEMA.TABLE_CONSTRAINTS Tab,
    INFORMATION_SCHEMA.CONSTRAINT_COLUMN_USAGE Col
WHERE
    Col.Constraint_Name = Tab.Constraint_Name
    AND Col.Table_Name = Tab.Table_Name
    AND Constraint_Type = 'FOREIGN KEY'
    AND Col.Table_Name = <TableName>)

Print | posted on Thursday, June 25, 2015 6:20 PM | Filed Under [ SQL ]

Feedback

Gravatar

# re: Get a list of all columns that participate in Foreign Key relationships

thank you to post what you know. I thin it is a valuable things for all.
6/25/2015 7:20 PM | gkmamun
Post A Comment
Title:
Name:
Email:
Comment:
Verification:
 

Powered by: