Generate Delete Statement From Foreign Key Relationships in SQL 2008?

Question

Is it possible via script/tool to generate a delete statement based on the tables fk relations.

i.e. I have the table: DelMe(ID) and there are 30 tables with fk references to its ID that I need to delete first, is there some tool/script that I can run that will generate the 30 delete statements based on the FK relations for me ?

(btw I know about cascade delete on the relations, I can't use it in this existing db)

I'm using Microsoft SQL Server 2008

Answer 1

DELETE statements generated for use in SP with parameter, and as ON DELETE triggers: (this variant supports single column FKs only)

SELECT 'DELETE '+detail.name+' WHERE '+dcolumn.name+' = @'+mcolumn.name AS stmt, 
    'DELETE ' + detail.name + ' FROM ' + detail.name + ' INNER JOIN deleted ON ' + 
    detail.name + '.' + dcolumn.name + ' = deleted.' + mcolumn.name AS trg
FROM sys.columns AS mcolumn 
INNER JOIN sys.foreign_key_columns ON mcolumn.object_id = 
            sys.foreign_key_columns.referenced_object_id 
    AND  mcolumn.column_id = sys.foreign_key_columns.referenced_column_id 
INNER JOIN sys.tables AS master ON mcolumn.object_id = master.object_id 
INNER JOIN sys.columns AS dcolumn 
    ON sys.foreign_key_columns.parent_object_id = dcolumn.object_id 
    AND sys.foreign_key_columns.parent_column_id = dcolumn.column_id 
INNER JOIN sys.tables AS detail ON dcolumn.object_id = detail.object_id
WHERE (master.name = N'MyTableName')