Click here to Skip to main content
65,938 articles
CodeProject is changing. Read more.
Articles / database / SQL-Server

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

3.00/5 (2 votes)
24 Mar 2010CPOL 1  
Understrading sys.objectsSys.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...

Understrading 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


SQL
Use [database_name]

declare @q as 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;

You can also visit My Blog

License

This article, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)