Foreign Keyof all tables for a sqlserver db with tables and columns
2007-10-02 (first published: 2002-06-20)
15,451 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