【SQL Server】删除所有表、存储过程

DECLARE @table varchar(64),

@sql varchar(150)

DECLARE curTable SCROLL CURSOR FOR SELECT name FROM sysobjects WHERE Type = 'U' And Name <> 'dtproperties' ORDER BY name

OPEN curTable

FETCH FIRST FROM curTable INTO @table

WHILE (@@FETCH_STATUS = 0)

BEGIN

set @sql = 'Drop table [' + Cast(@table as varchar) +']'

--print @sql

exec (@sql)

    

   FETCH NEXT FROM curTable INTO @table

END

CLOSE curTable

DEALLOCATE curTable

 

--删除存储过程

DECLARE @STRING VARCHAR(8000)

WHILE EXISTS(SELECT NAME FROM SYSOBJECTS WHERE TYPE='P' AND STATUS>=0)

BEGIN

SELECT TOP 1 @STRING='DROP PROCEDURE '+ NAME FROM SYSOBJECTS WHERE TYPE = 'P' AND STATUS>=0

--SELECT @STRING

exec(@STRING)

END

GO