Foreign keys

Question

Post reply

Foreign keys

Mike Levan

SSChampion

Points: 14201
More actions
July 27, 2007 at 7:12 am

#125129

How can i check if my database has any circular references/relationships and how to get rid of them.

Viewing 6 posts - 1 through 5 (of 5 total)

You must be logged in to reply to this topic. Login to reply

AMIT GUPTA-263376 SSCarpal Tunnel Points: 4244 More actions · Answer 1

Hi ,

Use this sp..

sp_depends <Table Name>

ex. sp_depends 'authors'

Regards ,

Amit Gupta

MVP

Mike Levan SSChampion Points: 14201 More actions · Answer 2

do i need to do that for each table..is there a way i can do for total database.

Actually I have to check comeplte database for circular references and come up with solutions to get rid of them.

but i dont think this will give me the relationships, this may result in store procs using that table.

Greg Charles SSC-Forever Points: 45403 More actions · Answer 3

Try this query to get a list of all the foreign keys and referenced tables in a database:

select

t.name as 'Table', c.name as 'Foreign_Key_Constraint', rt.name as Referenced_Table'

from (select distinct constid, fkeyid, rkeyid from sysforeignkeys) as FK

join sysobjects c on c.id = FK.constid

join sysobjects t on t.id = FK.fkeyid

join sysobjects rt on rt.id = FK.rkeyid

order by 1,2,3

Greg

Mike Levan SSChampion Points: 14201 More actions · Answer 4

Greg

by running your query i am able to get all the tables,references but how can I find circular references in it and get rid of them

Greg Charles SSC-Forever Points: 45403 More actions · Answer 5

If, by circular reference you mean a table referring to itself via a foreign key constraint, the query I posted should show that. You can get rid of them by deleting the constraint.

Greg