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:
SELECT name as fkname, OBJECT_NAME(parent_obj) as fkTableName
INNER JOIN sysreferences
WHERE name like 'FK%' AND xtype='F' -- foreign key only
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..