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 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;
NOTE :
If you are in need of any Web Development feel free to Inquire us . Dhanashree Inc. Expertise in Asp.net Development, Php Development, Website designing, Open Source customisation. Dhanashree Inc can be our offshore development company / outsourcing web development company, hire dedicated web programmers.
Above information is for knowledge sharing if you have problem / issue / suggestion please intimate us with details for proper and prompt action.
