Thursday, March 25, 2010

Delete All Stored Procedures and Tables from a single Query

hi to all of u
some time we in need to delete all stored procedures and tables from database
so for that we need a single query which do the all the work. below is the query once u run both query all tables and stored procedure of that database is drooped.
but carefully because once you dropped u cant get them back--
----------drop all tables from database-----------

exec sp_MSforeachtable "DROP TABLE ? PRINT '? to be dropped' "






------------drop all sp's in a database---------------


DECLARE @procedureName varchar(500)
DECLARE cur CURSOR
FOR SELECT [name] FROM sys.objects WHERE type = 'p'
OPEN cur

FETCH NEXT FROM cur INTO @procedureName
WHILE @@fetch_status = 0
BEGIN
EXEC('DROP PROCEDURE ' + @procedureName)
FETCH NEXT FROM cur INTO @procedureName
END
CLOSE cur
DEALLOCATE cur


this works i have tested it several time with MS sql server 2005

1 comment: