SQL Server 2008 – How to Delete Everything from a Database

Sometimes you might want to delete everything (tables, views, functions, stored procedures) from a database without having to drop the entire database.  But to do the deletion manually by hand can be very time consuming.

Below is the tried-tested-and-true stored procedure which I use for this purpose.

25% Off CouponSimply execute it in your SQL Server Management Studio.  You will then be able to run this stored procedure to nuke your entire database without having to drop the database.

Source Code:

create procedure usp_DropSPFunctionsViews as

-- variable to object name
declare @name  varchar(1000)

-- variable to hold object type
declare @xtype varchar(20)

-- variable to hold sql string
declare @sqlstring nvarchar(4000)
declare SPViews_cursor cursor for

SELECT QUOTENAME(ROUTINE_SCHEMA) + '.'
+ QUOTENAME(ROUTINE_NAME) AS name, ROUTINE_TYPE AS xtype
FROM INFORMATION_SCHEMA.ROUTINES
UNION
SELECT QUOTENAME(TABLE_SCHEMA) + '.'
+ QUOTENAME(TABLE_NAME) AS name, 'VIEW' AS xtype
FROM INFORMATION_SCHEMA.VIEWS

open SPViews_cursor
fetch next from SPViews_cursor into @name, @xtype

while @@fetch_status = 0
begin
-- test object type if it is a stored procedure
if @xtype = 'PROCEDURE'
begin
set @sqlstring = 'drop procedure ' + @name
exec sp_executesql @sqlstring
set @sqlstring = ' '
end

-- test object type if it is a function
if @xtype = 'FUNCTION'
begin
set @sqlstring = 'drop FUNCTION ' + @name
exec sp_executesql @sqlstring
set @sqlstring = ' '
end

-- test object type if it is a view
if @xtype = 'VIEW'
begin
set @sqlstring = 'drop view ' + @name
exec sp_executesql @sqlstring
set @sqlstring = ' '
end

-- get next record
fetch next from SPViews_cursor into @name, @xtype
end

close SPViews_cursor
deallocate SPViews_cursor

GO
Advertisements
Published in: on April 13, 2011 at 11:03 am  Leave a Comment  

The URI to TrackBack this entry is: https://blackbeltreview.wordpress.com/2011/04/13/sql-server-2008-how-to-delete-everything-from-a-database/trackback/

RSS feed for comments on this post.

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

%d bloggers like this: