Foreign Keyof all tables for a sqlserver db with tables and columns
2007-10-02 (first published: 2002-06-20)
15,459 reads
Foreign Keyof all tables for a sqlserver db with tables and columns
/* Foreign Key of all tables for a sqlserver db with tables and columns */
WITH ConstraintAndTableName AS
(SELECT sysobjects1.name AS ChildTablename,
sysobjects.id,
sysobjects.parent_obj,
sysobjects.name AS ConstraintName
FROM sysforeignkeys INNER
JOIN sysobjects ON sysforeignkeys.constid = sysobjects.id
AND sysforeignkeys.fkeyid = sysobjects.parent_obj INNER
JOIN sysobjects AS sysobjects1 ON sysobjects.parent_obj = sysobjects1.id
)
SELECT DISTINCT
syso1.name AS TableName,
sysc1.name AS ColumnName,
sysc1.colid AS Pos,
syso2.name AS MasterTableName,
sysc2.name AS MasterColumnName,
ConstraintAndTableName.ConstraintName
FROM sysforeignkeys sysfk INNER
JOIN sysobjects syso1 ON sysfk.fkeyid = syso1.id INNER
JOIN sysobjects syso2 ON sysfk.rkeyid = syso2.id INNER
JOIN syscolumns sysc1 ON sysfk.fkey = sysc1.colid AND sysc1.id = syso1.id INNER
JOIN syscolumns sysc2 ON sysfk.rkey = sysc2.colid AND sysc2.id = syso2.id INNER
JOIN ConstraintAndTableName ON sysfk.constid = ConstraintAndTableName.id
AND syso1.name = ConstraintAndTableName.ChildTablename
ORDER BY TableName