SQL Server Performance

Relationships

Discussion in 'T-SQL Performance Tuning for Developers' started by Reddy, Jul 27, 2007.

  1. Reddy New Member

    How can I check If my database has any cross relationships/circular references between the tables?

    Thanks!
    "He laughs best who laughs last"

  2. satya Moderator

    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.
  3. Reddy New Member

    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"

  4. satya Moderator

    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.
  5. Reddy New Member

    I never done this before. how can i use that view to display relationships for my database

    Thanks!
    "He laughs best who laughs last"

  6. Reddy New Member

    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"

  7. Chappy New Member

    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.
  8. Reddy New Member

    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
  9. Adriaan New Member

    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.

Share This Page