用TSQL清除sql-server中数据库的模式？

admin

用TSQL清除sql-server中数据库的模式？

sql

我想在我的SqlServer实例上清除数据库的架构。我应该使用什么tsql？

模式是指表，约束等。我希望结果类似于创建新数据库的结果，但是我实际上并不希望删除并创建数据库。

为什么：

对于那些好奇的人，由于需要隔离数据库以进行单元测试，因此我需要清空架构而不删除它。在运行测试之前，将保存数据库快照。运行每个测试后，将还原此快照。如果我将数据库操作保持在数据库范围内，则只能确保单元测试之间的状态一致。删除/创建数据库不在db的范围内（它在master的范围内）。

在这种情况下，我需要断言在架构为空时会发生预期的事情。通过sql清空架构可保持测试方法的一致性：基本上对数据库执行任何您想执行的操作，执行它，还原它。

拉吉·莫尔（Raj More）的回答让我开始。我希望有人可以缩短流程。

阅读 211

2021-07-01

共1个答案

admin

想通了，我会分享我最终想出的东西。该脚本创建一个游标，以遍历数据库的INFORMATION_SCHEMA中的表。它对表进行3次传递，分别是外键，然后是主键，最后是表本身。它基于Raj
More的想法，并考虑了devio的评论。

-- Helper Procedure
CREATE PROC #DropConstraints
  @tableSchema nvarchar(max),
  @tableName nvarchar(max),
  @constraintType nvarchar(20)
AS
BEGIN
  DECLARE @cName nvarchar(max);

  DECLARE constraint_cursor CURSOR FOR
    SELECT CONSTRAINT_NAME 
    FROM INFORMATION_SCHEMA.TABLE_CONSTRAINTS
    WHERE 
      CONSTRAINT_TYPE = @constraintType
      AND TABLE_NAME = @tableName
      AND TABLE_SCHEMA = @tableSchema

  OPEN constraint_cursor

  FETCH NEXT FROM constraint_cursor INTO @cName
  WHILE @@FETCH_STATUS = 0
  BEGIN
    EXEC ('ALTER TABLE ' + @tableSchema + '.' + @tableName + ' DROP CONSTRAINT ' + @cName);
    FETCH NEXT FROM constraint_cursor INTO @cName
  END

  CLOSE constraint_cursor
  DEALLOCATE constraint_cursor
END
GO

-- DROP DATABASE TABLES
BEGIN TRANSACTION
  DECLARE @tableSchema varchar(max), @tableName varchar(max);

  -- Setup Cursor for looping
  DECLARE table_cursor SCROLL CURSOR FOR
    SELECT TABLE_SCHEMA, TABLE_NAME 
    FROM INFORMATION_SCHEMA.TABLES

  OPEN table_cursor

  -- Drop Foreign Keys
  FETCH NEXT FROM table_cursor INTO @tableSchema, @tableName
  WHILE @@FETCH_STATUS = 0
  BEGIN  
    EXEC #DropConstraints @tableSchema, @tableName, 'FOREIGN KEY';

    FETCH NEXT FROM table_cursor INTO @tableSchema, @tableName
  END

  -- Drop Primary Keys
  FETCH FIRST FROM table_cursor INTO @tableSchema, @tableName
  WHILE @@FETCH_STATUS = 0
  BEGIN
    EXEC #DropConstraints @tableSchema, @tableName, 'PRIMARY KEY';

    FETCH NEXT FROM table_cursor INTO @tableSchema, @tableName
  END

  -- Drop Tables
  FETCH FIRST FROM table_cursor INTO @tableSchema, @tableName
  WHILE @@FETCH_STATUS = 0
  BEGIN
    EXEC ('DROP TABLE ' + @tableSchema + '.' + @tableName);

    FETCH NEXT FROM table_cursor INTO @tableSchema, @tableName
  END

  -- Cleanup
  CLOSE table_cursor
  DEALLOCATE table_cursor
COMMIT TRANSACTION
GO

2021-07-01