Drop all objects in SQL DB?

Question

Does anyone have a script that will drop all objects in a SQL Server DB?

I currently don't have rights to drop and create DBs on the server so am looking for a script to take me back to 'square one'

I've seen some on the web, but they don't do things in the right order or they don't remove everything.

Check out this link http://stackoverflow.com/q/536350/300863 or this one http://blog.falafel.com/blogs/adamanderson/09-01-06/T-SQL_Drop_All_Objects_in_a_SQL_Server_Database.aspx — codingbadger, Dec 07 '10 at 08:09
It's pretty difficult to get one that will get the order right 100% of the time - especially if you have views and functions that use SCHEMABINDING. — Damien_The_Unbeliever, Dec 07 '10 at 08:11
Damien is right, you need a parser which correctly identifies all object names in foreign keys, computed columns, views, triggers, stored procedures and functions in order to build a script which uses the correct order. Therefore it is unlikely to be possible using T-SQL only; but an external parser (such as in the answer I posted) can correctly parse and resolve all dependencies in order to create the correct drop statements in the right order. — Lucero, Dec 07 '10 at 08:32
@Barry, I will test it out. I figured that someone had probably asked this before, but after wading through 5 pages of SO search results I came up with nothing. @d_t_u, point taken about the ordering — adolf garlic, Dec 07 '10 at 08:41
If you don't have rights to drop and recreate databases on the server, you should not be doing this task. Give it to someone who does have the correct rights or have your organization assign them to you. If you didn't have those rights on my servers and you did this to get around the restriction (which is there for a reason), I'd fire you. — HLGEM, Dec 07 '10 at 22:15
I am on about a development server. I am a developer. I am trying to make sure that the scripts I am given by another part of the organisation work first time. What is your 'workaround' for this, or would you prefer that I release crappy scripts to the next stages? (testing, production) — adolf garlic, Dec 08 '10 at 08:26

score 8 · Answer 1 · answered Feb 11 '14 at 13:46

If you use schemas you may find the following script useful:

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;

Thanks for that! But the views don't seem to be dropped, even if I see "V" in your query. Do you know how to fix that? — electrotype, Mar 21 '18 at 23:06
@electrotype just add `SELECT N'V', N'DROP VIEW $S.$O;' UNION` to the union block section "Objects not associated with tables" and it will drop the views for you. — KuriosCurious, Apr 08 '19 at 14:06

score 3 · Answer 2 · edited May 23 '13 at 22:04

I found this code on "http://kashyapmakadia.blogspot.com/2009/08/drop-everything-in-sql-server-2005.html" and tried it and it worked just find!! You can find the code there, but if you're in hurry, the code is:

    /* 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

Worked well for me on Azure. Exactly what I needed just now. Thank you. — nealkernohan, Jan 20 '14 at 09:20
careful when using this on SQL servers > 2005. 2005 changed the concept surrounding schemas and this code doesn't work for objects which are not in the dbo schema. It gets stuck in a loop if you run it. — Mark Iannucci, Aug 19 '15 at 15:05

Lucero · Answer 3 · 2010-12-07T08:18:44.340

You can try the open-source bsn ModuleStore versioning toolkit, which can create an inventory of a database and create a DROP script for a database schema which respects the dependencies of the objects. It currently supports tables, indexes, triggers, views, functions, stored procedures and XML schema collections.

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());
}

score -4 · Answer 4 · edited Dec 07 '10 at 08:52

-4

Select * From sysobjects Where....

Then drop one by one.

edited Dec 07 '10 at 08:52

marc_s

732,580
175
1,330
1,459

answered Dec 07 '10 at 08:13

pinichi

2,199
15
17

That will fail if you have dependencies, such as foreign keys, schemabound functions, computed columns etc. – Lucero Dec 07 '10 at 08:18
1

dont sure but would have some option allow cascade or similar? – pinichi Dec 07 '10 at 08:19
1

The cascade option is for data referenced by foreign keys only (and even for data it is pretty limited on SQL Server - but that's another topic). – Lucero Dec 07 '10 at 08:23
I want to run one script not have to go through and manually delete stuff. I may as well sit and click on SSMS... – adolf garlic Dec 07 '10 at 08:38
1

If you post code or XML, **please** highlight those lines in the text editor and click on the "code" button (101 010) on the editor toolbar to nicely format and syntax highlight it! – marc_s Dec 07 '10 at 08:52

Drop all objects in SQL DB?

4 Answers4