对于时态表来说，由于可能会有一些外键和例外，这有点复杂:

在表XXX上执行删除表操作失败，因为在系统版本的时态表上不支持该操作

你可以使用的是:

-- Disable constraints (foreign keys)
EXEC sp_MSForEachTable 'ALTER TABLE ? NOCHECK CONSTRAINT ALL'
GO

-- Disable system versioning (temporial tables)
EXEC sp_MSForEachTable '
 IF OBJECTPROPERTY(object_id(''?''), ''TableTemporalType'') = 2
  ALTER TABLE ? SET (SYSTEM_VERSIONING = OFF)
'
GO

-- Removing tables
EXEC sp_MSForEachTable 'DROP TABLE ?'
GO

简短而甜蜜:

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

看来这个命令应该不带方巾

EXEC sp_msforeachtable 'drop table ?'

如果删除整个数据库，然后重新创建它呢?这对我很有用。

DROP DATABASE mydb;
CREATE DATABASE mydb;

对于时态表来说，由于可能会有一些外键和例外，这有点复杂:

在表XXX上执行删除表操作失败，因为在系统版本的时态表上不支持该操作

你可以使用的是:

-- Disable constraints (foreign keys)
EXEC sp_MSForEachTable 'ALTER TABLE ? NOCHECK CONSTRAINT ALL'
GO

-- Disable system versioning (temporial tables)
EXEC sp_MSForEachTable '
 IF OBJECTPROPERTY(object_id(''?''), ''TableTemporalType'') = 2
  ALTER TABLE ? SET (SYSTEM_VERSIONING = OFF)
'
GO

-- Removing tables
EXEC sp_MSForEachTable 'DROP TABLE ?'
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 ?'