Technical Article

Script for all Foreign Keys of all tables of a DB

,

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

Rate

5 (2)

You rated this post out of 5. Change rating

Share

Share

Rate

5 (2)

You rated this post out of 5. Change rating