数据库表的指定字段添加数据 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;