Code Simplified – Viral Sarvaiya

Code Simplified – Viral Sarvaiya, Web Developer Friends, dot net Developer, Sql Server Developer

Posts Tagged ‘drop trigger’

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.

Thanks

Advertisements

Posted in Sql Server | Tagged: , , , , , , | Leave a Comment »

 
%d bloggers like this: