我试图写一个脚本,将完全清空SQL Server数据库。这是我目前所拥有的:
USE [dbname]
GO
EXEC sp_msforeachtable 'ALTER TABLE ? NOCHECK CONSTRAINT all'
EXEC sp_msforeachtable 'DELETE ?'
当我在管理工作室运行它时,我得到:
命令执行成功。
但当我刷新表列表时,它们都还在那里。我做错了什么?
我试图写一个脚本,将完全清空SQL Server数据库。这是我目前所拥有的:
USE [dbname]
GO
EXEC sp_msforeachtable 'ALTER TABLE ? NOCHECK CONSTRAINT all'
EXEC sp_msforeachtable 'DELETE ?'
当我在管理工作室运行它时,我得到:
命令执行成功。
但当我刷新表列表时,它们都还在那里。我做错了什么?
当前回答
简短而甜蜜:
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 @command1 = "DROP TABLE ?"
编码愉快!
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
/* 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
你差不多说对了,可以用:
EXEC sp_msforeachtable 'ALTER TABLE ? NOCHECK CONSTRAINT all'
EXEC sp_msforeachtable 'DROP TABLE ?'
但是第二行你可能需要执行不止一次,直到你停止得到错误:
Could not drop object 'dbo.table' because it is referenced by a FOREIGN KEY constraint.
信息:
Command(s) completed successfully.
表示已成功删除所有表。
在地对地导弹:
右键单击数据库 转到“任务” 点击“生成脚本” 在“选择对象”部分,选择“脚本整个数据库和所有数据库对象” 在“设置脚本选项”部分,单击“高级”按钮 在“脚本删除和创建”上切换“脚本创建”到“脚本删除”并按“确定” 然后,保存到文件,剪贴板,或新的查询窗口。 运行脚本。
现在,这将删除所有内容,包括数据库。确保删除您不希望删除的项目的代码。或者,在“选择对象”部分,而不是选择脚本整个数据库,只是选择你想要删除的项目。