如何删除所有表并重置 Azure SQL 数据库

本文关键字：Azure 数据库 SQL 何删除 删除 | 更新日期: 2023-09-27 18:31:32

我有一个在本地工作的 ASP.NET MVC 5 项目，每当我需要吹走数据库时，我只需在其上打开一个新查询，将可用数据库下拉列表更改为 master，然后关闭本地数据库上的连接并运行查询"删除数据库 [name]"。 然后我构建项目，进入包管理器控制台并运行"更新数据库"。这似乎重建了一个全新的本地数据库，并在我的配置.cs文件中运行 seed 方法。

问题是当我需要在实时环境中测试内容以便我可以更好地测试 API 时，我将部署到 Azure 网站和随附的 Azure DB，这很好而且很容易做到。我在发布向导中勾选了"执行代码优先迁移"，大多数时候它都可以工作，我可以运行和调试我的实时版本。有时我需要吹掉该数据库并从头开始，但我真正找到的唯一方法是进入 Azure 门户，删除数据库，然后使用相同的名称重新创建它。Azure 需要一些时间来处理，因此这是一个缓慢的测试周期。

有没有一种快速的方法可以将 Azure SQL 数据库删除/重置为新鲜、空、原始状态，然后使用"执行代码优先迁移"重新发布，让它重新创建表并重新设定数据种子？

我看到过一些关于在创建数据库后创建初始迁移的讨论，然后尝试使用 Powershell 进行某种回滚到该初始状态，但我没有运气让它工作，我想同时删除所有数据。也许我只是语法错误或没有找到足够好的教程。虽然我可以在 Azure DB 上运行查询以"删除数据库 [x]"，但它实际上会像预期的那样终止 SQL Azure 数据库实例，您需要返回门户以重新创建它。有时，由于模型已经更新，因此初始状态并不好，因此无论如何这可能没有用。

我觉得应该有一些更简单的方法来测试实时环境中的更改，因为 MS 提供了所有这些很棒的工具和快捷方式，但是他们只是在这个开发阶段丢了球还是我错过了什么？

如何删除所有表并重置 Azure SQL 数据库

由于我知道没有 API 方法来执行此操作，因此我们使用此脚本来利用 T-SQL 查询来清除数据库。

删除每个表（并根据需要维护 EF 迁移历史记录）

while(exists(select 1 from INFORMATION_SCHEMA.TABLES 
             where TABLE_NAME != '__MigrationHistory' 
             AND TABLE_TYPE = 'BASE TABLE'))
begin
 declare @sql nvarchar(2000)
 SELECT TOP 1 @sql=('DROP TABLE ' + TABLE_SCHEMA + '.[' + TABLE_NAME
 + ']')
 FROM INFORMATION_SCHEMA.TABLES
 WHERE TABLE_NAME != '__MigrationHistory' AND TABLE_TYPE = 'BASE TABLE'
exec (@sql)
 /* you dont need this line, it just shows what was executed */
 PRINT @sql
end

如果需要，请先删除外键

while(exists(select 1 from INFORMATION_SCHEMA.TABLE_CONSTRAINTS where CONSTRAINT_TYPE='FOREIGN KEY'))
begin
 declare @sql nvarchar(2000)
 SELECT TOP 1 @sql=('ALTER TABLE ' + TABLE_SCHEMA + '.[' + TABLE_NAME
 + '] DROP CONSTRAINT [' + CONSTRAINT_NAME + ']')
 FROM information_schema.table_constraints
 WHERE CONSTRAINT_TYPE = 'FOREIGN KEY'
 exec (@sql)
 PRINT @sql
end

在我的测试中，这没有问题（除了我在查询时没有 DROP 表中的 where 子句，因为我不使用代码优先或 EF 迁移）。

只是为了补充答案，因为接受的答案在 Azure 上对我不起作用。使用以下脚本删除所有表，并基本上重置 azure 数据库。它首先删除所有约束，然后删除所有表。

正如@Skorunka František评论的那样，此脚本假设您使用默认的[dbo]模式。尽管您可以将其替换为自己的架构名称。

/* Azure friendly */
/* Drop all Foreign Key constraints */
DECLARE @name VARCHAR(128)
DECLARE @constraint VARCHAR(254)
DECLARE @SQL VARCHAR(254)
SELECT @name = (SELECT TOP 1 TABLE_NAME FROM INFORMATION_SCHEMA.TABLE_CONSTRAINTS WHERE constraint_catalog=DB_NAME() AND CONSTRAINT_TYPE = 'FOREIGN KEY' ORDER BY TABLE_NAME)
WHILE @name is not null
BEGIN
    SELECT @constraint = (SELECT TOP 1 CONSTRAINT_NAME FROM INFORMATION_SCHEMA.TABLE_CONSTRAINTS WHERE constraint_catalog=DB_NAME() AND CONSTRAINT_TYPE = 'FOREIGN KEY' AND TABLE_NAME = @name ORDER BY CONSTRAINT_NAME)
    WHILE @constraint IS NOT NULL
    BEGIN
        SELECT @SQL = 'ALTER TABLE [dbo].[' + RTRIM(@name) +'] DROP CONSTRAINT [' + RTRIM(@constraint) +']'
        EXEC (@SQL)
        PRINT 'Dropped FK Constraint: ' + @constraint + ' on ' + @name
        SELECT @constraint = (SELECT TOP 1 CONSTRAINT_NAME FROM INFORMATION_SCHEMA.TABLE_CONSTRAINTS WHERE constraint_catalog=DB_NAME() AND CONSTRAINT_TYPE = 'FOREIGN KEY' AND CONSTRAINT_NAME <> @constraint AND TABLE_NAME = @name ORDER BY CONSTRAINT_NAME)
    END
SELECT @name = (SELECT TOP 1 TABLE_NAME FROM INFORMATION_SCHEMA.TABLE_CONSTRAINTS WHERE constraint_catalog=DB_NAME() AND CONSTRAINT_TYPE = 'FOREIGN KEY' ORDER BY TABLE_NAME)
END
GO
/* Drop all Primary Key constraints */
DECLARE @name VARCHAR(128)
DECLARE @constraint VARCHAR(254)
DECLARE @SQL VARCHAR(254)
SELECT @name = (SELECT TOP 1 TABLE_NAME FROM INFORMATION_SCHEMA.TABLE_CONSTRAINTS WHERE constraint_catalog=DB_NAME() AND CONSTRAINT_TYPE = 'PRIMARY KEY' ORDER BY TABLE_NAME)
WHILE @name IS NOT NULL
BEGIN
    SELECT @constraint = (SELECT TOP 1 CONSTRAINT_NAME FROM INFORMATION_SCHEMA.TABLE_CONSTRAINTS WHERE constraint_catalog=DB_NAME() AND CONSTRAINT_TYPE = 'PRIMARY KEY' AND TABLE_NAME = @name ORDER BY CONSTRAINT_NAME)
    WHILE @constraint is not null
    BEGIN
        SELECT @SQL = 'ALTER TABLE [dbo].[' + RTRIM(@name) +'] DROP CONSTRAINT [' + RTRIM(@constraint)+']'
        EXEC (@SQL)
        PRINT 'Dropped PK Constraint: ' + @constraint + ' on ' + @name
        SELECT @constraint = (SELECT TOP 1 CONSTRAINT_NAME FROM INFORMATION_SCHEMA.TABLE_CONSTRAINTS WHERE constraint_catalog=DB_NAME() AND CONSTRAINT_TYPE = 'PRIMARY KEY' AND CONSTRAINT_NAME <> @constraint AND TABLE_NAME = @name ORDER BY CONSTRAINT_NAME)
    END
SELECT @name = (SELECT TOP 1 TABLE_NAME FROM INFORMATION_SCHEMA.TABLE_CONSTRAINTS WHERE constraint_catalog=DB_NAME() AND CONSTRAINT_TYPE = 'PRIMARY KEY' ORDER BY TABLE_NAME)
END
GO
/* Drop all tables */
DECLARE @name VARCHAR(128)
DECLARE @SQL VARCHAR(254)
SELECT @name = (SELECT TOP 1 [name] FROM sysobjects WHERE [type] = 'U' AND category = 0 ORDER BY [name])
WHILE @name IS NOT NULL
BEGIN
    SELECT @SQL = 'DROP TABLE [dbo].[' + RTRIM(@name) +']'
    EXEC (@SQL)
    PRINT 'Dropped Table: ' + @name
    SELECT @name = (SELECT TOP 1 [name] FROM sysobjects WHERE [type] = 'U' AND category = 0 AND [name] > @name ORDER BY [name])
END
GO

可悲的是，我再也找不到此代码的来源，因为我已将其保存在我的一个存储库中。我希望它对某人有所帮助。

只是为了将我的变体添加到组合中...这也考虑了视图和外部表。它不会在必须使用"删除外部表"单独删除的外部表上出现。这导致原始版本永远旋转。

while(exists(select 1 from INFORMATION_SCHEMA.TABLE_CONSTRAINTS where CONSTRAINT_TYPE='FOREIGN KEY'))
begin
 declare @sql nvarchar(2000)
 SELECT TOP 1 @sql=('ALTER TABLE ' + TABLE_SCHEMA + '.[' + TABLE_NAME
 + '] DROP CONSTRAINT [' + CONSTRAINT_NAME + ']')
 FROM information_schema.table_constraints
 WHERE CONSTRAINT_TYPE = 'FOREIGN KEY'
 exec (@sql)
 PRINT @sql
end

while(exists(select 1 from INFORMATION_SCHEMA.TABLES 
    where TABLE_NAME != 'database_firewall_rules' 
    AND TABLE_TYPE = 'BASE TABLE'
    AND TABLE_NAME NOT IN (select name from sys.external_tables)))
begin
 declare @sql1 nvarchar(2000)
 SELECT TOP 1 @sql1=('DROP TABLE ' + TABLE_SCHEMA + '.[' + TABLE_NAME + ']')
 FROM INFORMATION_SCHEMA.TABLES
 WHERE TABLE_NAME != 'database_firewall_rules'
    AND TABLE_TYPE = 'BASE TABLE'
    AND TABLE_NAME NOT IN (select name from sys.external_tables)
exec (@sql1)
 PRINT @sql1
end

我通常

    打开 SQL Server
  1. Management Studio 或从 Visual Studio 打开 SQL Server 对象资源管理器
    2. 我连接到 Azure SQL Server
  2. （即：使用选择了用于 SQL Server 身份验证选项的用户名和密码进行yourserver.database.windows.net）（另请记住，你需要在 Azure 门户中添加防火墙例外才能以这种方式从电脑连接到数据库）
  3. 右键单击数据库并删除。

就这么简单。

然后，由于你提到你有一个代码优先迁移方法，只需在 Azure SQL Server 中再次运行迁移（例如，在发布时，请确保选中为给定 sql Server 连接字符串应用迁移的选项）

我通常会删除远程数据库，然后使用命令重新部署应用程序以重新运行迁移。这将再次创建包含新表的数据库。用于设定数据库种子的代码位于我的启动代码中，因此如果数据库中没有值，则在初始化应用时随时为其设定种子。

这也适用于 AspNet Core Mvc （MVC6）

Azure SQL

本质上是SQL服务器。因此，您可以使用MS SQL Server Management Studio https://learn.microsoft.com/en-us/sql/ssms/download-sql-server-management-studio-ssms

使用管理员帐户在 Management Studio 中打开数据库，然后执行拖放表 SQL 命令或使用 GUI 右键单击菜单执行任何您想要的操作，就像使用本地数据库一样。

类似的事情也可以在Visual Studio SQL Server对象资源管理器中完成。只需右键单击根"添加SQL服务器"。

对@Gizmo3399给出的答案的补充。我们使用时态表与Entity Framework (EF) Core 5.0.2与.NET 5。这是我们用来清除数据库的命令。 History是我们用于系统版本控制的时态表的架构。目前需要运行两次才能删除所有内容，否则无需修改。

/* Azure friendly */
/* Drop all Foreign Key constraints */
DECLARE @name VARCHAR(128)
DECLARE @constraint VARCHAR(254)
DECLARE @SQL VARCHAR(254)
DECLARE @SQL2 VARCHAR(254)
SELECT @name = (SELECT TOP 1 TABLE_NAME FROM INFORMATION_SCHEMA.TABLE_CONSTRAINTS WHERE constraint_catalog=DB_NAME() AND CONSTRAINT_TYPE = 'FOREIGN KEY' ORDER BY TABLE_NAME)
WHILE @name is not null
BEGIN
    SELECT @constraint = (SELECT TOP 1 CONSTRAINT_NAME FROM INFORMATION_SCHEMA.TABLE_CONSTRAINTS WHERE constraint_catalog=DB_NAME() AND CONSTRAINT_TYPE = 'FOREIGN KEY' AND TABLE_NAME = @name ORDER BY CONSTRAINT_NAME)
    WHILE @constraint IS NOT NULL
    BEGIN
        IF EXISTS (SELECT * FROM sys.schemas WHERE name = 'History') AND @name != '__EFMigrationsHistory'
            BEGIN
                 SELECT @SQL = 'ALTER TABLE [history].[' + RTRIM(@name) +'] DROP CONSTRAINT [' + RTRIM(@constraint) +']'
                 EXEC (@SQL)
            END
        SELECT @SQL2 = 'ALTER TABLE [dbo].[' + RTRIM(@name) +'] DROP CONSTRAINT [' + RTRIM(@constraint) +']'
        EXEC (@SQL2)
        PRINT 'Dropped FK Constraint: ' + @constraint + ' on ' + @name
        SELECT @constraint = (SELECT TOP 1 CONSTRAINT_NAME FROM INFORMATION_SCHEMA.TABLE_CONSTRAINTS WHERE constraint_catalog=DB_NAME() AND CONSTRAINT_TYPE = 'FOREIGN KEY' AND CONSTRAINT_NAME <> @constraint AND TABLE_NAME = @name ORDER BY CONSTRAINT_NAME)
    END
SELECT @name = (SELECT TOP 1 TABLE_NAME FROM INFORMATION_SCHEMA.TABLE_CONSTRAINTS WHERE constraint_catalog=DB_NAME() AND CONSTRAINT_TYPE = 'FOREIGN KEY' ORDER BY TABLE_NAME)
END
GO
/* Drop all Primary Key constraints */
DECLARE @name VARCHAR(128)
DECLARE @constraint VARCHAR(254)
DECLARE @SQL VARCHAR(254)
DECLARE @SQL2 VARCHAR(254)
SELECT @name = (SELECT TOP 1 TABLE_NAME FROM INFORMATION_SCHEMA.TABLE_CONSTRAINTS WHERE constraint_catalog=DB_NAME() AND CONSTRAINT_TYPE = 'PRIMARY KEY' ORDER BY TABLE_NAME)
WHILE @name IS NOT NULL
BEGIN
    SELECT @constraint = (SELECT TOP 1 CONSTRAINT_NAME FROM INFORMATION_SCHEMA.TABLE_CONSTRAINTS WHERE constraint_catalog=DB_NAME() AND CONSTRAINT_TYPE = 'PRIMARY KEY' AND TABLE_NAME = @name ORDER BY CONSTRAINT_NAME)
    WHILE @constraint is not null
    BEGIN
        IF EXISTS (SELECT * FROM sys.schemas WHERE name = 'History') AND @name != '__EFMigrationsHistory'
            BEGIN
                SELECT @SQL = 'ALTER TABLE [history].[' + RTRIM(@name) +'] DROP CONSTRAINT [' + RTRIM(@constraint)+']'
                EXEC (@SQL)
            END
        SELECT @SQL2 = 'ALTER TABLE [dbo].[' + RTRIM(@name) +'] DROP CONSTRAINT [' + RTRIM(@constraint) +']'
        EXEC (@SQL2)
        PRINT 'Dropped PK Constraint: ' + @constraint + ' on ' + @name
        SELECT @constraint = (SELECT TOP 1 CONSTRAINT_NAME FROM INFORMATION_SCHEMA.TABLE_CONSTRAINTS WHERE constraint_catalog=DB_NAME() AND CONSTRAINT_TYPE = 'PRIMARY KEY' AND CONSTRAINT_NAME <> @constraint AND TABLE_NAME = @name ORDER BY CONSTRAINT_NAME)
    END
SELECT @name = (SELECT TOP 1 TABLE_NAME FROM INFORMATION_SCHEMA.TABLE_CONSTRAINTS WHERE constraint_catalog=DB_NAME() AND CONSTRAINT_TYPE = 'PRIMARY KEY' ORDER BY TABLE_NAME)
END
GO
/* Drop all tables */
DECLARE @name VARCHAR(128)
DECLARE @SQL VARCHAR(254)
DECLARE @SQL2 VARCHAR(254)
DECLARE @SQL3 VARCHAR(254)
SELECT @name = (SELECT TOP 1 [name] FROM sysobjects WHERE [type] = 'U' AND category = 0 ORDER BY [name])
WHILE @name IS NOT NULL
BEGIN
    IF EXISTS (SELECT * FROM sys.schemas WHERE name = 'History') AND @name != '__EFMigrationsHistory'
        BEGIN
            SELECT @SQL = ('ALTER TABLE [' + RTRIM(@name) +'] SET (SYSTEM_VERSIONING = OFF)');
            EXEC (@SQL)
            SELECT @SQL2 = 'DROP TABLE [history].[' + RTRIM(@name) +']'
            EXEC (@SQL2)
        END
    SELECT @SQL3 = 'DROP TABLE [dbo].[' + RTRIM(@name) +']'
    EXEC (@SQL3)
    PRINT 'Dropped Table: ' + @name
    SELECT @name = (SELECT TOP 1 [name] FROM sysobjects WHERE [type] = 'U' AND category = 0 AND [name] > @name ORDER BY [name])
END
GO
IF EXISTS (SELECT * FROM sys.schemas WHERE name = 'History')
BEGIN
    DROP SCHEMA History
END
GO

如果有人想要这样的脚本，但按架构过滤然后你去：

/* Azure friendly */
/* Drop all Foreign Key constraints */
/* Arguments */
DECLARE @schema VARCHAR(128) = '<TODO_PROVIDE_SCHEMA>'

/* Variables */
DECLARE @tableName VARCHAR(128)
DECLARE @tableSchema VARCHAR(128)
DECLARE @constraintName VARCHAR(254)
DECLARE @SQL VARCHAR(254)

SELECT TOP 1
   @tableName = sourceTable.name,
   @tableSchema = SCHEMA_NAME(sourceTable.schema_id),
   @constraintName = f.name
FROM sys.foreign_keys AS f
INNER JOIN sys.foreign_key_columns AS fc ON f.OBJECT_ID = fc.constraint_object_id
INNER JOIN sys.tables targetTable ON targetTable.OBJECT_ID = fc.referenced_object_id
INNER JOIN sys.tables sourceTable ON f.parent_object_id = sourceTable.object_id
WHERE targetTable.[schema_id] = SCHEMA_ID(@schema)
ORDER BY sourceTable.name
WHILE @tableName is not null
BEGIN
    SELECT @SQL = 'ALTER TABLE [' + RTRIM(@tableSchema) + '].[' + RTRIM(@tableName) +'] DROP CONSTRAINT [' + RTRIM(@constraintName) +']'
    EXEC (@SQL)
    PRINT 'Executed: ' + @SQL
    
    SET @tableName = NULL;
    SELECT TOP 1
       @tableName = sourceTable.name,
       @tableSchema = SCHEMA_NAME(sourceTable.schema_id),
       @constraintName = f.name
    FROM sys.foreign_keys AS f
    INNER JOIN sys.foreign_key_columns AS fc ON f.OBJECT_ID = fc.constraint_object_id
    INNER JOIN sys.tables targetTable ON targetTable.OBJECT_ID = fc.referenced_object_id
    INNER JOIN sys.tables sourceTable ON f.parent_object_id = sourceTable.object_id
    WHERE targetTable.[schema_id] = SCHEMA_ID(@schema)
    ORDER BY sourceTable.name
END
GO
/* Drop all Primary Key constraints */
DECLARE @schema VARCHAR(128) = '<TODO_PROVIDE_SCHEMA>'
DECLARE @tableName VARCHAR(128)
DECLARE @tableSchema VARCHAR(128)
DECLARE @constraintName VARCHAR(254)
DECLARE @constraintSchema VARCHAR(128)
DECLARE @SQL VARCHAR(254)

SELECT 
    @tableName = t.name,
    @tableSchema = SCHEMA_NAME(t.schema_id),
    @constraintName = pk.name,
    @constraintSchema = SCHEMA_NAME(t.schema_id)
FROM sys.objects pk 
JOIN sys.objects t ON (pk.parent_object_id = t.object_id)
WHERE pk.[type] = 'PK'
    AND pk.schema_id = SCHEMA_ID(@schema)
ORDER BY t.name 
WHILE @tableName is not null
BEGIN
    SELECT @SQL = 'ALTER TABLE [' + RTRIM(@tableSchema) + '].[' + RTRIM(@tableName) +'] DROP CONSTRAINT [' + RTRIM(@constraintName) +']'
    PRINT 'Executing: ' + @SQL
    EXEC (@SQL)
    SET @tableName = null;
    SELECT 
        @tableName = t.name,
        @tableSchema = SCHEMA_NAME(t.schema_id),
        @constraintName = pk.name,
        @constraintSchema = SCHEMA_NAME(t.schema_id)
    FROM sys.objects pk 
    JOIN sys.objects t ON (pk.parent_object_id = t.object_id)
    WHERE pk.[type] = 'PK'
        AND pk.schema_id = SCHEMA_ID(@schema)
    ORDER BY t.name 
END
GO

/* Drop all tables */
DECLARE @schema VARCHAR(128) = '<TODO_PROVIDE_SCHEMA>'
DECLARE @tableName VARCHAR(128)
DECLARE @tableSchema VARCHAR(128)
DECLARE @SQL VARCHAR(254)
SELECT TOP 1 
    @tableName = [name],
    @tableSchema = SCHEMA_NAME(schema_id)
FROM sys.tables
WHERE [type] = 'U' 
    AND schema_id = SCHEMA_ID(@schema)
WHILE @tableName IS NOT NULL
BEGIN
    SELECT @SQL = 'DROP TABLE [' + RTRIM(@tableSchema) +'].[' + RTRIM(@tableName) +']'
    EXEC (@SQL)
    PRINT 'Dropped Table: ' + @tableName
    SET @tableName = NULL;
    SELECT TOP 1 
        @tableName = [name],
        @tableSchema = SCHEMA_NAME(schema_id)
    FROM sys.tables
    WHERE [type] = 'U' 
        AND schema_id = SCHEMA_ID(@schema)
END
GO

/* DROP Sequences */
DECLARE @schema VARCHAR(128) = '<TODO_PROVIDE_SCHEMA>'

DECLARE @seqName VARCHAR(128)
DECLARE @seqSchema VARCHAR(128)
DECLARE @SQL VARCHAR(254)

SELECT TOP 1
    @seqName = s.name,
    @seqSchema = SCHEMA_NAME(s.schema_id)
FROM sys.sequences s
WHERE schema_id = SCHEMA_ID(@schema)
ORDER BY s.name

WHILE @seqName is not null
BEGIN
    SELECT @SQL = 'DROP SEQUENCE [' + RTRIM(@seqSchema) + '].[' + RTRIM(@seqName) +']'
    PRINT 'Executing: ' + @SQL
    EXEC (@SQL)
    SET @seqName = null
    SELECT TOP 1
        @seqName = s.name,
        @seqSchema = SCHEMA_NAME(s.schema_id)
    FROM sys.sequences s
    WHERE schema_id = SCHEMA_ID(@schema)
    ORDER BY s.name
END
GO

在执行之前，不要忘记更改<TODO_PROVIDE_SCHEMA>到您的架构

该脚本将查找与目标架构相关的所有 FK例如，当schema1.table1有 FK 时schema2.table2如果我们想删除schema2那么我的脚本也会删除该 FK（即使这是不同的架构）

脚本还会删除序列

对于遇到这种情况的人。这将删除所有约束，然后是视图，然后是表：

/* Azure friendly */
/* Drop all Foreign Key constraints */
DECLARE @name VARCHAR(128)
DECLARE @constraint VARCHAR(254)
DECLARE @SQL VARCHAR(254)
SELECT @name = (SELECT TOP 1 TABLE_NAME FROM INFORMATION_SCHEMA.TABLE_CONSTRAINTS WHERE constraint_catalog=DB_NAME() AND CONSTRAINT_TYPE = 'FOREIGN KEY' ORDER BY TABLE_NAME)
WHILE @name is not null
BEGIN
    SELECT @constraint = (SELECT TOP 1 CONSTRAINT_NAME FROM INFORMATION_SCHEMA.TABLE_CONSTRAINTS WHERE constraint_catalog=DB_NAME() AND CONSTRAINT_TYPE = 'FOREIGN KEY' AND TABLE_NAME = @name ORDER BY CONSTRAINT_NAME)
    WHILE @constraint IS NOT NULL
    BEGIN
        SELECT @SQL = 'ALTER TABLE [dbo].[' + RTRIM(@name) +'] DROP CONSTRAINT [' + RTRIM(@constraint) +']'
        EXEC (@SQL)
        PRINT 'Dropped FK Constraint: ' + @constraint + ' on ' + @name
        SELECT @constraint = (SELECT TOP 1 CONSTRAINT_NAME FROM INFORMATION_SCHEMA.TABLE_CONSTRAINTS WHERE constraint_catalog=DB_NAME() AND CONSTRAINT_TYPE = 'FOREIGN KEY' AND CONSTRAINT_NAME <> @constraint AND TABLE_NAME = @name ORDER BY CONSTRAINT_NAME)
    END
SELECT @name = (SELECT TOP 1 TABLE_NAME FROM INFORMATION_SCHEMA.TABLE_CONSTRAINTS WHERE constraint_catalog=DB_NAME() AND CONSTRAINT_TYPE = 'FOREIGN KEY' ORDER BY TABLE_NAME)
END
GO
/* Drop all Primary Key constraints */
DECLARE @name VARCHAR(128)
DECLARE @constraint VARCHAR(254)
DECLARE @SQL VARCHAR(254)
SELECT @name = (SELECT TOP 1 TABLE_NAME FROM INFORMATION_SCHEMA.TABLE_CONSTRAINTS WHERE constraint_catalog=DB_NAME() AND CONSTRAINT_TYPE = 'PRIMARY KEY' ORDER BY TABLE_NAME)
WHILE @name IS NOT NULL
BEGIN
    SELECT @constraint = (SELECT TOP 1 CONSTRAINT_NAME FROM INFORMATION_SCHEMA.TABLE_CONSTRAINTS WHERE constraint_catalog=DB_NAME() AND CONSTRAINT_TYPE = 'PRIMARY KEY' AND TABLE_NAME = @name ORDER BY CONSTRAINT_NAME)
    WHILE @constraint is not null
    BEGIN
        SELECT @SQL = 'ALTER TABLE [dbo].[' + RTRIM(@name) +'] DROP CONSTRAINT [' + RTRIM(@constraint)+']'
        EXEC (@SQL)
        PRINT 'Dropped PK Constraint: ' + @constraint + ' on ' + @name
        SELECT @constraint = (SELECT TOP 1 CONSTRAINT_NAME FROM INFORMATION_SCHEMA.TABLE_CONSTRAINTS WHERE constraint_catalog=DB_NAME() AND CONSTRAINT_TYPE = 'PRIMARY KEY' AND CONSTRAINT_NAME <> @constraint AND TABLE_NAME = @name ORDER BY CONSTRAINT_NAME)
    END
SELECT @name = (SELECT TOP 1 TABLE_NAME FROM INFORMATION_SCHEMA.TABLE_CONSTRAINTS WHERE constraint_catalog=DB_NAME() AND CONSTRAINT_TYPE = 'PRIMARY KEY' ORDER BY TABLE_NAME)
END
GO
/* Drop All Views */
while(exists(select 1 from INFORMATION_SCHEMA.VIEWS 
    where TABLE_NAME != 'database_firewall_rules' 
    AND TABLE_NAME NOT IN (select name from sys.external_tables)))
begin
 declare @sql1 nvarchar(2000)
 SELECT TOP 1 @sql1=('DROP VIEW ' + TABLE_SCHEMA + '.[' + TABLE_NAME + ']')
 FROM INFORMATION_SCHEMA.VIEWS
 WHERE TABLE_NAME != 'database_firewall_rules'
    AND TABLE_NAME NOT IN (select name from sys.external_tables)
exec (@sql1)
 PRINT @sql1
end
/* Drop all tables */
DECLARE @name VARCHAR(128)
DECLARE @SQL VARCHAR(254)
SELECT @name = (SELECT TOP 1 [name] FROM sysobjects WHERE [type] = 'U' AND category = 0 ORDER BY [name])
WHILE @name IS NOT NULL
BEGIN
    SELECT @SQL = 'DROP TABLE [dbo].[' + RTRIM(@name) +']'
    EXEC (@SQL)
    PRINT 'Dropped Table: ' + @name
    SELECT @name = (SELECT TOP 1 [name] FROM sysobjects WHERE [type] = 'U' AND category = 0 AND [name] > @name ORDER BY [name])
END
GO

或者，可以在 C# 中删除数据库，然后使用 ExecuteNonQuery 创建一个新数据库。

queryString = "DROP DATABASE TestDB2";
SqlCommand command = new SqlCommand(queryString, connection);
        command.Connection.Open();
        command.ExecuteNonQuery();

其中，连接是与 Azure SQL 数据库主数据库的连接。

或者，要创建数据库：

queryString = "CREATE DATABASE TestDB2 ( EDITION = 'standard' )";
相关文章：