已接受的答案不支持Azure。它使用一个无文档的存储过程“sp_MSforeachtable”。如果您在运行时遇到“azure无法找到存储过程sp_msforeachtable”错误，或者只是想避免依赖未记录的特性(可以在任何时候删除或更改其功能)，那么请尝试下面的方法。

这个版本忽略了实体框架迁移历史表“__MigrationHistory”和“database_firewall_rules”，这是一个你没有权限删除的Azure表。

在Azure上进行了简单测试。一定要确保这对你的环境没有不良影响。

DECLARE @sql NVARCHAR(2000)

WHILE(EXISTS(SELECT 1 from INFORMATION_SCHEMA.TABLE_CONSTRAINTS WHERE CONSTRAINT_TYPE='FOREIGN KEY'))
BEGIN
    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 * from INFORMATION_SCHEMA.TABLES WHERE TABLE_NAME != '__MigrationHistory' AND TABLE_NAME != 'database_firewall_rules'))
BEGIN
    SELECT TOP 1 @sql=('DROP TABLE ' + TABLE_SCHEMA + '.[' + TABLE_NAME + ']')
    FROM INFORMATION_SCHEMA.TABLES
    WHERE TABLE_NAME != '__MigrationHistory' AND TABLE_NAME != 'database_firewall_rules'
    EXEC(@sql)
    PRINT @sql
END

来自:

https://edspencer.me.uk/2013/02/25/drop-all-tables-in-a-sql-server-database-azure-friendly/

http://www.sqlservercentral.com/blogs/sqlservertips/2011/10/11/remove-all-foreign-keys/

Delete用于从表中删除行。你应该用drop table代替。

EXEC sp_msforeachtable 'drop table [?]'

已接受的答案不支持Azure。它使用一个无文档的存储过程“sp_MSforeachtable”。如果您在运行时遇到“azure无法找到存储过程sp_msforeachtable”错误，或者只是想避免依赖未记录的特性(可以在任何时候删除或更改其功能)，那么请尝试下面的方法。

这个版本忽略了实体框架迁移历史表“__MigrationHistory”和“database_firewall_rules”，这是一个你没有权限删除的Azure表。

在Azure上进行了简单测试。一定要确保这对你的环境没有不良影响。

DECLARE @sql NVARCHAR(2000)

WHILE(EXISTS(SELECT 1 from INFORMATION_SCHEMA.TABLE_CONSTRAINTS WHERE CONSTRAINT_TYPE='FOREIGN KEY'))
BEGIN
    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 * from INFORMATION_SCHEMA.TABLES WHERE TABLE_NAME != '__MigrationHistory' AND TABLE_NAME != 'database_firewall_rules'))
BEGIN
    SELECT TOP 1 @sql=('DROP TABLE ' + TABLE_SCHEMA + '.[' + TABLE_NAME + ']')
    FROM INFORMATION_SCHEMA.TABLES
    WHERE TABLE_NAME != '__MigrationHistory' AND TABLE_NAME != 'database_firewall_rules'
    EXEC(@sql)
    PRINT @sql
END

来自:

https://edspencer.me.uk/2013/02/25/drop-all-tables-in-a-sql-server-database-azure-friendly/

http://www.sqlservercentral.com/blogs/sqlservertips/2011/10/11/remove-all-foreign-keys/

sp_msforeachtable在Azure SQL中不可用

对于Azure SQL:

此查询将删除外键约束

DECLARE @Name VARCHAR(200)
DECLARE @Constraint VARCHAR(300)
DECLARE @SQL VARCHAR(300)

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

这将从数据库中删除所有表

DECLARE @Name VARCHAR(200)
DECLARE @SQL VARCHAR(300)

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) +']' /*here you can change schema if it is different from dbo*/
    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

对我来说，最简单的方法是:

--First delete all constraints

DECLARE @sql NVARCHAR(MAX);
SET @sql = N'';

SELECT @sql = @sql + N'
ALTER TABLE ' + QUOTENAME(s.name) + N'.'
+ QUOTENAME(t.name) + N' DROP CONSTRAINT '
+ QUOTENAME(c.name) + ';'
FROM sys.objects AS c
INNER JOIN sys.tables AS t
ON c.parent_object_id = t.[object_id]
INNER JOIN sys.schemas AS s 
ON t.[schema_id] = s.[schema_id]
WHERE c.[type] IN ('D','C','F','PK','UQ')
ORDER BY c.[type];

EXEC sys.sp_executesql @sql;

-- Then drop all tables

exec sp_MSforeachtable 'DROP TABLE ?'

简短而甜蜜:

USE YOUR_DATABASE_NAME
-- Disable all referential integrity constraints
EXEC sp_MSforeachtable 'ALTER TABLE ? NOCHECK CONSTRAINT ALL'
GO

-- Drop all PKs and FKs
declare @sql nvarchar(max)
SELECT @sql = STUFF((SELECT '; ' + 'ALTER TABLE ' + Table_Name  +'  drop constraint ' + Constraint_Name  from Information_Schema.CONSTRAINT_TABLE_USAGE ORDER BY Constraint_Name FOR XML PATH('')),1,1,'')
EXECUTE (@sql)
GO

-- Drop all tables
EXEC sp_MSforeachtable 'DROP TABLE ?'
GO