다음을 통해 공유

How to drop all tables, all views, and all stored procedures from a SQL 2005 DB...

  • 아티클

This is a follow-up to the blog entry from Jasper Jugan. This is a modification to the script to allow for schema specific deletes of the SP's, Views and I added Functions.

  

 create procedure usp_DropSPFunctionsViews

 as

  

 -- variable to object name

 declare @name  varchar(1000)

 -- variable to hold object type

 declare @xtype varchar(20)

 -- variable to hold sql string

 declare @sqlstring nvarchar(4000)

  

 declare SPViews_cursor cursor for

 SELECT QUOTENAME(ROUTINE_SCHEMA) + '.' + QUOTENAME(ROUTINE_NAME) AS name, ROUTINE_TYPE AS xtype

 FROM

 INFORMATION_SCHEMA.ROUTINES

 UNION

 SELECT QUOTENAME(TABLE_SCHEMA) + '.' + QUOTENAME(TABLE_NAME) AS name, 'VIEW' AS xtype

 FROM

 INFORMATION_SCHEMA.VIEWS

  

 open SPViews_cursor

  

 fetch next from SPViews_cursor into @name, @xtype

  

 while @@fetch_status = 0

   begin

 -- test object type if it is a stored procedure

    if @xtype = 'PROCEDURE'

       begin

         set @sqlstring = 'drop procedure ' + @name

         exec sp_executesql @sqlstring

         set @sqlstring = ' '

       end

 -- test object type if it is a function

    if @xtype = 'FUNCTION'

       begin

         set @sqlstring = 'drop FUNCTION ' + @name

         exec sp_executesql @sqlstring

         set @sqlstring = ' '

       end

 -- test object type if it is a view

    if @xtype = 'VIEW'

       begin

          set @sqlstring = 'drop view ' + @name

          exec sp_executesql @sqlstring

          set @sqlstring = ' '

       end

  

 -- get next record

     fetch next from SPViews_cursor into @name, @xtype

   end

  

 close SPViews_cursor

 deallocate SPViews_cursor

 GO

 

Technorati Tags: SQL Server,SQL Server 2005,SQL Server 2008,TSQL

Comments

  • Anonymous
    April 29, 2008
    Just got your comment on my blog about updating the script to include schemas and functions. You're the man! I'll go ahead and update my page so it links to your version of the script

  • Anonymous
    April 29, 2008
    PingBack from http://microsoftnews.askpcdoc.com/sql-server-2005/how-to-drop-all-tables-all-views-and-all-stored-procedures-from-a-sql-2005-db

  • Anonymous
    May 22, 2008
    One of the problems I had when setting up Community Server 2008 was that my attempts to move from a local

  • Anonymous
    August 30, 2008
    wow! i have almost forgot this :)

  • Anonymous
    January 06, 2009
    Click the link in my name to see how to accomplish the same thing without using a cursor

  • Anonymous
    September 20, 2010
    Unfortunately this doesn't work if you have an aggregate defined.