September 10, 2012 at 4:13 pm
Comments posted to this topic are about the item Find Foreign Keys
September 19, 2012 at 3:02 pm
soory i am getting an error
September 20, 2012 at 11:34 pm
Not sure if there's any whitespace problem. Please use the below query.
;With CTE As
(SelectObject_Name(constraint_object_id) Constraint_Name,
Object_Name(parent_object_id) Table_Name,
C.name Column_Name
From sys.foreign_key_columns FK
Inner Join sys.columns C
On FK.parent_object_id = C.object_id
And FK.parent_column_id = C.column_id)
SelectC.Constraint_Name,
C.Table_Name,
C.Column_Name,
Object_Name(FK.referenced_object_id) Referenced_Table_Name,
SC.name Referenced_Column_Name
from CTE C
Inner Join sys.foreign_key_columns FK
On C.Constraint_Name = Object_Name(FK.constraint_object_id)
Inner Join sys.columns SC
On FK.referenced_object_id = SC.object_id
And FK.referenced_column_id = SC.column_id
September 20, 2012 at 11:34 pm
Not sure if there's any whitespace problem. Please use the below query.
;With CTE As
(SelectObject_Name(constraint_object_id) Constraint_Name,
Object_Name(parent_object_id) Table_Name,
C.name Column_Name
From sys.foreign_key_columns FK
Inner Join sys.columns C
On FK.parent_object_id = C.object_id
And FK.parent_column_id = C.column_id)
SelectC.Constraint_Name,
C.Table_Name,
C.Column_Name,
Object_Name(FK.referenced_object_id) Referenced_Table_Name,
SC.name Referenced_Column_Name
from CTE C
Inner Join sys.foreign_key_columns FK
On C.Constraint_Name = Object_Name(FK.constraint_object_id)
Inner Join sys.columns SC
On FK.referenced_object_id = SC.object_id
And FK.referenced_column_id = SC.column_id
October 8, 2012 at 4:37 am
SELECT
FK_Table = FK.TABLE_NAME,
FK_Column = CU.COLUMN_NAME,
PK_Table = PK.TABLE_NAME,
PK_Column = PT.COLUMN_NAME,
Constraint_Name = C.CONSTRAINT_NAME
FROM
INFORMATION_SCHEMA.REFERENTIAL_CONSTRAINTS C
INNER JOIN
INFORMATION_SCHEMA.TABLE_CONSTRAINTS FK
ON C.CONSTRAINT_NAME = FK.CONSTRAINT_NAME
INNER JOIN
INFORMATION_SCHEMA.TABLE_CONSTRAINTS PK
ON C.UNIQUE_CONSTRAINT_NAME = PK.CONSTRAINT_NAME
INNER JOIN
INFORMATION_SCHEMA.KEY_COLUMN_USAGE CU
ON C.CONSTRAINT_NAME = CU.CONSTRAINT_NAME
INNER JOIN
(
SELECT
i1.TABLE_NAME, i2.COLUMN_NAME
FROM
INFORMATION_SCHEMA.TABLE_CONSTRAINTS i1
INNER JOIN
INFORMATION_SCHEMA.KEY_COLUMN_USAGE i2
ON i1.CONSTRAINT_NAME = i2.CONSTRAINT_NAME
WHERE i1.CONSTRAINT_TYPE = ‘PRIMARY KEY’
) PT
ON PT.TABLE_NAME = PK.TABLE_NAME
ORDER BY
FK_Column
May 9, 2016 at 1:14 pm
Thanks for the script.
Viewing 6 posts - 1 through 6 (of 6 total)
You must be logged in to reply to this topic. Login to reply
This website stores cookies on your computer.
These cookies are used to improve your website experience and provide more personalized services to you, both on this website and through other media.
To find out more about the cookies we use, see our Privacy Policy