Results 1 to 5 of 5
  1. #1
    Join Date
    May 2007
    Posts
    4

    Unanswered: Finding Duplicate Foreign Keys

    Hi

    i tried the following query and able to get the list of foreign keys with column names as well as referred tables and referenced column

    select parent_column_id as 'child Column',object_name(constraint_object_id)as 'FK Name',object_name(parent_object_id) as 'parent table',name,object_name(referenced_object_id)as 'referenced table',referenced_column_id
    from sys.foreign_key_columns inner join sys.columns on (parent_column_id = column_id and parent_object_id=object_id)
    Order by object_name(parent_object_id) asc

    but i am not able to get the fks created more than once on same column refering to same pk

    Thanks in Advance

  2. #2
    Join Date
    Jan 2007
    Location
    UK
    Posts
    11,434
    Provided Answers: 10
    Is this helpful?
    Quote Originally Posted by Example data
    Field1 has the following values;
    1, 2, 3, 3, 4, 5, 6, 6, 6, 7, 8, 9
    duplicates highlighted in bold
    The following SQL will pick out duplicates only
    Code:
    SELECT Field1
    FROM MyTable
    GROUP BY Field1
    HAVING Count(*) > 1
    Quote Originally Posted by Results
    Field1
    -----
    3
    6
    George
    Home | Blog

  3. #3
    Join Date
    May 2007
    Posts
    49
    I am not sure what is the exact problem, but I think 'Information_Schema' might help. It contains number of views that can be used to retrive meta data.

    E.g.
    Select * from information_Schema.TABLE_CONSTRAINTS
    Mihir Mehendale
    DB Team @ Clarion
    Clarion Technologies
    (SEI CMMI Level 3)
    Pune, India
    www.clariontechnologies.co.in
    www.vEmployee.com

  4. #4
    Join Date
    May 2004
    Location
    Seattle
    Posts
    1,313
    here's one that I use in SqlSpec, it may be more than you need though:

    Code:
    select   
        quotename(su2.name) collate Latin1_General_CI_AS + N'.' collate Latin1_General_CI_AS + quotename(so2.name) collate Latin1_General_CI_AS as parent  
        ,quotename(su1.name) collate Latin1_General_CI_AS + N'.' collate Latin1_General_CI_AS + quotename(so1.name) collate Latin1_General_CI_AS as name  
        ,so1.name as shortname  ,sc2.name as colName  
        ,'foreign key from ' + quotename(su2.name) + N'.' + quotename(so2.name) + ' to ' + quotename(su3.name) + N'.' + quotename(so3.name) as description      
        ,quotename(su3.name) collate Latin1_General_CI_AS + N'.' collate Latin1_General_CI_AS + quotename(so3.name) collate Latin1_General_CI_AS as refTable  
        ,sc3.name as refColumn  
        ,ObjectProperty(sf.constid, 'CnstIsDisabled') as isDisabled     
        ,ObjectProperty(sf.constid, 'CnstIsNotRepl') as isNotForReplication     
        ,ObjectProperty(sf.constid, 'CnstIsDeleteCascade') as isDeleteCascade     
        ,ObjectProperty(sf.constid, 'CnstIsUpdateCascade') as isUpdateCascade     
        ,so1.object_id as id from sysforeignkeys sf 
    join sys.objects so1 on sf.constid=so1.object_id 
    join sys.objects so2 on sf.fkeyid=so2.object_id 
    join sys.objects so3 on sf.rkeyid=so3.object_id 
    join sys.schemas su1 on su1.schema_id=so1.schema_id 
    join sys.schemas su2 on su2.schema_id=so2.schema_id 
    join sys.schemas su3 on su3.schema_id=so3.schema_id 
    join syscolumns sc2 on sc2.colid=sf.fkey and sc2.id=so2.object_id 
    join syscolumns sc3 on sc3.colid=sf.rkey and sc3.id=so3.object_id

  5. #5
    Join Date
    May 2007
    Posts
    4
    Thanks You ALL for your reply.

    i got that using
    INFORMATION_SCHEMA.TABLE_CONSTRAINTS tc and INFORMATION_SCHEMA.CONSTRAINT_COLUMN_USAGE

    with CTE

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •