Monday, March 19, 2012

Getting relationship data

Hi
I am trying to create a query or set of querys, which will allow me to retrieve the relationship data of a database. In sql Server 2000 the user can create diagrams which show these relationships, what i want to be able to do is get this data, but i am h
aving trouble finding a starting place. Could anyone point me in the right direction?
Thanks in advance
You can start by querying INFORMATION_SCHEMA.REFERENTIAL_CONSTRAINTS view.
If you are interested in viewing the reference constraints that exist in
your database, you can do:
SELECT o1.name AS "tablename",
OBJECT_NAME( f1.constid ) AS "constraintname",
OBJECT_NAME( f1.rkeyid ) as referencedtable
FROM sysobjects o1
LEFT OUTER JOIN sysconstraints c1
ON o1.id = c1.id
AND c1.status &3 = 3
LEFT OUTER JOIN sysforeignkeys f1
ON c1.constid = f1.constid
WHERE o1.type = 'u'
ORDER BY "referencedtable", "tablename" ;
Anith

No comments:

Post a Comment