SQL 2000: T-SQL for obtaining foreign keys for a table

Question

A similar but NOT ID for SQL Server 2000 - requesting foreign relations with a foreign key

I need a T-SQL statement that will run SQL 2000, which would give a table name, will return foreign key relationships for this table, for example.

The MyFristTable table has a foreign key for MySecondTable, where MyFirstTable.ColA must be in MySecondTable.ColB. I would be happy if the sql statement (or stored proc) runs for MyFirstTable and returns a result set in rows

Column | FK_Table | FK_COLUMN ---------------------------------- ColA | MySecondTable | ColB 

NB : I have samples for SQL 2005 that will not work because they rely on sys.foreign_key_columns

I would prefer not to parse the results of the sp_help statement.

Thanks,

Answer 1

 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 think that I used only those system views that are available in SQL 2000 (i.e. sysXXX, not SQL 2005 sys.XXX), but I just tested this in SQL 2005 environemnt.

Answer 2

I needed to do this exactly for the query, and I found this stored procedure by trying a version similar to the sys table:

 exec sp_fkeys @fktable_name = 'foo' 

This seems to be available in SQL Server 2000 . In addition, I found that in several cases there were slight differences between this stored procedure and the queries here. I assume sp_fkeys is a canonical version.

Answer 3

I found this on google ... so if this work is not my merit. Hope this helps.

  SELECT FK_Table = FK.TABLE_NAME, FK_Column = CU.COLUMN_NAME, PK_Table = PK.TABLE_NAME, PK_Column = PT.COLUMN_NAME, Constraint_Name = C.CONSTRAINT_NAME FROM INFORMATION_SCHEMA.REFERENTIAL_CONSTRAINTS C INNER JOIN INFORMATION_SCHEMA.TABLE_CONSTRAINTS FK ON C.CONSTRAINT_NAME = FK.CONSTRAINT_NAME INNER JOIN INFORMATION_SCHEMA.TABLE_CONSTRAINTS PK ON C.UNIQUE_CONSTRAINT_NAME = PK.CONSTRAINT_NAME INNER JOIN INFORMATION_SCHEMA.KEY_COLUMN_USAGE CU ON C.CONSTRAINT_NAME = CU.CONSTRAINT_NAME INNER JOIN ( SELECT i1.TABLE_NAME, i2.COLUMN_NAME FROM INFORMATION_SCHEMA.TABLE_CONSTRAINTS i1 INNER JOIN INFORMATION_SCHEMA.KEY_COLUMN_USAGE i2 ON i1.CONSTRAINT_NAME = i2.CONSTRAINT_NAME WHERE i1.CONSTRAINT_TYPE = 'PRIMARY KEY' ) PT ON PT.TABLE_NAME = PK.TABLE_NAME WHERE PK.TABLE_NAME='something' -- the table for you are asking 

Answer 4

I needed something like this, so I just looked at the source code of the stored procedure of the system and copied what I needed into my own procedure and made it work as needed.

You can see the source code for sp_helpconstraint ...