成熟丰满熟妇高潮XXXXX,人妻无码AV中文系列久久兔费 ,国产精品一国产精品,国精品午夜福利视频不卡麻豆

您好,歡迎來到九壹網(wǎng)。
搜索
您的當(dāng)前位置:首頁SqlServer批量清理指定數(shù)據(jù)庫中所有數(shù)據(jù)

SqlServer批量清理指定數(shù)據(jù)庫中所有數(shù)據(jù)

來源:九壹網(wǎng)

在實際應(yīng)用中,當(dāng)我們準(zhǔn)備把一個項目移交至客戶手中使用時,我們需要把庫中所有表先前的測試數(shù)據(jù)清空,以給客戶一個干凈的數(shù)據(jù)庫,如果涉及的表很多,要一一的清空,不僅花費(fèi)時間,還容易出錯以及漏刪,在這兒我提供了一個方法,可快捷有效的清空指定數(shù)據(jù)庫

  在實際應(yīng)用中,,當(dāng)我們準(zhǔn)備把一個項目移交至客戶手中使用時,我們需要把庫中所有表先前的測試數(shù)據(jù)清空,以給客戶一個干凈的數(shù)據(jù)庫,如果涉及的表很多,要一一的清空,不僅花費(fèi)時間,還容易出錯以及漏刪,在這兒我提供了一個方法,可快捷有效的清空指定數(shù)據(jù)庫所有表的數(shù)據(jù)。僅供參考,歡迎交流不同意見。

  --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;

Copyright ? 2019- 91gzw.com 版權(quán)所有 湘ICP備2023023988號-2

違法及侵權(quán)請聯(lián)系:TEL:199 1889 7713 E-MAIL:2724546146@qq.com

本站由北京市萬商天勤律師事務(wù)所王興未律師提供法律服務(wù)