SQL Server - 删除未使用的行
问题描述:
我需要获取存储过程(不是标准的,只有自定义)才能删除未使用的行表(table1)和其他一些表(table2,table3,table4 ...- 我们不知道FOREIGN KEY CONSTRAINT)可以引用多少个表引用table1。SQL Server - 删除未使用的行
例如:
С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
有在Table 1和表2,表3是指表1(提醒的是,我们不知道有多少表是指由外键约束表1)
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)
有在表行之前调用存储过程
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
而且后 [DBO]。[delete_on_constraints]“table1的“身份证”过程调用
table1
id value
1 100
2 200
3 300
答
首先,请允许我对这段代码不应该在生产中使用的记录。我的意思是,它应该只能用作一次性清理。如果您经常需要在不知道DRI的情况下删除行,那么存在基本的设计问题。
简而言之,您需要使用动态SQL来查询关系并构建Delete语句。
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)
顺便说一句,让我也提一个更好的解决方案可能是启用级联删除您的关系。通过级联删除,这个例程的需要将是没有意义的。