Tuesday, July 24, 2007

Foreign keys for the referenced table

Let's say, I have a database table "SingleTable" and I want to get list of all tables which are referencing to this SingleTable and names of foreign keys...

(I need this, for example, if I am going to drop SingleTable but I need drop all dependencies from this table first )

I have option to use undocumented stored procedure sp_MStablerefs, however I am trying to avoid using undocumented feature. Never know when and where Microsoft will put stick in my wheels :)
So, there is alternative way I found by experiments:

USE mydatabase
SELECT name as fkname, OBJECT_NAME(parent_obj) as fkTableName
FROM sysobjects
INNER JOIN sysreferences
ON sysobjects.id=sysreferences.constid
WHERE name like 'FK%' AND xtype='F' -- foreign key only
AND rkeyid=object_id(N'SingleTable')

Result is set of forein key names and table names referencing to my table..
Bingo. Now we can drop them one by one using cursor, or DataSet on the upper code level..

No comments: