How to make SQL Server 2005 database empty using cursor & sys.objects (Delete All Tables, stored procedures, views & UDF’s)

Understanding sys.objects:

Sys.objects is a system VIEW in SQL Server 2005, for each SQL database there is a separate sys.object view which gets stored within databse itself.

Using Sys.objects returns list of all database objects and its types, type can be either of given below:

DB OBJECT TYPES

F     FOREIGN_KEY_CONSTRAINT

IT    INTERNAL_TABLE

PK    PRIMARY_KEY_CONSTRAINT

S     SYSTEM_TABLE

SQ    SERVICE_QUEUE

U     USER_TABLE

V     VIEW

How to DELETE all User Tables , stored procedures , UDF’s and Views using cursor

Use [database name]

declare @q nvarchar(max)

declare @name nvarchar(max);

declare @type nvarchar(max);

declare cur cursor for

select name ,type from sys.objects where type in(‘p’,‘fn’,‘v’,‘u’);

open cur;

fetch next from cur into @name,@type

while @@fetch_status = 0

begin

if(@type=‘p’)

begin

set @q=N‘drop procedure ‘ + @name;

end

if(@type=‘fn’)

begin

set @q=N‘drop function ‘ + @name;

end

if(@type=‘v’)

begin

set @q=N‘drop view ‘ + @name;

end

if(@type=‘u’)

begin

set @q=N‘drop table ‘ + @name;

end

exec sp_executesql @q;

fetch next from cur into @name,@type

end

close cur;

deallocate cur;