有没有一个脚本可以删除SQL Server数据库中的所有对象？
我目前没有在服务器上删除和创建数据库的权限，所以正在寻找一个脚本，让我回到“原点”。
我在网上看到了一些脚本，但它们没有按正确顺序执行或者没有删除所有东西。
在SQL数据库中删除所有对象？
6
- adolf garlic
6
1请查看以下链接：https://dev59.com/CHRB5IYBdhLWcg3wuZY1 或者 http://blog.falafel.com/blogs/adamanderson/09-01-06/T-SQL_Drop_All_Objects_in_a_SQL_Server_Database.aspx - codingbadger
2很难找到一个能够100%正确排序的程序 - 特别是如果您有使用SCHEMABINDING的视图和函数。 - Damien_The_Unbeliever
Damien是正确的，你需要一个解析器来正确识别外键、计算列、视图、触发器、存储过程和函数中的所有对象名称，以便构建使用正确顺序的脚本。因此，仅使用T-SQL可能不太可能实现；但是，外部解析器（例如我发布的答案中的解析器）可以正确解析和解决所有依赖关系，以创建正确的删除语句并按正确顺序执行。 - Lucero
@Barry，我会测试一下。我想可能已经有人问过这个问题了，但是在翻阅了5页的SO搜索结果后，我什么都没找到。@d_t_u，我明白你说的关于排序的问题。 - adolf garlic
1如果您没有在服务器上删除和重新创建数据库的权限，您不应该执行此任务。将其交给拥有正确权限的人，或者让您的组织为您分配权限。
如果您没有这些权限并且做了这个绕过限制（限制是出于某种原因存在）的操作，我会解雇您。 - HLGEM
我正在使用开发服务器。我是一名开发人员。我试图确保组织的另一部分提供给我的脚本第一次就能正常工作。你对此有什么“变通方法”，或者你更喜欢我将糟糕的脚本发布到下一个阶段吗？（测试、生产） - adolf garlic
4个回答
8
如果您使用模式，您可能会发现以下脚本很有用：
SET NOCOUNT ON;
DECLARE @OnlyInSchema sysname;
-- Set this to a value to only drop objects in one schema.
-- SET @OnlyInSchema = N'';
DECLARE @Commands TABLE (
[Description] NVARCHAR(MAX),
[Line] NVARCHAR(MAX)
);
DECLARE @Drops TABLE (
[Type] NVARCHAR(2),
[Template] NVARCHAR(MAX)
);
-- -- -- -- -- OBJECTS NOT ASSOCIATED WITH TABLES -- -- -- -- --
INSERT INTO @Drops
SELECT N'AF', N'DROP AGGREGATE $S.$O;' UNION
SELECT N'FN', N'DROP FUNCTION $S.$O;' UNION
SELECT N'FS', N'DROP FUNCTION $S.$O;' UNION
SELECT N'FT', N'DROP FUNCTION $S.$O;' UNION
SELECT N'IF', N'DROP FUNCTION $S.$O;' UNION
SELECT N'P', N'DROP PROCEDURE $S.$O;' UNION
SELECT N'SN', N'DROP SYNONYM $S.$O;' UNION
SELECT N'SQ', N'DROP QUEUE $S.$O;' UNION
SELECT N'TR', N'DROP TRIGGER $S.$O;' UNION
SELECT N'TT', N'DROP TYPE $S.$O;' UNION
SELECT N'TF', N'DROP FUNCTION $S.$O;';
INSERT INTO @Commands
SELECT QUOTENAME(RTRIM([S].[name])) + '.' + QUOTENAME(RTRIM([O].[name])),
REPLACE(REPLACE([D].[Template], '$S', QUOTENAME(RTRIM([S].[name]))), '$O', QUOTENAME(RTRIM([O].[name])))
FROM [sys].[objects] AS [O]
INNER JOIN [sys].[schemas] AS [S] ON [O].[schema_id] = [S].[schema_id]
INNER JOIN @Drops AS [D] ON [O].[type] COLLATE Latin1_General_CS_AS = [D].[Type] COLLATE Latin1_General_CS_AS
WHERE (@OnlyInSchema IS NULL OR [S].[name] COLLATE Latin1_General_CS_AS = @OnlyInSchema)
AND [S].[name] COLLATE Latin1_General_CS_AS <> 'sys'
AND [O].[is_ms_shipped] = 0;
-- -- -- -- -- OBJECTS ASSOCIATED WITH TABLES -- -- -- -- --
DELETE FROM @Drops;
INSERT INTO @Drops
SELECT N'C', N'ALTER TABLE $TS.$TO DROP CONSTRAINT $O;' UNION
SELECT N'D', N'ALTER TABLE $TS.$TO DROP CONSTRAINT $O;' UNION
SELECT N'F', N'ALTER TABLE $TS.$TO DROP CONSTRAINT $O;' UNION
SELECT N'PK', N'ALTER TABLE $TS.$TO DROP CONSTRAINT $O;';
INSERT INTO @Commands
SELECT QUOTENAME(RTRIM([S].[name])) + '.' + QUOTENAME(RTRIM([PO].[name])) + '::' + QUOTENAME(RTRIM([O].[name])),
REPLACE(REPLACE(REPLACE([D].[Template], '$TS', QUOTENAME(RTRIM([S].[name]))), '$O', QUOTENAME(RTRIM([O].[name]))), '$TO', QUOTENAME(RTRIM([PO].[name])))
FROM [sys].[objects] AS [O]
INNER JOIN [sys].[objects] AS [PO] ON [O].[parent_object_id] = [PO].[object_id]
INNER JOIN [sys].[schemas] AS [S] ON [PO].[schema_id] = [S].[schema_id]
INNER JOIN @Drops AS [D] ON [O].[type] COLLATE Latin1_General_CS_AS = [D].[Type] COLLATE Latin1_General_CS_AS
WHERE (@OnlyInSchema IS NULL OR [S].[name] COLLATE Latin1_General_CS_AS = @OnlyInSchema)
AND [S].[name] COLLATE Latin1_General_CS_AS <> 'sys'
AND [O].[is_ms_shipped] = 0;
-- -- -- -- -- ACTUAL DROP -- -- -- -- --
DELETE FROM @Drops;
INSERT INTO @Drops
SELECT N'U', N'DROP TABLE $S.$O;' UNION
SELECT N'V', N'DROP TABLE $S.$O;';
INSERT INTO @Commands
SELECT QUOTENAME(RTRIM([S].[name])) + '.' + QUOTENAME(RTRIM([O].[name])),
REPLACE(REPLACE([D].[Template], '$S', QUOTENAME(RTRIM([S].[name]))), '$O', QUOTENAME(RTRIM([O].[name])))
FROM [sys].[objects] AS [O]
INNER JOIN [sys].[schemas] AS [S] ON [O].[schema_id] = [S].[schema_id]
INNER JOIN @Drops AS [D] ON [O].[type] COLLATE Latin1_General_CS_AS = [D].[Type] COLLATE Latin1_General_CS_AS
WHERE (@OnlyInSchema IS NULL OR [S].[name] COLLATE Latin1_General_CS_AS = @OnlyInSchema)
AND [S].[name] COLLATE Latin1_General_CS_AS <> 'sys'
AND [O].[is_ms_shipped] = 0;
-- -- -- -- -- TABLES -- -- -- -- --
DECLARE @Description NVARCHAR(MAX);
DECLARE @Message NVARCHAR(MAX);
DECLARE @Command NVARCHAR(MAX);
DECLARE CommandCursor CURSOR FOR
SELECT [Description], [Line] FROM @Commands;
OPEN CommandCursor;
FETCH NEXT FROM CommandCursor INTO @Description, @Command;
WHILE @@FETCH_STATUS = 0
BEGIN
SET @Message = N'Dropping ' + @Description + '...';
PRINT @Message;
BEGIN TRY
EXEC sp_executesql @Command;
END TRY
BEGIN CATCH
SET @Message = N'Failed to drop ' + @Description + ':';
PRINT @Message;
PRINT ERROR_MESSAGE()
END CATCH
FETCH NEXT FROM CommandCursor INTO @Description, @Command;
END
CLOSE CommandCursor;
DEALLOCATE CommandCursor;
- Jonathan Dickinson
2
谢谢你！但是，即使我在你的查询中看到“V”，视图似乎并没有被删除。你知道怎么解决吗？ - electrotype
@electrotype 只需在联合块的“未与表相关的对象”部分添加
SELECT N'V', N'DROP VIEW $S.$O;' UNION，它就会为您删除视图。 - KuriosCurious3
我在 "http://kashyapmakadia.blogspot.com/2009/08/drop-everything-in-sql-server-2005.html" 上找到了这段代码，尝试了一下，结果非常好！你可以在那里找到代码，但如果你很匆忙，代码如下：
/* Drop all non-system stored procs */
DECLARE @name VARCHAR(128)
DECLARE @SQL VARCHAR(254)
SELECT @name = (SELECT TOP 1 [name] FROM sysobjects WHERE [type] = 'P' AND category = 0 ORDER BY [name])
WHILE @name is not null
BEGIN
SELECT @SQL = 'DROP PROCEDURE [' + RTRIM(@name) +']'
EXEC (@SQL)
PRINT 'Dropped Procedure: ' + @name
SELECT @name = (SELECT TOP 1 [name] FROM sysobjects WHERE [type] = 'P' AND category = 0 AND [name] > @name ORDER BY [name])
END
GO
/* Drop all views */
DECLARE @name VARCHAR(128)
DECLARE @SQL VARCHAR(254)
SELECT @name = (SELECT TOP 1 [name] FROM sysobjects WHERE [type] = 'V' AND category = 0 ORDER BY [name])
WHILE @name IS NOT NULL
BEGIN
SELECT @SQL = 'DROP VIEW [' + RTRIM(@name) +']'
EXEC (@SQL)
PRINT 'Dropped View: ' + @name
SELECT @name = (SELECT TOP 1 [name] FROM sysobjects WHERE [type] = 'V' AND category = 0 AND [name] > @name ORDER BY [name])
END
GO
/* Drop all functions */
DECLARE @name VARCHAR(128)
DECLARE @SQL VARCHAR(254)
SELECT @name = (SELECT TOP 1 [name] FROM sysobjects WHERE [type] IN (N'FN', N'IF', N'TF', N'FS', N'FT') AND category = 0 ORDER BY [name])
WHILE @name IS NOT NULL
BEGIN
SELECT @SQL = 'DROP FUNCTION [' + RTRIM(@name) +']'
EXEC (@SQL)
PRINT 'Dropped Function: ' + @name
SELECT @name = (SELECT TOP 1 [name] FROM sysobjects WHERE [type] IN (N'FN', N'IF', N'TF', N'FS', N'FT') AND category = 0 AND [name] > @name ORDER BY [name])
END
GO
/* 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 [' + 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 [' + 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 [' + 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
- MA9H
2
在Azure上对我很有效。正是我现在所需要的。谢谢。 - nealkernohan
在使用此代码时请注意SQL服务器> 2005。2005年改变了关于模式的概念，此代码不适用于不在dbo模式中的对象。如果运行它，它会陷入循环。 - Mark Iannucci
0
您可以尝试使用开源的bsn ModuleStore版本控制工具包，它可以创建数据库清单并为数据库模式创建一个DROP脚本，以尊重对象的依赖关系。目前它支持表、索引、触发器、视图、函数、存储过程和XML模式集合。
using (ManagementConnectionProvider provider = new ManagementConnectionProvider(connection, "dbo")) {
DatabaseInventory inventory = new DatabaseInventory(provider, "dbo");
string dropScript = string.Join(";\r\nGO\r\n\r\n", inventory.GenerateUninstallSql().ToArray());
}
- Lucero
-4
Select * From sysobjects Where....
然后一个一个地删除。
- pinichi
5
如果你有依赖关系，比如外键、绑定到模式的函数、计算列等，则会导致失败。 - Lucero
1不确定，但可能会有一些选项允许级联或类似操作吗？ - pinichi
1级联选项仅适用于由外键引用的数据（在 SQL Server 上，即使对于数据，它也非常有限 - 但这是另一个话题）。 - Lucero
我想运行一个脚本而不必手动删除东西。我倒不如坐在那里点击 SSMS... - adolf garlic
1如果您发布代码或XML，请在文本编辑器中突出显示这些行，并单击编辑器工具栏上的“代码”按钮（101 010）以使其格式化和语法高亮！ - marc_s
网页内容由stack overflow 提供, 点击上面的可以查看英文原文，
原文链接
原文链接