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

Question

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

我所说的架构是指表、约束等。我希望结果与创建新数据库时的结果类似，但我不想实际删除并创建数据库。

原因:

对于那些好奇的人，由于数据库为单元测试而隔离的方式，我需要在不删除的情况下清空架构。在运行我的测试之前，数据库的快照被保存。每次测试运行后，都会恢复此快照。如果我将数据库操作保持在数据库范围内，我只能确保跨单元测试的状态一致。删除/创建数据库在数据库的范围之外(它在主人的范围内)。

在这种情况下，我需要断言当模式为空时会发生预期的事情。通过 sql 清空模式使测试方法保持一致:基本上对数据库做任何您想做的事情，运行它，恢复它。

Raj More 的回答让我开始了。我希望有人可以缩短流程。

Answer 1

想我会分享我最终想出的东西。此脚本创建一个游标以遍历数据库 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