I need a query for SQL Server 2000 to get a list of all foreign keys.
Particularly all the foreign keys that point to a particular column.
How do I write this query?
Add a comment |
3 Answers
select * from sysobjects where xtype = 'F' That should do the trick and be compatible with SQL Server 2000, I hope!
If you additionally need the table and column information in SQL Server 2000, it gets a bit more involved; you need to join the sysforeignkeys and syscolumns catalog views like so:
select so.name 'foreign key name', OBJECT_NAME(parent_obj) 'table', OBJECT_NAME(sf.fkeyid) 'referencing table', sc1.name 'referencing column', OBJECT_NAME(sf.rkeyid) 'referenced table', sc2.name 'referenced column' from sysobjects so inner join sysforeignkeys sf on so.id = sf.constid inner join syscolumns sc1 on sf.fkeyid = sc1.id and sf.fkey = sc1.colid inner join syscolumns sc2 on sf.rkeyid = sc2.id and sf.fkey = sc2.colid where so.xtype in ('F','PK') And if you want to leverage the INFORMATION_SCHEMA views which ARE indeed available in SQL Server 2000, use this query:
SELECT rc.CONSTRAINT_NAME, rcu.TABLE_NAME 'Referencing Table', rcu.COLUMN_NAME 'Referencing Column', rcu1.TABLE_NAME 'Referenced Table', rcu1.COLUMN_NAME 'Referenced Column' FROM INFORMATION_SCHEMA.REFERENTIAL_CONSTRAINTS rc INNER JOIN INFORMATION_SCHEMA.CONSTRAINT_COLUMN_USAGE rcu ON rc.CONSTRAINT_CATALOG = rcu.CONSTRAINT_CATALOG AND rc.CONSTRAINT_NAME = rcu.CONSTRAINT_NAME INNER JOIN INFORMATION_SCHEMA.CONSTRAINT_COLUMN_USAGE rcu1 ON rc.UNIQUE_CONSTRAINT_CATALOG = rcu1.CONSTRAINT_CATALOG AND rc.UNIQUE_CONSTRAINT_NAME = rcu1.CONSTRAINT_NAME Marc
5 Comments
Rashack
The second query seems a bit odd. It seems to be missing join condition of some kind - I got some Cartesian product.
Mark
You actually need to join on
sf.fkeyid = sc1.id and sf.fkey = sc1.colid (and the same for rkey)Mark
and you probably also want to use
so.xtype in ('F', 'PK')
Gary Kindel
This was the exact solution I was looking for. Thanks
Pedro Custódio
The referenced column should be joins with
and sf.rkey = sc2.colid instead of and sf.fkey = sc2.colidselect * from INFORMATION_SCHEMA.REFERENTIAL_CONSTRAINTS If you need more information about the key then you can join it to the INFORMATION_SCHEMA.KEY_COLUMN_USAGE view, which contains the columns referenced by the key.
3 Comments
marc_s
I don't think INFORMATION_SCHEMA was available in SQL Server 2000..... (I think that was new in 2005, no?)
David McEwing
No. It is available. I checked it against a SQL 2000 instance before I posted it.
KM.
how do you filter on columns?
Look at the source of sp_helpconstraint for more ideas, but this should work...
to get every FK that refers to target table & column
- replace "YourTableName"
- uncomment the last "AND" and set your target column name
code:
--list all tables & columns that refer to the given table select k.name,pt.Name AS ParentTable,pc.name,c.constraint_column_id,ct.Name AS ReferedToTable,c.referenced_column_id,cc.Name from sys.foreign_keys k INNER JOIN sys.foreign_key_columns c ON k.parent_object_id=c.parent_object_id INNER JOIN sys.objects pt ON c.parent_object_id=pt.object_ID INNER JOIN sys.objects ct ON c.referenced_object_id=ct.object_ID INNER JOIN sys.columns pc ON c.parent_object_id=pc.object_ID AND c.parent_column_id=pc.column_id INNER JOIN sys.columns cc ON c.referenced_object_id=cc.object_ID AND c.referenced_column_id=cc.column_id where k.referenced_object_id = object_id('YourTableName') --AND pc.name='YourColumnName' --parent table column name --AND cc.name='YourColumnName' --referenced table's column name 1 Comment
marc_s
Again - the "sys" schema is not availabe in SQL Server 2000.... this works just fine in 2005 and 2008.