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 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply