Foreign Constraint details - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2000 Forums

Transact-SQL (2000)

Foreign Constraint details

Author

Topic

scelamko
Constraint Violating Yak Guru

309 Posts

Posted - 2006-12-11 : 17:18:58

Guys,

I am trying to join sysforeignkeys, sysobjects and information_schema.columns to get the details of the foreignkeys. But somehow I dont seem to get the desired output.

Below query is how far I got

SELECT T.NAME FNAME, O.NAME PNAME, F.FKEY, S.NAME CNAME, F.RKEY
FROM SYSFOREIGNKEYS F INNER JOIN SYSOBJECTS O ON F.FKEYID = O.ID
INNER JOIN SYSOBJECTS S ON F.RKEYID = S.ID
INNER JOIN SYSOBJECTS T ON F.CONSTID = T.ID

fname - foreignkey name
pname - parent table name
pcol - parent column name
pdtype - parent column datatype
cname - child table name
ccol - child column name
cdtype - child column datatype

My desired output is something like below.

cname pname pcol pdtype cname col cdtype

Any suggestions/inputs would help

Thanks

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2006-12-11 : 17:22:18

Maybe you should read this topic http://www.sqlteam.com/forums/topic.asp?TOPIC_ID=72957

Peter Larsson
Helsingborg, Sweden

scelamko
Constraint Violating Yak Guru

309 Posts

Posted - 2006-12-11 : 17:31:15

Peso,

But I am looking to drill down to the column levele details
and the link you sent me doesnt seem to do this.

any suggestions/inputs??

Thanks

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2006-12-11 : 17:33:41

Maybe you can make that topic a start, and just add the datatype as you need?

Peter Larsson
Helsingborg, Sweden

scelamko
Constraint Violating Yak Guru

309 Posts

Posted - 2006-12-12 : 17:31:04

This query should suffice my requirements

SELECT
OBJECT_NAME(sfk.constid) FNAME,
OBJECT_NAME(sfk.fkeyid) PNAME,
sc1.name PCOL,
st1.name PDTYPE,
OBJECT_NAME(sfk.rkeyid) CNAME,
sc2.name CCOL,
st2.name CDTYPE
FROM sysforeignkeys sfk
JOIN syscolumns sc1 ON sfk.fkeyid = sc1.id AND sfk.fkey = sc1.colid
JOIN systypes st1 ON sc1.xtype = st1.xtype
JOIN syscolumns sc2 ON sfk.rkeyid = sc2.id AND sfk.rkey = sc2.colid
JOIN systypes st2 ON sc2.xtype = st2.xtype
WHERE st1.name <> 'sysname'
AND st2.name <> 'sysname'
ORDER BY FNAME, PNAME, CNAME, keyno

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources