手机
当前位置:查字典教程网 >编程开发 >mssql数据库 >SqlServer批量清理指定数据库中所有数据
SqlServer批量清理指定数据库中所有数据
摘要:在实际应用中,当我们准备把一个项目移交至客户手中使用时,我们需要把库中所有表先前的测试数据清空,以给客户一个干净的数据库,如果涉及的表很多,...

在实际应用中,当我们准备把一个项目移交至客户手中使用时,我们需要把库中所有表先前的测试数据清空,以给客户一个干净的数据库,如果涉及的表很多,要一一的清空,不仅花费时间,还容易出错以及漏删,在这儿我提供了一个方法,可快捷有效的清空指定数据库所有表的数据。仅供参考,欢迎交流不同意见。

--Remove all data from a database

SET NOCOUNT ON

--Tables to ignore

DECLARE @IgnoreTables

TABLE (TableName varchar(512))

INSERT INTO @IgnoreTables (TableName) VALUES ('sysdiagrams')

DECLARE @AllRelationships

TABLE (ForeignKey varchar(512)

,TableName varchar(512)

,ColumnName varchar(512)

,ReferenceTableName varchar(512)

,ReferenceColumnName varchar(512)

,DeleteRule varchar(512))

INSERT INTO @AllRelationships

SELECT f.name AS ForeignKey,

OBJECT_NAME(f.parent_object_id) AS TableName,

COL_NAME(fc.parent_object_id,

fc.parent_column_id) AS ColumnName,

OBJECT_NAME (f.referenced_object_id) AS ReferenceTableName,

COL_NAME(fc.referenced_object_id,

fc.referenced_column_id) AS ReferenceColumnName,

delete_referential_action_desc as DeleteRule

FROM sys.foreign_keys AS f

INNER JOIN sys.foreign_key_columns AS fc

ON f.OBJECT_ID = fc.constraint_object_id

DECLARE @TableOwner varchar(512)

DECLARE @TableName varchar(512)

DECLARE @ForeignKey varchar(512)

DECLARE @ColumnName varchar(512)

DECLARE @ReferenceTableName varchar(512)

DECLARE @ReferenceColumnName varchar(512)

DECLARE @DeleteRule varchar(512)

PRINT('Loop through all tables and switch all constraints to have a delete rule of CASCADE')

DECLARE DataBaseTables0

CURSOR FOR

SELECT SCHEMA_NAME(t.schema_id) AS schema_name, t.name AS table_name

FROM sys.tables AS t;

OPEN DataBaseTables0;

FETCH NEXT FROM DataBaseTables0

INTO @TableOwner,@TableName;

WHILE @@FETCH_STATUS = 0

BEGIN

IF (NOT EXISTS(SELECT TOP 1 1 FROM @IgnoreTables WHERE TableName = @TableName))

BEGIN

PRINT '['+@TableOwner+'].[' + @TableName + ']';

DECLARE DataBaseTableRelationships CURSOR FOR

SELECT ForeignKey, ColumnName, ReferenceTableName, ReferenceColumnName

FROM @AllRelationships

WHERE TableName = @TableName

OPEN DataBaseTableRelationships;

FETCH NEXT FROM DataBaseTableRelationships INTO @ForeignKey, @ColumnName, @ReferenceTableName, @ReferenceColumnName;

IF @@FETCH_STATUS <> 0

PRINT '=====> No Relationships' ;

WHILE @@FETCH_STATUS = 0

BEGIN

PRINT '=====> switching delete rule on ' + @ForeignKey + ' to CASCADE';

BEGIN TRANSACTION

BEGIN TRY

EXEC('

ALTER TABLE ['+@TableOwner+'].[' + @TableName + ']

DROP CONSTRAINT '+@ForeignKey+';

ALTER TABLE ['+@TableOwner+'].[' + @TableName + '] ADD CONSTRAINT

'+@ForeignKey+' FOREIGN KEY

(

'+@ColumnName+'

) REFERENCES '+@ReferenceTableName+'

(

'+@ReferenceColumnName+'

) ON DELETE CASCADE;

');

COMMIT TRANSACTION

END TRY

BEGIN CATCH

PRINT '=====> can''t switch ' + @ForeignKey + ' to CASCADE, - ' +

CAST(ERROR_NUMBER() AS VARCHAR) + ' - ' + ERROR_MESSAGE();

ROLLBACK TRANSACTION

END CATCH;

FETCH NEXT FROM DataBaseTableRelationships INTO @ForeignKey, @ColumnName, @ReferenceTableName, @ReferenceColumnName;

END;

CLOSE DataBaseTableRelationships;

DEALLOCATE DataBaseTableRelationships;

END

PRINT '';

PRINT '';

FETCH NEXT FROM DataBaseTables0

INTO @TableOwner,@TableName;

END

CLOSE DataBaseTables0;

DEALLOCATE DataBaseTables0;

PRINT('Loop though each table and DELETE All data from the table')

DECLARE DataBaseTables1 CURSOR FOR

SELECT SCHEMA_NAME(t.schema_id) AS schema_name, t.name AS table_name

FROM sys.tables AS t;

OPEN DataBaseTables1;

FETCH NEXT FROM DataBaseTables1

INTO @TableOwner,@TableName;

WHILE @@FETCH_STATUS = 0

BEGIN

IF (NOT EXISTS(SELECT TOP 1 1 FROM @IgnoreTables WHERE TableName = @TableName))

BEGIN

PRINT '['+@TableOwner+'].[' + @TableName + ']';

PRINT '=====> deleting data from ['+@TableOwner+'].[' + @TableName + ']';

BEGIN TRY

EXEC('

DELETE FROM ['+@TableOwner+'].[' + @TableName + ']

DBCC CHECKIDENT ([' + @TableName + '], RESEED, 0)

');

END TRY

BEGIN CATCH

PRINT '=====> can''t FROM ['+@TableOwner+'].[' + @TableName + '], - ' +

CAST(ERROR_NUMBER() AS VARCHAR) + ' - ' + ERROR_MESSAGE();

END CATCH;

END

PRINT '';

PRINT '';

FETCH NEXT FROM DataBaseTables1

INTO @TableOwner,@TableName;

END

CLOSE DataBaseTables1;

DEALLOCATE DataBaseTables1;

PRINT('Loop through all tables and switch all constraints to have a delete rule they had at the beggining of the task')

DECLARE DataBaseTables2 CURSOR FOR

SELECT SCHEMA_NAME(t.schema_id) AS schema_name, t.name AS table_name

FROM sys.tables AS t;

OPEN DataBaseTables2;

FETCH NEXT FROM DataBaseTables2

INTO @TableOwner,@TableName;

WHILE @@FETCH_STATUS = 0

BEGIN

IF (NOT EXISTS(SELECT TOP 1 1 FROM @IgnoreTables WHERE TableName = @TableName))

BEGIN

PRINT '['+@TableOwner+'].[' + @TableName + ']';

DECLARE DataBaseTableRelationships CURSOR FOR

SELECT ForeignKey, ColumnName, ReferenceTableName, ReferenceColumnName, DeleteRule

FROM @AllRelationships

WHERE TableName = @TableName

OPEN DataBaseTableRelationships;

FETCH NEXT FROM DataBaseTableRelationships INTO @ForeignKey, @ColumnName, @ReferenceTableName, @ReferenceColumnName, @DeleteRule;

IF @@FETCH_STATUS <> 0

PRINT '=====> No Relationships' ;

WHILE @@FETCH_STATUS = 0

BEGIN

DECLARE @switchBackTo varchar(50) =

CASE

WHEN @DeleteRule = 'NO_ACTION' THEN 'NO ACTION'

WHEN @DeleteRule = 'CASCADE' THEN 'CASCADE'

WHEN @DeleteRule = 'SET_NULL' THEN 'SET NULL'

WHEN @DeleteRule = 'SET_DEFAULT' THEN 'SET DEFAULT'

END

PRINT '=====> switching delete rule on ' + @ForeignKey + ' to ' + @switchBackTo;

BEGIN TRANSACTION

BEGIN TRY

EXEC('

ALTER TABLE ['+@TableOwner+'].[' + @TableName + ']

DROP CONSTRAINT '+@ForeignKey+';

ALTER TABLE ['+@TableOwner+'].[' + @TableName + '] ADD CONSTRAINT

'+@ForeignKey+' FOREIGN KEY

(

'+@ColumnName+'

) REFERENCES '+@ReferenceTableName+'

(

'+@ReferenceColumnName+'

) ON DELETE '+@switchBackTo+'

');

COMMIT TRANSACTION

END TRY

BEGIN CATCH

PRINT '=====> can''t change '+@ForeignKey + ' back to '+ @switchBackTo +', - ' +

CAST(ERROR_NUMBER() AS VARCHAR) + ' - ' + ERROR_MESSAGE();

ROLLBACK TRANSACTION

END CATCH;

FETCH NEXT FROM DataBaseTableRelationships

INTO @ForeignKey, @ColumnName, @ReferenceTableName, @ReferenceColumnName, @DeleteRule;

END;

CLOSE DataBaseTableRelationships;

DEALLOCATE DataBaseTableRelationships;

END

PRINT '';

PRINT '';

FETCH NEXT FROM DataBaseTables2

INTO @TableOwner,@TableName;

END

CLOSE DataBaseTables2;

DEALLOCATE DataBaseTables2;

【SqlServer批量清理指定数据库中所有数据】相关文章:

Sql 批量查看字符所在的表及字段

SQLServer 数据集合的交、并、差集运算

在SQL Server 2005所有表中搜索某个指定列的方法

sql server 临时表 查找并删除的实现代码

Sql Server 代理错误日志知多少

显示 Sql Server 中所有表中的信息

Sql Server 存储过程实例讲解

Eclipse 连接 SQL Server 2012数据库

sql 随机抽取几条数据的方法 推荐

SQL Server2005 中的数据类型总结

精品推荐
分类导航