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