What Was I Thinking?

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

Thursday, June 25, 2015

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>)

Posted On Thursday, June 25, 2015 6:20 PM | Comments (1) | Filed Under [ SQL ]

Powered by: