Post reply

Foreign Key Constraint

  • March 16, 2007 at 6:54 am

    #95043

    Hi All,

          Can anybody let me know the SQL statement to retrieve the Column name on a particular table on which a foreign key constraint has been created. I had tried to retrieve the data from SysColumns and SysForeignKeys but was not able to get the output. can anybody help me in this regard.

     

     

    Thanks in advance.

  • March 16, 2007 at 7:10 am

    #695442

    Assuming you're using SQL Server 2000, the sysreferences table should contain the information you're looking for.

    John

  • March 16, 2007 at 7:33 am

    #695451

    try using sp_fkey 'tablename'. this should give u info on foregin key imposed in a table.

    Cheers,
    Sugeshkumar Rajendran
    SQL Server MVP
    http://sugeshkr.blogspot.com

  • March 16, 2007 at 7:41 am

    #695458

    Yes I am using SQL 2000 and i had tried in Sysreferences already and i could not get the column name. i was able to fetch the table name only.

     

    Regarding the sp_fkey 'tablename', it didnt work for me. one more thing is i would be using this within a procedure so sp_fkey will not help me. if there is any select query to return the result, it would be fine.

  • March 16, 2007 at 8:03 am

    #695481

    This is how you do it.  If your foreign key constraints are on composite keys, then the logic gets a little bit more complicated, but it isn't difficult to work out.

    SELECT OBJECT_NAME(r.constid) AS ConstraintName,

     OBJECT_NAME(r.fkeyid) AS ConstraintTable,

     c1.NAME AS ReferencingColumn,

     OBJECT_NAME(r.rkeyid) AS ReferencedTable,

     c2.NAME AS ReferencedColumn

    FROM sysreferences r JOIN syscolumns c1

    ON r.fkeyid = c1.id AND r.fkey1 = c1.colid

    JOIN syscolumns c2

    ON r.rkeyid = c2.id AND r.rkey1 = c2.colid

    John

  • March 16, 2007 at 8:20 am

    #695485

    Thanks a lot John!!

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

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