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

Unfortunately, I think cascading is the tool you're asking for. I understand not being able to use it, but that fact that it exists as a built-in part of the db has pretty much killed the need for an alternative.

Answer 2

I'm pretty sure I posted code here on Stack Overflow which does this automatically using INFORMATION_SCHEMA to generate dynamic SQL, but I can't find it. Let me see if I can regenerate it.

You might need to check this out a bit, I couldn't find my original code, so I modified some code I had which builds flattend views for star-schemas automatically.

DECLARE @COLUMN_NAME AS sysname
DECLARE @TABLE_NAME AS sysname
DECLARE @IDValue AS int

SET @COLUMN_NAME = '<Your COLUMN_NAME here>'
SET @TABLE_NAME = '<Your TABLE_NAME here>'
SET @IDValue = 123456789

DECLARE @sql AS varchar(max) ;
WITH    RELATED_COLUMNS
          AS (
              SELECT    QUOTENAME(c.TABLE_SCHEMA) + '.'
                        + QUOTENAME(c.TABLE_NAME) AS [OBJECT_NAME]
                       ,c.COLUMN_NAME
              FROM      PBANKDW.INFORMATION_SCHEMA.COLUMNS AS c WITH (NOLOCK)
              INNER JOIN PBANKDW.INFORMATION_SCHEMA.TABLES AS t WITH (NOLOCK)
                        ON c.TABLE_CATALOG = t.TABLE_CATALOG
                           AND c.TABLE_SCHEMA = t.TABLE_SCHEMA
                           AND c.TABLE_NAME = t.TABLE_NAME
                           AND t.TABLE_TYPE = 'BASE TABLE'
              INNER JOIN (
                          SELECT    rc.CONSTRAINT_CATALOG
                                   ,rc.CONSTRAINT_SCHEMA
                                   ,lkc.TABLE_NAME
                                   ,lkc.COLUMN_NAME
                          FROM      PBANKDW.INFORMATION_SCHEMA.REFERENTIAL_CONSTRAINTS rc
                                    WITH (NOLOCK)
                          INNER JOIN PBANKDW.INFORMATION_SCHEMA.KEY_COLUMN_USAGE lkc
                                    WITH (NOLOCK)
                                    ON lkc.CONSTRAINT_CATALOG = rc.CONSTRAINT_CATALOG
                                       AND lkc.CONSTRAINT_SCHEMA = rc.CONSTRAINT_SCHEMA
                                       AND lkc.CONSTRAINT_NAME = rc.CONSTRAINT_NAME
                          INNER JOIN PBANKDW.INFORMATION_SCHEMA.TABLE_CONSTRAINTS tc
                                    WITH (NOLOCK)
                                    ON rc.CONSTRAINT_CATALOG = tc.CONSTRAINT_CATALOG
                                       AND rc.CONSTRAINT_SCHEMA = tc.CONSTRAINT_SCHEMA
                                       AND rc.UNIQUE_CONSTRAINT_NAME = tc.CONSTRAINT_NAME
                          INNER JOIN PBANKDW.INFORMATION_SCHEMA.KEY_COLUMN_USAGE rkc
                                    WITH (NOLOCK)
                                    ON rkc.CONSTRAINT_CATALOG = tc.CONSTRAINT_CATALOG
                                       AND rkc.CONSTRAINT_SCHEMA = tc.CONSTRAINT_SCHEMA
                                       AND rkc.CONSTRAINT_NAME = tc.CONSTRAINT_NAME
                          WHERE     rkc.COLUMN_NAME = @COLUMN_NAME
                                    AND rkc.TABLE_NAME = @TABLE_NAME
                         ) AS j
                        ON j.CONSTRAINT_CATALOG = c.TABLE_CATALOG
                           AND j.CONSTRAINT_SCHEMA = c.TABLE_SCHEMA
                           AND j.TABLE_NAME = c.TABLE_NAME
                           AND j.COLUMN_NAME = c.COLUMN_NAME
             )
    SELECT  @sql = COALESCE(@sql, '') + 'DELETE FROM ' + [OBJECT_NAME]
            + ' WHERE ' + [COLUMN_NAME] + ' = ' + CONVERT(varchar, @IDValue)
            + CHAR(13) + CHAR(10)
    FROM    RELATED_COLUMNS

PRINT @sql

Answer 3

Another technique is to use a code generator to create the Sql. I'm pretty sure the MyGeneration (no connection) has existing templates to do this. Using that tool and the right template you can create a sql script that deletes the relevant stuff with no pain.

Answer 4

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')

Answer 5

devio,

Thank you for the code generation query. Great!

I am looking for a quick way to execute the DELETE statements, for example in a bulk. I tried with a cursor, but it requires lots of code according to me.

Do you have any hint ?

Answer 6

Anybody has the ORACLE PL/SQL VERSION of the same statement? It's urgent.....