我正在尝试编写一个脚本,以完全清空SQL Server数据库。目前我有以下代码:
USE [dbname]
GO
EXEC sp_msforeachtable 'ALTER TABLE ? NOCHECK CONSTRAINT all'
EXEC sp_msforeachtable 'DELETE ?'
在 Management Studio 中运行时,我得到:
命令已成功完成。
但是当我刷新表列表时,它们仍然都在那里。我做错了什么吗?
我正在尝试编写一个脚本,以完全清空SQL Server数据库。目前我有以下代码:
USE [dbname]
GO
EXEC sp_msforeachtable 'ALTER TABLE ? NOCHECK CONSTRAINT all'
EXEC sp_msforeachtable 'DELETE ?'
在 Management Studio 中运行时,我得到:
命令已成功完成。
但是当我刷新表列表时,它们仍然都在那里。我做错了什么吗?
看起来命令应该没有方括号
EXEC sp_msforeachtable 'drop table ?'
对我而言,最简单的方法是:
--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 ?'
Azure SQL +表(带约束条件)位于不同于dbo的模式中+ ipv6_database_firewall_rules条件。
这是对https://dev59.com/1Woy5IYBdhLWcg3wnPOJ#43128914答案的一个小扩展。
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 ' + CONSTRAINT_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' AND TABLE_NAME != 'ipv6_database_firewall_rules'))
BEGIN
SELECT TOP 1 @sql=('DROP TABLE ' + CONSTRAINT_SCHEMA + '.[' + TABLE_NAME + ']')
FROM INFORMATION_SCHEMA.TABLE_CONSTRAINTS
WHERE TABLE_NAME != '__MigrationHistory' AND TABLE_NAME != 'database_firewall_rules'
EXEC(@sql)
PRINT @sql
END
试试删掉整个数据库,然后再重新创建它?这对我来说有效。
DROP DATABASE mydb;
CREATE DATABASE mydb;
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
对于时间表,由于可能存在一些外键和异常情况,所以稍微有些复杂:
删除表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
最终,我想出了这个方法。我在所有地方进行了测试(一般而言),它可以工作(没有任何隐藏的存储过程)。
大多数情况下适用于 SQL Server 2014。(但我尝试过的其他版本似乎也可以正常工作)。
我尝试过 while 循环、null 等等,游标和其他各种形式,但它们总是在某些数据库上失败,而在其他数据库上却没有明显的原因。
获取计数并使用其进行迭代似乎在我测试的所有内容中都有效。
USE [****YOUR_DATABASE****]
GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
-- Drop all referential integrity constraints --
-- Drop all Primary Key constraints. --
DECLARE @sql NVARCHAR(296)
DECLARE @table_name VARCHAR(128)
DECLARE @constraint_name VARCHAR(128)
SET @constraint_name = ''
DECLARE @row_number INT
SELECT @row_number = Count(*) FROM INFORMATION_SCHEMA.REFERENTIAL_CONSTRAINTS rc1
LEFT JOIN INFORMATION_SCHEMA.TABLE_CONSTRAINTS tc2 ON tc2.CONSTRAINT_NAME = rc1.CONSTRAINT_NAME
WHILE @row_number > 0
BEGIN
BEGIN
SELECT TOP 1 @table_name = tc2.TABLE_NAME, @constraint_name = rc1.CONSTRAINT_NAME FROM INFORMATION_SCHEMA.REFERENTIAL_CONSTRAINTS rc1
LEFT JOIN INFORMATION_SCHEMA.TABLE_CONSTRAINTS tc2 ON tc2.CONSTRAINT_NAME = rc1.CONSTRAINT_NAME
AND rc1.CONSTRAINT_NAME > @constraint_name
ORDER BY rc1.CONSTRAINT_NAME
SELECT @sql = 'ALTER TABLE [dbo].[' + RTRIM(@table_name) +'] DROP CONSTRAINT [' + RTRIM(@constraint_name)+']'
EXEC (@sql)
PRINT 'Dropped Constraint: ' + @constraint_name + ' on ' + @table_name
SET @row_number = @row_number - 1
END
END
GO
-- Drop all tables --
DECLARE @sql NVARCHAR(156)
DECLARE @name VARCHAR(128)
SET @name = ''
DECLARE @row_number INT
SELECT @row_number = Count(*) FROM sysobjects WHERE [type] = 'U' AND category = 0
WHILE @row_number > 0
BEGIN
SELECT @name = (SELECT TOP 1 [name] FROM sysobjects WHERE [type] = 'U' AND category = 0 AND [name] > @name ORDER BY [name])
SELECT @sql = 'DROP TABLE [dbo].[' + RTRIM(@name) +']'
EXEC (@sql)
PRINT 'Dropped Table: ' + @name
SET @row_number = @row_number - 1
END
GO