Results 1 to 3 of 3
  1. #1
    Join Date
    May 2004
    Posts
    2

    Question Unanswered: Retrieving constraint information

    Hello

    I wrote this query to retrieve all constraints (primary keys, foreign keys, unique keys, checks) on a table.

    Code:
                
    SELECT
        c.name AS name,
        CASE
            WHEN c.xtype = 'PK' THEN 'primary'
            WHEN c.xtype = 'F' THEN 'foreign'
            WHEN c.xtype = 'UQ' THEN 'unique'
            WHEN c.xtype = 'C' THEN 'check'
        END AS type,
        tkt.name AS contable,
        tkc.name AS confield,
        fkt.name AS reftable,
        fkc.name AS reffield,
        com.text AS expr
    FROM
        sysobjects c
        LEFT JOIN sysconstraints con ON con.constid = c.id
        LEFT JOIN sysforeignkeys fks ON fks.constid = con.constid
        LEFT JOIN sysobjects tkt ON tkt.id = con.id
        LEFT JOIN syscolumns tkc ON tkc.id = tkt.id AND tkc.colid = con.colid
        LEFT JOIN sysobjects fkt ON fkt.id = fks.rkeyid
        LEFT JOIN syscolumns fkc ON fkc.id = fkt.id AND fkc.colid = fks.rkey
        LEFT JOIN syscomments com ON com.id = c.id
    WHERE
        c.xtype IN ('PK', 'F', 'UQ', 'C')
        AND tkt.name = '$table'
        AND c.name = '$constraint'
    It returns a row for each constraint which can be easilly stored in an associative array.

    Code:
            
    Array (
        [name],        //name of the constraint
        [type],          //(primary|foreign|unique|check)
        [contable],  //table the constraint is on
        [confield],   //field the constraint is on
        [reftable],  //referenced table, null if type!=foreign
        [reffield],   //referenced field, null if type!=foreign
        [expr],        //check expression, null if type!=check
    )
    Everything works as expected except for one issue. For primary keys and unique keys, the sysconstraints.colid field is always '0'. The sysconstraints.id field properly indicates the id of the table that the primary/unique key is on, however I have no way of knowing which column(s) the primary/unique key is on. According to the Transact-SQL reference, the sysconstraints.colid field is the "ID of the column on which the constraint is defined, 0 if a table constraint.". Therefore, it looks like primary/unique constraints are stored as table constraints instead as a primary/unique constraint. However the sysconstraints.status field indicates the type of constraint to be a primary constraint or a unique constraint, not a table constraint.

    Code:
    Pseudo-bit-mask indicating the status. Possible values include:
    
    1 = PRIMARY KEY constraint.
    2 = UNIQUE KEY constraint.
    3 = FOREIGN KEY constraint.
    4 = CHECK constraint.
    5 = DEFAULT constraint.
    16 = Column-level constraint.
    32 = Table-level constraint.
    Is there something I am missing? Or maybe there is a better way to find the columns of a primary key or unique key that I can integrate into my above query?

    Thanks

    -except10n

  2. #2
    Join Date
    Jul 2002
    Location
    Village, MD
    Posts
    621
    Use INFORMATION_SCHEMA instead. I have done almost the same what you are trying to do and late I understood that this is not the best way to use internal tables. Every SP can change something and you will have a problem.

  3. #3
    Join Date
    May 2004
    Posts
    2
    Quote Originally Posted by snail
    Use INFORMATION_SCHEMA instead. I have done almost the same what you are trying to do and late I understood that this is not the best way to use internal tables. Every SP can change something and you will have a problem.
    Unfortunately I can't use INFORMATION_SCHEMA for reasons that would take too long to explain at the moment. In your past experiences, were you able to achieve what I am trying to do using the standard system tables?

Posting Permissions

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