Asp.net blogs

Thursday, 4 July 2013

Delete All Stored Procedure's and Tables from Database at a time instead of one by one in Sql Server

Query to delete all Stored Procedure at a time from database in Sql Server Query Execution Window :

declare @procName sysname
declare someCursor cursor for
select name from sysobjects where type = 'P' and objectproperty(id, 'IsMSShipped') = 0
open someCursor
fetch next from someCursor into @procName
while @@FETCH_STATUS = 0
begin
exec('drop proc ' + @procName)
fetch next from someCursor into @procName
end
close someCursor
deallocate someCursor
go

Query to delete all Tables at a time from database in Sql Server Query Execution Window :

DECLARE @Sql NVARCHAR(500) DECLARE @Cursor CURSOR
SET @Cursor = CURSOR FAST_FORWARD FOR
SELECT DISTINCT sql = 'ALTER TABLE [' + tc2.TABLE_NAME + '] DROP [' + rc1.CONSTRAINT_NAME + ']'
FROM INFORMATION_SCHEMA.REFERENTIAL_CONSTRAINTS rc1
LEFT JOIN INFORMATION_SCHEMA.TABLE_CONSTRAINTS tc2 ON tc2.CONSTRAINT_NAME =rc1.CONSTRAINT_NAME
OPEN @Cursor FETCH NEXT FROM @Cursor INTO @Sql
WHILE (@@FETCH_STATUS = 0)
BEGIN
Exec SP_EXECUTESQL @Sql
FETCH NEXT FROM @Cursor INTO @Sql
END
CLOSE @Cursor DEALLOCATE @Cursor
GO
EXEC sp_MSForEachTable 'DROP TABLE ?'