Delete multiple Procedure/Table/Trigger form sqlserver database
Posted by Viral Sarvaiya on December 14, 2012
In sql server we use one query for drop table/procedure/trigger like below
Drop table table_name Drop procedure Procedure_name Drop trigger Trigger_name
But if we want to delete some or all Tables/Procedure/Trigger then what?
have to delete one by one?
We can create one loop with cursor to drop table/procedure/trigger for that as like below.
we can create SP to delete perticulers of the database as passing text and type as a parameter.
For that we can use sys.objects table
For table Type = ‘U’
For procedure Type = ‘P’
For Trigger Type = ‘TR’
For more type of sys.objects please refer http://msdn.microsoft.com/en-us/library/ms190324.aspx
Stored procedure is as below.
ALTER PROCEDURE DeleteEntities @Type VARCHAR(2), @SearchText VARCHAR(50) AS BEGIN DECLARE @procName varchar(500) DECLARE @StrDrop VARCHAR(50) IF (@type != '') BEGIN DECLARE cur cursor FOR SELECT [name] FROM sys.objects WHERE TYPE = @Type AND NAME LIKE '%'+ @SearchText +'%' open cur fetch next from cur into @procName while @@fetch_status = 0 BEGIN SET @StrDrop = ((CASE WHEN @Type = 'U' THEN 'drop table ' WHEN @Type = 'P' THEN 'drop Procedure ' WHEN @Type = 'TR' THEN 'Drop trigger ' ELSE '' END) + @procName) IF (@StrDrop != '') BEGIN EXEC @StrDrop END fetch next from cur into @procName end close cur deallocate cur END END
As like this we can also drop table constraints.
Hope this will helps you.