add schema to sysforeignkeys info

To the below code, how would you add the schema (owner) info also, in column 2 and 3?

thanks
SELECT 
OBJECT_NAME(f.constid) AS 'ForeignKey',
OBJECT_NAME(f.rkeyid) AS 'PKTable', 
--c2.[name] AS 'PKColumnName',
OBJECT_NAME(f.fkeyid) AS 'FKTable'
--c1.[name] AS 'FKColumnName'
FROM sysforeignkeys f
INNER JOIN syscolumns c1
ON f.fkeyid = c1.[id]
AND f.fkey = c1.colid
INNER JOIN syscolumns c2
ON f.rkeyid = c2.[id]
AND f.rkey = c2.colid

Open in new window

LVL 6
anushahannaAsked:
Who is Participating?
 
Raja Jegan RSQL Server DBA & Architect, EE Solution GuideCommented:
Hope this provides you with the required info:

select * from INFORMATION_SCHEMA.REFERENTIAL_CONSTRAINTS
0
 
k_murli_krishnaCommented:
rrejan17 is correct. You can try a more generic one:

select * from INFORMATION_SCHEMA.TABLE_CONSTRAINTS;
0
 
anushahannaAuthor Commented:
k_murli_krishna, do you see benefit adding INFORMATION_SCHEMA.TABLE_CONSTRAINTS instead of INFORMATION_SCHEMA.REFERENTIAL_CONSTRAINTS in the code below?
SELECT 
OBJECT_NAME(f.constid) AS 'ForeignKey',
UNIQUE_CONSTRAINT_SCHEMA+'.'+OBJECT_NAME(f.rkeyid) AS 'PKTable', 
CONSTRAINT_SCHEMA+'.'+OBJECT_NAME(f.fkeyid) AS 'FKTable',
UNIQUE_CONSTRAINT_NAME as PrimaryKey
FROM sysforeignkeys f
INNER JOIN syscolumns c1
ON f.fkeyid = c1.[id]
AND f.fkey = c1.colid
INNER JOIN syscolumns c2
ON f.rkeyid = c2.[id]
AND f.rkey = c2.colid
LEFT OUTER JOIN INFORMATION_SCHEMA.REFERENTIAL_CONSTRAINTS RC
ON OBJECT_NAME(f.constid)=CONSTRAINT_NAME

Open in new window

0
 
Raja Jegan RSQL Server DBA & Architect, EE Solution GuideCommented:
>> do you see benefit adding INFORMATION_SCHEMA.TABLE_CONSTRAINTS  instead of INFORMATION_SCHEMA.REFERENTIAL_CONSTRAINTS in the code below?

TABLE_CONSTRAINTS will contain information about the Constraint Name along with Table name whereas REFERENTIAL_CONSTRAINTS view would provide you information about the referenced table name as you are finding it..
0
 
anushahannaAuthor Commented:
Good point, thanks.
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.