SQL Server - Delete unused rows

I need to get stored procedure (not standart, only custom) to delete unused rows table (table1) and some other tables (table2, table3, table4...- we don't know how many tables refers to table1 by FOREIGN KEY CONSTRAINT) can refers to this table.

For example:

СREATE PROCEDURE [dbo].[delete_on_constraints]
@table varchar(36) – table name we need to delete unused rows
@column varchar(36) – column name - primary key, on which other tables refers by FOREIGN KEY CONSTRAINT

There are the table1 and table2, table3 which refers to table1 (remind, we don't know how many tables refers to table1 by FOREIGN KEY CONSTRAINT)

CREATE TABLE [table1] (
    [id] [int] IDENTITY (1, 1) NOT NULL CONSTRAINT [PK_ table1] PRIMARY KEY,
    [value] [int] NULL 
)
GO

CREATE TABLE [table2] (
    [id] [int] IDENTITY (1, 1) NOT NULL CONSTRAINT [PK_ table2] PRIMARY KEY,
    [table1_id] [int] NOT NULL ,
       CONSTRAINT [FK_ table2_ table1] FOREIGN KEY ([table1_id]) REFERENCES [table1] ( [id] ) 
) 
GO 

CREATE TABLE [table3] (
    [id] [int] IDENTITY (1, 1) NOT NULL CONSTRAINT [PK_ table3] PRIMARY KEY,
    [table1_id] [int] NOT NULL ,
       CONSTRAINT [FK_ table3_ table1] FOREIGN KEY ([table1_id]) REFERENCES [table1] ( [id] ) 
)

GO 
INSERT INTO [table1] VALUES (100)
INSERT INTO [table1] VALUES (200)
INSERT INTO [table1] VALUES (300)
INSERT INTO [table1] VALUES (400)

GO
INSERT INTO [table2] VALUES (1)
INSERT INTO [table2] VALUES (3)

GO
INSERT INTO [table3] VALUES (1)
INSERT INTO [table3] VALUES (2)
INSERT INTO [table3] VALUES (3)

There are rows in tables before call stored procedure

table1
id value
1 100
2 200
3 300
4 400

table2
id table1_id
1 1
2 3

table3
id table1_id
1 1
2 2
3 3

And after [dbo].[delete_on_constraints] ‘table1’ ‘id’ procedure call

开发者_JAVA百科table1

id       value
1        100
2        200
3        300

First, let me state for the record that this code should never be used in production. By that I mean, it should only ever be used as a one-time cleanup. If you have regular need of deleting rows without knowing the DRI, then there is a fundamental design problem.

In short, you need to use dynamic SQL to query for the relations and build the Delete statements.

Declare @Columns Cursor
Declare @PKTableSchema nvarchar(128)
Declare @PKTableName nvarchar(128)
Declare @PKColumnName nvarchar(128)
Declare @TableSchema nvarchar(128)
Declare @TableName nvarchar(128)
Declare @ColumnName nvarchar(128)
Declare @Sql nvarchar(max)

Set @Columns = Cursor Fast_Forward For
    Select Quotename(PK_CCU.TABLE_SCHEMA)
        , Quotename(PK_CCU.TABLE_NAME)
        , Quotename(PK_CCU.COLUMN_NAME)
        , Quotename(RC_CCU.TABLE_SCHEMA)
        , Quotename(RC_CCU.TABLE_NAME)
        , Quotename(RC_CCU.COLUMN_NAME)
    From INFORMATION_SCHEMA.REFERENTIAL_CONSTRAINTS As RC
        Join INFORMATION_SCHEMA.CONSTRAINT_COLUMN_USAGE As RC_CCU
            On RC_CCU.CONSTRAINT_SCHEMA = RC.CONSTRAINT_SCHEMA
                And RC_CCU.CONSTRAINT_NAME = RC.CONSTRAINT_NAME
        Join INFORMATION_SCHEMA.TABLE_CONSTRAINTS As TC
            On TC.CONSTRAINT_SCHEMA = RC.UNIQUE_CONSTRAINT_SCHEMA
                And TC.CONSTRAINT_NAME = RC.UNIQUE_CONSTRAINT_NAME
                And TC.CONSTRAINT_TYPE = 'PRIMARY KEY'
        Join INFORMATION_SCHEMA.CONSTRAINT_COLUMN_USAGE As PK_CCU
            On PK_CCU.CONSTRAINT_SCHEMA = TC.CONSTRAINT_SCHEMA
                And PK_CCU.CONSTRAINT_NAME = TC.CONSTRAINT_NAME
    Where PK_CCU.TABLE_SCHEMA = 'dbo'
        And PK_CCU.TABLE_NAME = @TableParam
        And PK_CCU.COLUMN_NAME = @ColumnParam

Open @Columns 
Fetch Next From @Columns Into @PKTableSchema, @PKTableName, @PKColumnName, @TableSchema, @TableName, @ColumnName

Set @Sql = 'Delete ' + @PKTableSchema + '.' + @PKTableName 
    + ' Where 1 = 1' 

While @@Fetch_Status = 0
Begin
    Set @Sql = @Sql + Char(13) + Char(10)
        + ' And Not Exists ( Select 1 From ' + @TableSchema + '.' + @TableName
                            + ' Where ' + @ColumnName + ' = ' + @PKTableSchema + '.' + @PKTableName + '.' + @PKColumnName
                            + ')'
    Fetch Next From @Columns Into @PKTableSchema, @PKTableName, @PKColumnName, @TableSchema, @TableName, @ColumnName
End

Close @Columns
Deallocate @Columns

--Print @Sql
Exec( @Sql )

Btw, let me also mention that a better solution might be to enable Cascade Delete on your relations. With cascade delete, the need for this routine would be moot.

See Cascading Referential Integrity Constraints for more.