I need to write a stored procedure that when passed the id of a row on a table, will query all tables that are referencing that row and move the data to another row. I am doing this because we have 2 seperate systems that are linked by a table and
sometimes things get out of whack and you have to manually move information over.
I have this query that can find all the dependent tables, my question is what would I add to this to obtain the name of the column that is the foreign key on that tables.
SELECT o.name[parentTable], o2.name[childTable] FROM sys.sysobjects o
INNER JOIN sys.sysforeignkeys fk ON fk.rkeyid = o.id
INNER JOIN sys.sysobjects o2 ON o2.id = fk.fkeyid AND o2.id <> o.id
WHERE o.name = 'tablename'
I want to do this as a query because I want the procedure to be able to detect any new tables that might have been added.
View Complete Post