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

 

Comments (6)

  1. 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

  2. One of the problems I had when setting up Community Server 2008 was that my attempts to move from a local

  3. Jasper Jugan says:

    wow! i have almost forgot this 🙂

  4. Click the link in my name to see how to accomplish the same thing without using a cursor

  5. Radu Luncasu says:

    Unfortunately this doesn't work if you have an aggregate defined.