How to Delete all the Tables and Stored Procedures in Sql Server 2008

How to delete or drop all tables from a SQL Server database using just a SQL query.

The following SQL will delete all the tables and their associated information from a database.

USE [database] EXEC sp_MSforeachtable @command1 = "DROP TABLE ?"

The only downside to this is that you cannot perform a rollback, so you need to make sure this is what you want to do. Otherwise, that’s all there is to it. Just run this in a New Query Window in SQL Server Management Studio (SSMS) and you’re done.

Sql Scripts - Delete all Tables, Procedures, Views and Functions

Friday, August 20, 2010SQL Server T-SQL

In a shared environment you typically don't have access to delete your database, and recreate it for fresh installs of your product.

I managed to find these scripts which should help you clean out your database.

Use at your own risk.

Delete All Tables

--Delete All Keys

DECLARE @Sql NVARCHAR(500) DECLARE @Cursor CURSOR

SET @Cursor = CURSOR FAST_FORWARD FOR

SELECT DISTINCT sql = 'ALTER TABLE [' + 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

Exec SP_EXECUTESQL @Sql

FETCH NEXT FROM @Cursor INTO @Sql

END

CLOSE @Cursor DEALLOCATE @Cursor

GO

EXEC sp_MSForEachTable 'DROP TABLE ?'

GO

Delete All Stored Procedures

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

          if @procName <> 'DeleteAllProcedures'

                exec('drop procedure ' + @procName)

                fetch next from cur into @procName

    end

close cur

deallocate cur

Delete All Views

declare @procName varchar(500)

declare cur cursor

  for select [name] from sys.objects where type = 'v'

open cur

fetch next from cur into @procName

    while @@fetch_status = 0

    begin

                exec('drop view ' + @procName)

                fetch next from cur into @procName

    end

close cur

deallocate cur

Delete All Functions

declare @procName varchar(500)

declare cur cursor

  for select [name] from sys.objects where type = 'fn'

open cur

fetch next from cur into @procName

    while @@fetch_status = 0

    begin

                exec('drop function ' + @procName)

                fetch next from cur into @procName

    end

close cur

deallocate cur

Delete All SP

DECLARE @sql NVARCHAR(MAX) = N''; SELECT @sql += N'DROP PROCEDURE dbo.' + QUOTENAME(name) + '; ' FROM sys.procedures WHERE name LIKE N'sp[_]%' AND SCHEMA_NAME(schema_id) = N'dbo'; EXEC sp_executesql @sql;