how to get all relationship between the table in sql database (Key, constraint)

SELECT OBJECT_NAME(OBJECT_ID) AS NameofConstraint, SCHEMA_NAME(schema_id) AS SchemaName, OBJECT_NAME(parent_object_id) AS TableName, type_desc AS ConstraintType FROM sys.objects WHERE type_desc LIKE ‘%key%’

 

Select object_name(rkeyid) [Master_Table_Name], object_name(fkeyid) [Child_Table_Name], SysCol.Name ForiegnKey_Column_Name, SysCol2.name Reference_Key_Column_Name
From sys.sysforeignkeys SysFrKey
Inner join sys.syscolumns SysCol on (SysFrKey.fkeyid = SysCol.id And SysFrKey.fkey = SysCol.colid )
Inner join syscolumns SysCol2 on ( SysFrKey.rkeyid = SysCol2.id And SysFrKey.rkey = SysCol2.colid )
Order by Master_Table_Name,Child_Table_Name

Advertisements

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out /  Change )

Google+ photo

You are commenting using your Google+ account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )

w

Connecting to %s

%d bloggers like this: