Click here to Skip to main content
Click here to Skip to main content

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

, 24 Mar 2010 CPOL
Rate this:
Please Sign up or sign in to vote.
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

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)

Share

About the Author

kiran dangar
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 | :)
Follow on   Twitter

Comments and Discussions

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

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

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

| Advertise | Privacy | Mobile
Web03 | 2.8.141022.2 | Last Updated 24 Mar 2010
Article Copyright 2010 by kiran dangar
Everything else Copyright © CodeProject, 1999-2014
Terms of Service
Layout: fixed | fluid