SQL Server Performance Forum – Threads Archive
Relationships
How can I check If my database has any cross relationships/circular references between the tables? Thanks!"He laughs best who laughs last"
Look at the INFORMATION_SCHEMA.REFERENTIAL_CONSTRAINTS view in thsi case. Satya SKJ
Microsoft SQL Server MVP
Writer, Contributing Editor & Moderator
http://www.SQL-Server-Performance.Com
@http://www.askasqlguru.com/ This posting is provided AS IS with no rights for the sake of knowledge sharing. Knowledge is of two kinds. We know a subject ourselves or we know where we can find information on it.
I guess you are referring to the view in the master table, which is empty in my case. Thanks!
"He laughs best who laughs last"
What result you get on that user database?
BOL
quote:Contains one row for each foreign constraint in the current database. This information schema view returns information about the objects to which the current user has permissions. The INFORMATION_SCHEMA.REFERENTIAL_CONSTRAINTS view is based on the sysreferences, sysindexes, and sysobjects system tables.
To retrieve information from these views, specify the fully qualified name of INFORMATION_SCHEMA view_name.
Satya SKJ
Microsoft SQL Server MVP
Writer, Contributing Editor & Moderator
http://www.SQL-Server-Performance.Com
@http://www.askasqlguru.com/ This posting is provided AS IS with no rights for the sake of knowledge sharing. Knowledge is of two kinds. We know a subject ourselves or we know where we can find information on it.
I never done this before. how can i use that view to display relationships for my database Thanks!
"He laughs best who laughs last"
Acually I was asked to find circular refrences in a database and solutions how to get rid of them. Help me where to start the job. Thanks!
"He laughs best who laughs last"
Run this on your database, not on master. The views are special type and available on all databases select * from INFORMATION_SCHEMA.REFERENTIAL_CONSTRAINTS
This shows all constraints select * from INFORMATION_SCHEMA.CONSTRAINT_COLUMN_USAGE
This shows all columns involved in the constraints Youd need to write your own code to identify circular references.
own code? can you let me know how we can identify circular references..I mean is there any example where i look at it to code for my database. when I run select * from INFORMATION_SCHEMA.REFERENTIAL_CONSTRAINTS, I get 220 such rows, here is the result SIRSUdbofk_tbldept_empIDSIRSUdbopk_tblUNICORN_RoleIDNONENO ACTIONNO ACTION
SIRSUdbofk_tbloffice_SkillIDSIRSUdbopk_tblSirsmain_jobIDNONENO ACTIONNO ACTION
SIRSUdbofk_tblbudget_offIDSIRSUdbopk_tblUNICORN_RoleID NONENO ACTIONNO ACTION
Circular references are FK between (at least) three tables. At least when the FKs have cascading updates and/or deletes, SQL Server does not allow you to close the circle:
(1) T1 references T2 on column X
(2) T2 references T3 on column Y
(3*) T1 references T3 on column Z
It doesn’t matter if the circle starts and ends on different columns in T1: the point is that it starts and ends on the same table, and so the third FK from the list will not be created. The only workaround here is to implement that last relationship in a trigger – which means you’re not seeing all the "effective" relationships by looking at FKs.
Not sure if you can have non-cascading circular FK constraints. If they are allowed, then you need to play around with sp_fkeys to find them.
]]>