SQL SERVER 2005: Drop all the tables, stored procedures, triggers, constriants and all the dependencies in one sql statement?

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 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.

– StuffHappens Dec 20 '10 at 10:54 2 I think I might love you for creating this script, thank you, thank you, thank you. – CubanX Feb 23 at 21:27 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 8 at 1:00.

– renegadeMind Feb 11 '09 at 11:31 1 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 +1!

Great suggestion! – Mark Kram Feb 11 '090 at 19:17.

The best thing to do it is "Generate scripts for Drop" Select Database -> Right Click -> Tasks -> Generate Scripts - will open wizard for generating scripts Select the database -> next -> Set option 'Script to create' to true (want to create) -> Set option 'Script to Drop' to true (want to drop) -> Next -> Select the Check box to select objects wish to create script -> Select the choice to write script (File, New window, Clipboard) It includes dependendent objects by default. Execute the script This way we can customize our script. I hope this will help you!

Best Wishes, JP.

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

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 9 at 23:46.

I found this implementation that take schemas into account. I'm not sure if it is solid as rock, but it worked for me until now. tdryan.blogspot.com/2010/05/drop-all-obj... tdryan.blogspot.com/2010/05/drop-all-sch....

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 a single statement.

Exec sp_CleanDatabases @DatabaseName='DBname.

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.

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 1 might blow up if a parent table is dropped before the child one – AlexKuznetsov Sep 24 '09 at 19:15.

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.

Something tells me that you aren't supposed to have these rights for a good reason and that perhaps you're up to something nefarious.

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.

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: blog.falafel.com/Blogs/AdamAnderson/09-0....

I cant really gove you an answer,but what I can give you is a way to a solution, that is you have to find the anglde that you relate to or peaks your interest. A good paper is one that people get drawn into because it reaches them ln some way.As for me WW11 to me, I think of the holocaust and the effect it had on the survivors, their families and those who stood by and did nothing until it was too late.