Drop all the tables, stored procedures, triggers, constraints and all the dependencies in one sql statement

Question

Is there any way in which I can clean a database in SQl Server 2005 by dropping all the tables and deleting stored procedures, triggers, constraints and all the dependencies in one SQL statement?

REASON FOR REQUEST:

I want to have a DB script for cleaning up an existing DB which is not in use rather than creating new ones, especially when you have to put in a request to your DB admin and wait for a while to get it done!

score 642 · Accepted Answer · edited Jul 26 '11 at 20:43

this script cleans all views, SPS, functions PKs, FKs and tables.

/* 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 [dbo].[' + 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 [dbo].[' + 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 [dbo].[' + 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 [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

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

This script definitively does not take other schemas (other than dbo) into account. I tested it and it runs forever. I imagine it could be tweaked for this scenario. — W3Max, Oct 08 '11 at 01:00
This script works quite well. Missing a few things, You need to account for assemblies and User Defined Types. So as an example in your Drop Procedure do [type] = 'P' OR [type] = 'PC' also when you drop the assembly do a order by assembly_ID desc so that dependancies are deleted in the correct order. — Brian, Feb 15 '15 at 17:55
It seems unnecessary to drop all primary keys separately; dropping the tables should take care of that — erikkallen, Jun 16 '16 at 14:36
@erikkallen the reason the Primary Keys must be dropped first is so that the tables can then be dropped in any order without concern for Foreign Key dependencies. Without that, a complex drop order would need to be used to ensure all the weak entity types are dropped prior to the strong entity types, otherwise the script would fail with referential integrity constraint violations. — Jeff Puckett, Oct 07 '16 at 16:55
This is still accurate answer in 2017! Thanks for sharing your script :) — Gaurav Daga, Nov 04 '17 at 12:36
Btw, I would remove the anti-pattern of having 2 copies of the iteration code for each loop. a) change to "WHILE (1=1)", b) delete iteration code copy before loop, c) move the iteration code copy at the end of the loop to the beginning, d) initialize the iterator variable (i.e. "@name") to a non-NULL min value (i.e. "") (so it can be compared against before the 1st iteration completes) and e) add "IF (iterator = termination value) BREAK;" after the iteration code. — Tom, Nov 30 '17 at 18:39
I would drop functions after tables because they can be depended upon by columns with default values referencing them. `Cannot DROP FUNCTION 'fun_foo' because it is being referenced by object 'table_bar'.` — Jeff Puckett, Dec 10 '17 at 04:17
I added some initial code that requires a declared run datetime. If the time is not within 10 minutes of the specified datetime a 'set noexec on' is applied to prevent deletions. Also added messages and a 'set noexec off' at end of code. Feels safer. — jim birch, Dec 12 '19 at 01:39

score 132 · Answer 2 · edited Dec 09 '19 at 02:21

I'm using this script by Adam Anderson, updated to support objects in other schemas than dbo.

declare @n char(1)
set @n = char(10)

declare @stmt nvarchar(max)

-- procedures
select @stmt = isnull( @stmt + @n, '' ) +
    'drop procedure [' + schema_name(schema_id) + '].[' + name + ']'
from sys.procedures


-- check constraints
select @stmt = isnull( @stmt + @n, '' ) +
'alter table [' + schema_name(schema_id) + '].[' + object_name( parent_object_id ) + ']    drop constraint [' + name + ']'
from sys.check_constraints

-- functions
select @stmt = isnull( @stmt + @n, '' ) +
    'drop function [' + schema_name(schema_id) + '].[' + name + ']'
from sys.objects
where type in ( 'FN', 'IF', 'TF' )

-- views
select @stmt = isnull( @stmt + @n, '' ) +
    'drop view [' + schema_name(schema_id) + '].[' + name + ']'
from sys.views

-- foreign keys
select @stmt = isnull( @stmt + @n, '' ) +
    'alter table [' + schema_name(schema_id) + '].[' + object_name( parent_object_id ) + '] drop constraint [' + name + ']'
from sys.foreign_keys

-- tables
select @stmt = isnull( @stmt + @n, '' ) +
    'drop table [' + schema_name(schema_id) + '].[' + name + ']'
from sys.tables

-- user defined types
select @stmt = isnull( @stmt + @n, '' ) +
    'drop type [' + schema_name(schema_id) + '].[' + name + ']'
from sys.types
where is_user_defined = 1


exec sp_executesql @stmt

Source: an Adam Anderson blog post

almost perfect, but there is one crazy/stupid edge case: a table with a calculated column using a function. But just move the drop functions part, and you are good to go! — Akos Lukacs, Nov 23 '15 at 13:14
works great, it will try and drop the view sys.database_firewall_rules if you run this against an Azure PAAS SQL Database but as azure does prevent it from dropping. — Simon, Aug 26 '17 at 09:51
I added this after "from sys.views" to address the Azure SQL issue: WHERE schema_name(schema_id) != 'sys' — UnionP, Sep 24 '18 at 22:23
You missed this: `-- system-versioned tables SELECT @stmt = isnull(@stmt + CHAR(10), '') + 'alter table [' + schema_name(schema_id) + '].[' + name + '] SET ( SYSTEM_VERSIONING = OFF)' FROM sys.tables WHERE TEMPORAL_TYPE = 2;` — Christopher Shortt, Mar 02 '20 at 08:49

score 118 · Answer 3 · edited Jun 20 '20 at 09:12

118

The best thing to do it is "Generate scripts for Drop"

Select Database -> Right Click -> Tasks -> Generate Scripts - will open wizard for generating scripts

after choosing objects in set Scripting option click Advanced Button

-> Set option 'Script to create' to true (want to create)
-> Set option 'Script to Drop' to true (want to drop)
-> Select the Check box to select objects wish to create script
-> Select the choice to write script (File, New window, Clipboard)
It includes dependent objects by default.(and will drop constraint at first)

Execute the script

This way we can customize our script.

edited Jun 20 '20 at 09:12

Community

1
1

answered Apr 22 '11 at 19:01

JP Emvia

1,724
1
12
6

In which version is this feature available ? In 2012 I do not see it. – Veverke Feb 08 '16 at 12:36
1

It is there in SQL Studio 2012. – Bernhard Döbler Feb 17 '16 at 17:42
1

yes,in 2012 when little different when we choose *Script to Drop* there *Script to Drop* and *Script to Create* in a same dropdown. – Sk Asraf Aug 11 '16 at 21:50
2

Does this include sequences? – Markus Pscheidt Feb 18 '17 at 22:39
This is the only thing that works in an enterprise level application. Don't waste your time on any of the other scripts. – bvdb Aug 23 '21 at 06:22
@MarkusPscheidt nope. you need to delete it in another way. – QkiZ Mar 06 '22 at 10:13
Worked for me. The steps slightly different in SQL Server Management Studio v18. – blogofsongs Mar 24 '22 at 10:45

score 52 · Answer 4 · answered Feb 11 '09 at 11:22

52

To drop all tables:

exec sp_MSforeachtable 'DROP TABLE ?'

This will, of course, drop all constraints, triggers etc., everything but the stored procedures.

For the stored procedures I'm afraid you will need another stored procedure stored in master.

answered Feb 11 '09 at 11:22

Quassnoi

413,100
91
616
614

I don't know if sp_MSforeachtable will work here. It may try to drop a table that still has FKs to it and that would fail. – Tom H Feb 11 '09 at 13:47
8

Works great yeah. If foreign keys are present, you just need to execute it a couple of times. – Mathias Lykkegaard Lorenzen Oct 01 '12 at 16:11
Could not drop object 'dbo.sometable' because it is referenced by a FOREIGN KEY constraint. – Oct 30 '15 at 17:54
check out [this](http://www.dotnet-tricks.com/Tutorial/sqlserver/T685270912-Drop-all-tables,-stored-procedure,-views-and-triggers.html) article for deleting `stored procedures` etc. – Shaiju T Dec 05 '15 at 10:43
just for reference, this will not work for Azure PAAS SQL databases. – Simon Aug 26 '17 at 09:47
Not sure it works for every occasion but this `while` did the trick for me on MSSQL at least: ```sql USE GO WHILE ((SELECT COUNT(*) FROM sys.tables) > 0) BEGIN exec sp_MSforeachtable 'DROP TABLE ?' END ``` – Uli Aug 29 '23 at 12:03

score 15 · Answer 5 · edited Sep 25 '09 at 11:21

15

I'd do it in two statements: DROP DATABASE ???

and then CREATE DATABASE ???

edited Sep 25 '09 at 11:21

Adam Batkin

51,711
9
123
115

answered Feb 11 '09 at 11:21

Xn0vv3r

17,766
13
58
65

8

If you don't have these rights and you can't give the script to someone who does have the rights to run, you shouldn't be contemplating doing this. There is a reason developers don't get these rights. – HLGEM Feb 11 '09 at 15:52
@HLGEM Some dba's don't know how to set up rights. My website host doesn't allow me to drop my own database. I think you need special elevated rights to drop databases which might allow you to drop other users databases which is a big no no and security risk. – Tony_Henrich Aug 21 '14 at 02:19
1

In case of AWS RDS, recreating db takes at least 15-20 minutes and don't want to recreate db – Kamran Qadir May 18 '17 at 06:21
1

I assume, ??? needs to be replaced with real DB name? Also, are other settings preserved, like recovery model and file location? – Andreas Reiff Feb 23 '21 at 18:09

David Roussel · Answer 6 · 2015-04-29T11:45:26.380

I tried some of the script here, but they didn't work for me, as I have my tables in schemas. So I put together the following. Note that this script takes a list of schemas, and drops then in sequence. You need to make sure that you have a complete ordering in your schemas. If there are any circular dependencies, then it will fail.

PRINT 'Dropping whole database'
GO

------------------------------------------
-- Drop constraints
------------------------------------------
DECLARE @Sql NVARCHAR(500) DECLARE @Cursor CURSOR

SET @Cursor = CURSOR FAST_FORWARD FOR
SELECT DISTINCT sql = 'ALTER TABLE ['+tc2.CONSTRAINT_SCHEMA+'].[' + tc2.TABLE_NAME + '] DROP [' + rc1.CONSTRAINT_NAME + ']'
FROM INFORMATION_SCHEMA.REFERENTIAL_CONSTRAINTS rc1
LEFT JOIN INFORMATION_SCHEMA.TABLE_CONSTRAINTS tc2 ON tc2.CONSTRAINT_NAME =rc1.CONSTRAINT_NAME

OPEN @Cursor FETCH NEXT FROM @Cursor INTO @Sql

WHILE (@@FETCH_STATUS = 0)
BEGIN
PRINT @Sql
Exec (@Sql)
FETCH NEXT FROM @Cursor INTO @Sql
END

CLOSE @Cursor DEALLOCATE @Cursor
GO


------------------------------------------
-- Drop views
------------------------------------------

DECLARE @sql VARCHAR(MAX) = ''
        , @crlf VARCHAR(2) = CHAR(13) + CHAR(10) ;

SELECT @sql = @sql + 'DROP VIEW ' + QUOTENAME(SCHEMA_NAME(schema_id)) + '.' + QUOTENAME(v.name) +';' + @crlf
FROM   sys.views v

PRINT @sql;
EXEC(@sql);
GO
------------------------------------------
-- Drop procs
------------------------------------------
PRINT 'Dropping all procs ...'
GO

DECLARE @sql VARCHAR(MAX) = ''
        , @crlf VARCHAR(2) = CHAR(13) + CHAR(10) ;

SELECT @sql = @sql + 'DROP PROC ' + QUOTENAME(SCHEMA_NAME(p.schema_id)) + '.' + QUOTENAME(p.name) +';' + @crlf
FROM   [sys].[procedures] p

PRINT @sql;
EXEC(@sql);
GO

------------------------------------------
-- Drop tables
------------------------------------------
PRINT 'Dropping all tables ...'
GO
EXEC sp_MSForEachTable 'DROP TABLE ?'
GO

------------------------------------------
-- Drop sequences
------------------------------------------

PRINT 'Dropping all sequences ...'
GO
DECLARE @DropSeqSql varchar(1024)
DECLARE DropSeqCursor CURSOR FOR
SELECT DISTINCT 'DROP SEQUENCE ' + s.SEQUENCE_SCHEMA + '.' + s.SEQUENCE_NAME
    FROM INFORMATION_SCHEMA.SEQUENCES s

OPEN DropSeqCursor

FETCH NEXT FROM DropSeqCursor INTO @DropSeqSql

WHILE ( @@FETCH_STATUS <> -1 )
BEGIN
    PRINT @DropSeqSql
    EXECUTE( @DropSeqSql )
    FETCH NEXT FROM DropSeqCursor INTO @DropSeqSql
END

CLOSE DropSeqCursor
DEALLOCATE DropSeqCursor
GO

------------------------------------------
-- Drop Schemas
------------------------------------------


DECLARE @schemas as varchar(1000) = 'StaticData,Ird,DataImport,Collateral,Report,Cds,CommonTrade,MarketData,TypeCode'
DECLARE @schemasXml as xml = cast(('<schema>'+replace(@schemas,',' ,'</schema><schema>')+'</schema>') as xml)

DECLARE @Sql NVARCHAR(500) DECLARE @Cursor CURSOR

SET @Cursor = CURSOR FAST_FORWARD FOR
SELECT sql = 'DROP SCHEMA ['+schemaName+']' FROM 
(SELECT CAST(T.schemaName.query('text()') as VARCHAR(200)) as schemaName FROM @schemasXml.nodes('/schema') T(schemaName)) as X
JOIN information_schema.schemata S on S.schema_name = X.schemaName

OPEN @Cursor FETCH NEXT FROM @Cursor INTO @Sql

WHILE (@@FETCH_STATUS = 0)
BEGIN
PRINT @Sql
Exec (@Sql)
FETCH NEXT FROM @Cursor INTO @Sql
END

CLOSE @Cursor DEALLOCATE @Cursor
GO

@StuffHappens this will drop tables that are not in dbo schema. — David Roussel, Dec 10 '14 at 15:38
Not sure why I'm being downvoted when my answer works, and drops all tables, views and stored proc, not just those in dbo (unlike the main answer which didn't work for me) — David Roussel, May 12 '15 at 10:51

score 9 · Answer 7 · answered Nov 27 '14 at 13:21

9

This is what I have tried:

SELECT 'DROP TABLE [' + SCHEMA_NAME(schema_id) + '].[' + name + ']' FROM sys.tables

What ever the output it will print, just copy all and paste in new query and press execute. This will delete all tables.

answered Nov 27 '14 at 13:21

DareDevil

5,249
6
50
88

score 7 · Answer 8 · answered Sep 24 '09 at 19:16

7

Back up a completely empty database. Instead of dropping all the objects, just restore the backup.

answered Sep 24 '09 at 19:16

A-K

16,804
8
54
74

3

a good example database to backup and restore over your database would be the model database, as that is where CREATE DATABASE gets the template for new databases. – David Parvin Nov 09 '11 at 23:46

score 3 · Answer 9 · edited Apr 30 '11 at 07:54

I accidentally ran a db init script against my master database tonight. Anyways, I quickly ran into this thread. I used the: exec sp_MSforeachtable 'DROP TABLE ?' answer, but had to execute it multiple times until it didn't error (dependencies.) After that I stumbled upon some other threads and pieced this together to drop all the stored procedures and functions.

DECLARE mycur CURSOR FOR select O.type_desc,schema_id,O.name
from 
    sys.objects             O LEFT OUTER JOIN
    sys.extended_properties E ON O.object_id = E.major_id
WHERE
    O.name IS NOT NULL
    AND ISNULL(O.is_ms_shipped, 0) = 0
    AND ISNULL(E.name, '') <> 'microsoft_database_tools_support'
    AND ( O.type_desc = 'SQL_STORED_PROCEDURE' OR O.type_desc = 'SQL_SCALAR_FUNCTION' )
ORDER BY O.type_desc,O.name;

OPEN mycur;

DECLARE @schema_id int;
DECLARE @fname varchar(256);
DECLARE @sname varchar(256);
DECLARE @ftype varchar(256);

FETCH NEXT FROM mycur INTO @ftype, @schema_id, @fname;

WHILE @@FETCH_STATUS = 0
BEGIN
    SET @sname = SCHEMA_NAME( @schema_id );
    IF @ftype = 'SQL_STORED_PROCEDURE'
        EXEC( 'DROP PROCEDURE "' + @sname + '"."' + @fname + '"' );
    IF @ftype = 'SQL_SCALAR_FUNCTION'
        EXEC( 'DROP FUNCTION "' + @sname + '"."' + @fname + '"' );

    FETCH NEXT FROM mycur INTO @ftype, @schema_id, @fname;
END

CLOSE mycur
DEALLOCATE mycur

GO

score 2 · Answer 10 · edited Feb 24 '15 at 18:45

Try this

Select 'ALTER TABLE ' + Table_Name  +'  drop constraint ' + Constraint_Name  from Information_Schema.CONSTRAINT_TABLE_USAGE

Select 'drop Procedure ' + specific_name  from Information_Schema.Routines where specific_name not like 'sp%' AND specific_name not like 'fn_%'

Select 'drop View ' + table_name  from Information_Schema.tables where Table_Type = 'VIEW'

SELECT 'DROP TRIGGER ' + name FROM sysobjects WHERE type = 'tr'

Select 'drop table ' + table_name  from Information_Schema.tables where Table_Type = 'BASE TABLE'

score 2 · Answer 11 · answered Mar 10 '15 at 08:40

In addition to @Ivan's answer, types all need to be included

    /* Drop all Types */
DECLARE @name VARCHAR(128)
DECLARE @SQL VARCHAR(254)

SELECT @name = (SELECT TOP 1 [name] FROM sys.types where is_user_defined = 1 ORDER BY [name])

WHILE @name IS NOT NULL
BEGIN
    SELECT @SQL = 'DROP TYPE [dbo].[' + RTRIM(@name) +']'
    EXEC (@SQL)
    PRINT 'Dropped Type: ' + @name
    SELECT @name = (SELECT TOP 1 [name] FROM sys.types where is_user_defined = 1 AND [name] > @name ORDER BY [name])
END
GO

score 1 · Answer 12 · edited Apr 28 '18 at 15:25

You have to disable all triggers and constraints first.

EXEC sp_MSforeachtable @command1="ALTER TABLE ? NOCHECK CONSTRAINT ALL"

EXEC sp_MSforeachtable @command1="ALTER TABLE ? DISABLE TRIGGER ALL"

After that you can generate the scripts for deleting the objects as

SELECT 'Drop Table '+name FROM sys.tables WHERE type='U';

SELECT 'Drop Procedure '+name FROM  sys.procedures WHERE type='P';

Execute the statements generated.

Haseeb · Answer 13 · 2022-05-07T15:50:40.103

try this with sql2012 or above,

this will help to delete all objects by selected schema

DECLARE @MySchemaName VARCHAR(50)='dbo', @sql VARCHAR(MAX)='';
DECLARE @SchemaName VARCHAR(255), @ObjectName VARCHAR(255), @ObjectType VARCHAR(255), @ObjectDesc VARCHAR(255), @Category INT;

DECLARE cur CURSOR FOR
    SELECT  (s.name)SchemaName, (o.name)ObjectName, (o.type)ObjectType,(o.type_desc)ObjectDesc,(so.category)Category
    FROM    sys.objects o
    INNER JOIN sys.schemas s ON o.schema_id = s.schema_id
    INNER JOIN sysobjects so ON so.name=o.name
    WHERE s.name = @MySchemaName
    AND so.category=0
    AND o.type IN ('P','PC','U','V','FN','IF','TF','FS','FT','PK','TT')

OPEN cur
FETCH NEXT FROM cur INTO @SchemaName,@ObjectName,@ObjectType,@ObjectDesc,@Category

SET @sql='';
WHILE @@FETCH_STATUS = 0 BEGIN    
    IF @ObjectType IN('FN', 'IF', 'TF', 'FS', 'FT') SET @sql=@sql+'Drop Function '+@MySchemaName+'.'+@ObjectName+CHAR(13)
    IF @ObjectType IN('V') SET @sql=@sql+'Drop View '+@MySchemaName+'.'+@ObjectName+CHAR(13)
    IF @ObjectType IN('P') SET @sql=@sql+'Drop Procedure '+@MySchemaName+'.'+@ObjectName+CHAR(13)
    IF @ObjectType IN('U') SET @sql=@sql+'Drop Table '+@MySchemaName+'.'+@ObjectName+CHAR(13)

    --PRINT @ObjectName + ' | ' + @ObjectType
    FETCH NEXT FROM cur INTO @SchemaName,@ObjectName,@ObjectType,@ObjectDesc,@Category
END
CLOSE cur;    
DEALLOCATE cur;
SET @sql=@sql+CASE WHEN LEN(@sql)>0 THEN 'Drop Schema '+@MySchemaName+CHAR(13) ELSE '' END
PRINT @sql
--EXECUTE (@sql)

this works perfectly fine, what I would fix/add is a distinct clause on first select statement from top to bottom line 5 to make it a bit more performant. — El maik, May 04 '22 at 17:13

score 0 · Answer 14 · answered May 15 '14 at 12:46

Here I found new query to delete all sp,functions and triggers

declare @procName varchar(500)
declare cur cursor 

for select [name] from sys.objects where type = 'p'
open cur
fetch next from cur into @procName
while @@fetch_status = 0
begin
    exec('drop procedure ' + @procName)
    fetch next from cur into @procName
end
close cur
deallocate cur

score 0 · Answer 15 · answered Aug 22 '14 at 21:01

To add to Ivan's answer, I have also had the need to drop all user-defined types, so I have added this to the script:

/* Drop all user-defined types */
DECLARE @name VARCHAR(128)
DECLARE @SQL VARCHAR(254)

SELECT @name = (select TOP 1 [name] from sys.types where is_user_defined = 1)

WHILE @name IS NOT NULL
BEGIN
    SELECT @SQL = 'DROP TYPE [dbo].[' + RTRIM(@name) +']'
    EXEC (@SQL)
    PRINT 'Dropped Type: ' + @name
    SELECT @name = (select TOP 1 [name] from sys.types where is_user_defined = 1)
END
GO

Goyal Vicky · Answer 16 · 2018-07-25T08:42:21.687

To remove all objects in oracle :

1) Dynamic

DECLARE
CURSOR IX IS
SELECT * FROM ALL_OBJECTS WHERE OBJECT_TYPE ='TABLE' 
AND OWNER='SCHEMA_NAME';
 CURSOR IY IS
 SELECT * FROM ALL_OBJECTS WHERE OBJECT_TYPE 
IN ('SEQUENCE',
'PROCEDURE',
'PACKAGE',
'FUNCTION',
'VIEW') AND  OWNER='SCHEMA_NAME';
 CURSOR IZ IS
 SELECT * FROM ALL_OBJECTS WHERE OBJECT_TYPE IN ('TYPE') AND  OWNER='SCHEMA_NAME';
BEGIN
 FOR X IN IX LOOP
   EXECUTE IMMEDIATE('DROP '||X.OBJECT_TYPE||' '||X.OBJECT_NAME|| ' CASCADE CONSTRAINT');
 END LOOP;
 FOR Y IN IY LOOP
   EXECUTE IMMEDIATE('DROP '||Y.OBJECT_TYPE||' '||Y.OBJECT_NAME);
 END LOOP;
 FOR Z IN IZ LOOP
   EXECUTE IMMEDIATE('DROP '||Z.OBJECT_TYPE||' '||Z.OBJECT_NAME||' FORCE ');
 END LOOP;
END;
/

2)Static

    SELECT 'DROP TABLE "' || TABLE_NAME || '" CASCADE CONSTRAINTS;' FROM user_tables
        union ALL
        select 'drop '||object_type||' '|| object_name || ';' from user_objects 
        where object_type in ('VIEW','PACKAGE','SEQUENCE', 'PROCEDURE', 'FUNCTION')
        union ALL
        SELECT 'drop '
        ||object_type
        ||' '
        || object_name
        || ' force;'
        FROM user_objects
        WHERE object_type IN ('TYPE');

score 0 · Answer 17 · answered Jan 22 '21 at 09:11

One more sample

declare @objectId int,  @objectName varchar(500), @schemaName varchar(500), @type nvarchar(30), @parentObjId int, @parentObjName nvarchar(500)
declare cur cursor 
for 

select obj.object_id, s.name as schema_name, obj.name, obj.type, parent_object_id
from sys.schemas s
    inner join sys.sysusers u
        on u.uid = s.principal_id
        JOIN
        sys.objects obj on obj.schema_id = s.schema_id
WHERE s.name = 'schema_name' and (type = 'p' or obj.type = 'v' or obj.type = 'u' or obj.type = 'f' or obj.type = 'fn')

order by obj.type

open cur
fetch next from cur into @objectId, @schemaName, @objectName,  @type, @parentObjId
while @@fetch_status = 0
begin
    if @type = 'p'
    begin
        exec('drop procedure ['+@schemaName +'].[' + @objectName + ']')
    end

    if @type = 'fn'
    begin
        exec('drop FUNCTION ['+@schemaName +'].[' + @objectName + ']')
    end

    if @type = 'f'
    begin
        set @parentObjName = (SELECT name from sys.objects WHERE object_id = @parentObjId)
        exec('ALTER TABLE ['+@schemaName +'].[' + @parentObjName + ']' + 'DROP CONSTRAINT ' +  @objectName)
    end

    if @type = 'u'
    begin
        exec('drop table ['+@schemaName +'].[' + @objectName + ']')
    end

    if @type = 'v'
    begin
        exec('drop view ['+@schemaName +'].[' + @objectName + ']')
    end
    fetch next from cur into  @objectId, @schemaName, @objectName,  @type, @parentObjId
end
close cur
deallocate cur

Gennadii Saltyshchak · Answer 18 · 2023-07-22T07:48:56.000

My option is targeted to revert database to the original state, that is the same state as newly created database, in order to guarantee deterministic and fault-tolerant behavior of integration tests. For this purpose, it cleans up all objects and schemas created by user and only retains the empty predefined schema dto. This script requires SQL Server 2017 by using some of its features which help to make code more readable and maintainable.

DECLARE @command NVARCHAR(MAX) = '';

WITH UserDefinedSchemas AS (
    SELECT 
        s.schema_id, 
        s.name AS schema_name,
        IIF(s.Name = 'dbo', 1, 0) schema_predefined
    FROM sys.schemas s
    INNER JOIN sys.sysusers u ON u.uid = s.principal_id
    WHERE u.issqlrole = 0 AND u.name NOT IN ('sys', 'guest', 'INFORMATION_SCHEMA')
),
Commands(Command) AS (
    -- Procedures
    SELECT 'DROP PROCEDURE [' + schema_name + '].[' + name + ']'
    FROM sys.procedures o
    JOIN UserDefinedSchemas schemas ON o.schema_id = schemas.schema_id
    
    -- Functions
    UNION ALL
    SELECT 'DROP FUNCTION [' + schema_name + '].[' + name + ']'
    FROM sys.objects o
    JOIN UserDefinedSchemas schemas ON o.schema_id = schemas.schema_id
    WHERE type IN ('FN', 'IF', 'TF')
    
    -- Views
    UNION ALL
    SELECT 'DROP VIEW [' + schema_name + '].[' + name + ']'
    FROM sys.views o
    JOIN UserDefinedSchemas schemas ON o.schema_id = schemas.schema_id
    
    -- Check constraints
    UNION ALL
    SELECT 
        'ALTER TABLE [' + schema_name + '].[' + object_name(parent_object_id) + '] ' +
        'DROP CONSTRAINT [' + name + ']'
    FROM sys.check_constraints o
    JOIN UserDefinedSchemas schemas ON o.schema_id = schemas.schema_id

    -- Foreign keys
    UNION ALL
    SELECT 
        'ALTER TABLE [' + schema_name + '].[' + object_name(parent_object_id) + '] ' +
        'DROP CONSTRAINT [' + name + ']'
    FROM sys.foreign_keys o
    JOIN UserDefinedSchemas schemas ON o.schema_id = schemas.schema_id
    
    -- Tables
    UNION ALL
    SELECT 'DROP TABLE [' + schema_name + '].[' + name + ']'
    FROM sys.tables o
    JOIN UserDefinedSchemas schemas ON o.schema_id = schemas.schema_id

    -- Sequences
    UNION ALL
    SELECT 'DROP SEQUENCE [' + schema_name + '].[' + name + ']'
    FROM sys.sequences o
    JOIN UserDefinedSchemas schemas ON o.schema_id = schemas.schema_id

    -- User defined types
    UNION ALL
    SELECT 'DROP TYPE [' + schema_name + '].[' + name + ']'
    FROM sys.types o
    JOIN UserDefinedSchemas schemas ON o.schema_id = schemas.schema_id
    WHERE is_user_defined = 1

    -- Schemas
    UNION ALL
    SELECT 'DROP SCHEMA [' + schema_name + ']'
    FROM UserDefinedSchemas
    WHERE schema_predefined = 0
)
SELECT @command = STRING_AGG(Command, CHAR(10))
FROM Commands

PRINT @command
-- CAUTION: This line will permanently remove all user-created object in database!
-- Please double the commands to be executed in output window and uncomment this line once you are completely confident to run them!
-- EXEC sp_executesql @command

score -1 · Answer 19 · edited Jul 14 '16 at 20:02

DECLARE @name VARCHAR(255)
DECLARE @type VARCHAR(10)
DECLARE @prefix VARCHAR(255)
DECLARE @sql VARCHAR(255)

DECLARE curs CURSOR FOR
SELECT [name], xtype
FROM sysobjects
WHERE xtype IN ('U', 'P', 'FN', 'IF', 'TF', 'V', 'TR') -- Configuration point 1
ORDER BY name

OPEN curs
FETCH NEXT FROM curs INTO @name, @type

WHILE @@FETCH_STATUS = 0
BEGIN
-- Configuration point 2
SET @prefix = CASE @type
WHEN 'U' THEN 'DROP TABLE'
WHEN 'P' THEN 'DROP PROCEDURE'
WHEN 'FN' THEN 'DROP FUNCTION'
WHEN 'IF' THEN 'DROP FUNCTION'
WHEN 'TF' THEN 'DROP FUNCTION'
WHEN 'V' THEN 'DROP VIEW'
WHEN 'TR' THEN 'DROP TRIGGER'
END

SET @sql = @prefix + ' ' + @name
PRINT @sql
EXEC(@sql)
FETCH NEXT FROM curs INTO @name, @type
END

CLOSE curs
DEALLOCATE curs

score -1 · Answer 20 · answered Mar 01 '09 at 20:47

-1

Seems like a rather dangerous feature to me. If you'd implement something like this I would make sure to properly secure it in a way you won't be able to run this per accident.

As suggested before you could make some sort of stored procedure yourself. In SQL Server 2005 you could have a look this system table to determine and find the objects you would like to drop.

select * from sys.objects

answered Mar 01 '09 at 20:47

Roel Snetselaar

244
2
5

not more dangerous than a recursive delete of a bunch of folders. – bvdb Aug 23 '21 at 06:10

score -2 · Answer 21 · edited Jun 26 '14 at 10:51

try this....

USE DATABASE
GO
DECLARE @tname VARCHAR(150)
DECLARE @strsql VARCHAR(300)

SELECT @tname = (SELECT TOP 1 [name] FROM sys.objects WHERE [type] = 'U' and [name] like N'TableName%' ORDER BY [name])

WHILE @tname IS NOT NULL
BEGIN
    SELECT @strsql = 'DROP TABLE [dbo].[' + RTRIM(@tname) +']'
    EXEC (@strsql)
    PRINT 'Dropped Table : ' + @tname
    SELECT @tname = (SELECT TOP 1 [name] FROM sys.objects WHERE [type] = 'U' AND [name] like N'TableName%'  AND [name] > @tname ORDER BY [name])
END

score -2 · Answer 22 · edited Dec 01 '15 at 10:50

-2

There is no single statement that can be used to achieve this aim.

You could of course create yourself a stored procedure that you could use to perform these various administrative tasks.

You could then execute the procedure using this single statement.

Exec sp_CleanDatabases @DatabaseName='DBname'

edited Dec 01 '15 at 10:50

Sabyasachi Mishra

1,677
2
31
49

answered Feb 11 '09 at 11:21

John Sansom

41,005
9
72
84

Yes, we could. We could then create a webservice for it, and we could connect it to alexa, and then we could drop the schema using our voice. But it's the content of that `sp_CleanDatabases` function that I'm really interested about. – bvdb Aug 23 '21 at 06:15

Drop all the tables, stored procedures, triggers, constraints and all the dependencies in one sql statement

22 Answers22

Linked

Related