SQL 2000: T-SQL to get foreign key relationships for a table

foreign-key-relationship, sql-server-2000

Solution

DECLARE @tableName sysname

SET @tableName = '' -- Your table name goes here

SELECT
    c.name
    , target.name
    , targetc.name
FROM
    -- source table
    sysobjects t
    -- source column
    INNER JOIN syscolumns c ON t.id = c.id
    -- general constraint
    INNER JOIN sysconstraints co ON t.id = co.id AND co.colid = c.colid
    -- foreign key constraint
    INNER JOIN sysforeignkeys fk ON co.constid = fk.constid
    -- target table
    INNER JOIN sysobjects target ON fk.rkeyid = target.id
    -- target column
    INNER JOIN syscolumns targetc ON fk.rkey = targetc.colid AND fk.rkeyid = targetc.id
WHERE
    t.name = @tableName

NOTE I have I think used only those system views available in SQL 2000 (ie the sysXXX ones rather than the SQL 2005 sys.XXX ones) but I have only actually tested this in a SQL 2005 environemnt.

Problem

Similar but NOT IDENTICAL to SQL Server 2000 - Query a Table’s Foreign Key relationships I need a T-SQL statement that will work SQL 2000 that given a table name, will return the foreign key relationships for that table e.g. Table MyFristTable has a foreign key to MySecondTable, where MyFirstTable.ColA must be in MySecondTable.ColB. I'd be delighted, if the sql statement (or stored proc) is ran for MyFirstTable and returned a result set on the lines of ``` Column | FK_Table | FK_COLUMN ---------------------------------- ColA | MySecondTable | ColB ``` NB: I have samples for SQL 2005 that won't work because they rely on sys.foreign_key_columns I'd rather not have to parse out the results of the sp_help statement. Thanks,

Original source

Related problems