Click here to Skip to main content
15,867,765 members
Articles / Database Development / SQL Server
Tip/Trick

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

Rate me:
Please Sign up or sign in to vote.
3.00/5 (2 votes)
24 Mar 2010CPOL 25.8K   8   5
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)


Written By
Software Developer (Senior) Gateway Technolabs Pvt. Ltd
India India
Having 4+ years of Technical Experience in asp.net,c#.net, SQL Server 2008,AJAX, XML,JQuery, JavaScript, .net framework, WCF, WPF/Silverlight,SSIS, SSRS, asp.net MVC.

While not working loves Photography and bike riding. Playing computer games are the best stress buster for him Smile | :)

Comments and Discussions

 
GeneralFor Ravi LVS, Pin
kiran dangar25-Feb-10 19:20
kiran dangar25-Feb-10 19:20 
GeneralRe: For Ravi LVS, Pin
Ravi LVS26-Feb-10 15:36
Ravi LVS26-Feb-10 15:36 
GeneralRe: For Ravi LVS, Pin
kiran dangar26-Feb-10 17:33
kiran dangar26-Feb-10 17:33 

General General    News News    Suggestion Suggestion    Question Question    Bug Bug    Answer Answer    Joke Joke    Praise Praise    Rant Rant    Admin Admin   

Use Ctrl+Left/Right to switch messages, Ctrl+Up/Down to switch threads, Ctrl+Shift+Left/Right to switch pages.