interesting
things in SQL if you have a list of
Store Procedures ,Views and triggers in
your database and reason being you want
to delete all Store procedures means you want to delete all the thing from data
base. In that condition what you will do? It quiet simple you will delete one
by one all the things. But it is so boring and time wasting. Let see how to
write a simple Script to delete all Store procedures, views and triggers from
database.
Remove
All User Defined Store procedures.
Create PROCEDURE [dbo].[usp_RemoveAllStoreProceduers]
AS
BEGIN
declare @spName varchar(150);
--Step 1 : Define Cursor (Create a result set of all
procedures in database).
declare cur cursor for select [name] from sys.objects where type='p'
--Step 2 : Open Cursor
open cur
--Step 3 : Fatch data into local parameter
fetch next from cur into @spName
while @@fetch_Status
=0
begin
-- Drop current store procedure in Cursor
exec ('drop procedure '+
@spName)
fetch next from cur into @spName
end
close cur
--Close Cursor After done
deallocate cur
--Deallocate Cursor After done
END
Remove
All User Defined View.
Create PROCEDURE [dbo].[usp_RemoveAllUserDefinedViews]
AS
BEGIN
declare @viewName varchar(150);
--Step 1 : Define Cursor (Create a result set of all views in database).
declare cur cursor for select [name] from sys.objects where type='v'
--Step 2 : Open Cursor
open cur
--Step 3 : Fatch data into local parameter
fetch next from cur into @viewName
while @@fetch_Status
=0
begin
-- Drop current view in Cursor
exec ('drop view '+ @viewName)
fetch next from cur into @ viewName
end
close cur
--Close Cursor After done
deallocate cur
--Deallocate Cursor After done
E
.net training in jaipur
