Get Foreign Key of all the tables for Sqlserver DB

,

This script will create two views and select the data from the TableReferences view. This view will give list of all the foreignkeys of the selected database with its constraint name, the table name and column name(s) on which the constraint is created and the referencing table name and column name(s).

CREATE VIEW dbo.ConstraintAndTableName
AS
SELECT     sysobjects_1.name AS ChildTablename, dbo.sysobjects.id, dbo.sysobjects.parent_obj, dbo.sysobjects.name AS ConstraintName
FROM         dbo.sysforeignkeys INNER JOIN
                      dbo.sysobjects ON dbo.sysforeignkeys.constid = dbo.sysobjects.id AND dbo.sysforeignkeys.fkeyid = dbo.sysobjects.parent_obj INNER JOIN
                      dbo.sysobjects sysobjects_1 ON dbo.sysobjects.parent_obj = sysobjects_1.id

Go

CREATE VIEW dbo.TableReferences
AS
SELECT DISTINCT 
                      TOP 100 PERCENT syso1.name AS TableName, sysc1.name AS ColumnName, syso2.name AS MasterTableName, 
                      sysc2.name AS MasterColumnName, dbo.ConstraintAndTableName.ConstraintName, sysc1.colid
FROM         dbo.sysforeignkeys sysfk INNER JOIN
                      dbo.sysobjects syso1 ON sysfk.fkeyid = syso1.id INNER JOIN
                      dbo.sysobjects syso2 ON sysfk.rkeyid = syso2.id INNER JOIN
                      dbo.syscolumns sysc1 ON sysfk.fkey = sysc1.colid AND sysc1.id = syso1.id INNER JOIN
                      dbo.syscolumns sysc2 ON sysfk.rkey = sysc2.colid AND sysc2.id = syso2.id INNER JOIN
                      dbo.ConstraintAndTableName ON sysfk.constid = dbo.ConstraintAndTableName.id AND 
                      syso1.name = dbo.ConstraintAndTableName.ChildTablename
ORDER BY syso2.name
go

Select * from dbo.TableReferences

Rate

5 (2)

Share

Share

Rate

5 (2)