SQL Server Performance

Dropp All of Specifice Object from Database

Discussion in 'Contribute Your SQL Server Scripts' started by mkhanjani, Jun 23, 2004.

  1. mkhanjani New Member

    /*This SP Dropp All SP,VW or Tables from your database */<br />/*Use it simply by creating it and passing recuired parameter*/<br />/*Use this SP when you whant Update Version of DATABASE*/<br />/*and you don't know which of SP or View should be ADDED */<br />CREATE PROCEDURE [DropAll_VW_SP] <br /> @Drop_Tables bit = 0, --NOT Drop<br /> @Drop_Views bit =1, --Drop<br /> @Drop_Sps bit =1 --Drop<br />AS<br /> declare @Name varchar(50), @Type varchar(4)<br /> declare @SQL nvarchar(100)<br /> /* Attention<img src='/community/emoticons/emotion-2.gif' alt=':D' />ropping tables will be delete all of your DATA */<br /> /* @Drop_Tables 0<img src='/community/emoticons/emotion-2.gif' alt=':D' />o not drop Tables,1<img src='/community/emoticons/emotion-2.gif' alt=':D' />rop All Tables */<br /> /* Be carefull about set 1 Value to @Drop_Tables */<br />-- SELECT @Drop_Tables = 0 --NOT Drop<br /><br /> declare Row_Cursor cursor for <br /> --Select All Objects<br /> --u:user table, p<img src='/community/emoticons/emotion-7.gif' alt=':S' />tored Procedure, v: View<br /> --Category=0 User defined object <br /> select Name,Type from sysobjects <br /> Where category=0 and type in ('u','p','v') order by type,name<br /> open Row_Cursor <br /> fetch next from Row_Cursor into @Name,@Type<br /> <br /> while @@Fetch_Status=0 <br /> begin<br /> select @SQL=''<br /> if @Type='v' and @Drop_Views=1 set @SQL='drop view ' + @Name<br /> if @Type='p' and @Drop_SPs=1 set @SQL='drop procedure ' + @Name<br /> if @Type='u' and @Drop_Tables=1 set @SQL='drop table ' + @Name<br /> exec sp_executesql @SQL <br /> fetch next from Row_Cursor into @Name,@Type<br /> end <br /> <br /> close Row_Cursor<br /> deallocate Row_Cursor<br /><br />GO<br />

Share This Page