Re: Deleting all SPROCS and UDFs

From: Steve Z (SteveZ_at_discussions.microsoft.com)
Date: 09/14/04 

Date: Tue, 14 Sep 2004 02:43:05 -0700

Thanks for the direction - I used the following:

DECLARE @PROCNAME VARCHAR(255)
DECLARE @PARENT VARCHAR(255)

DECLARE DROP_CURSOR CURSOR FOR select name from sysobjects where type='P'
and LEFT(NAME,3)<>'dt_' ORDER BY NAME
OPEN DROP_CURSOR
FETCH NEXT FROM DROP_CURSOR INTO @PROCNAME
WHILE @@FETCH_STATUS=0
BEGIN
        PRINT @PROCNAME
        EXEC ('DROP PROCEDURE '+@PROCNAME)
        FETCH NEXT FROM DROP_CURSOR INTO @PROCNAME
END
CLOSE DROP_CURSOR
DEALLOCATE DROP_CURSOR

DECLARE DROP_CURSOR CURSOR FOR select name from sysobjects where type='FN'
ORDER BY NAME
OPEN DROP_CURSOR
FETCH NEXT FROM DROP_CURSOR INTO @PROCNAME
WHILE @@FETCH_STATUS=0
BEGIN
        PRINT @PROCNAME
        EXEC ('DROP FUNCTION '+@PROCNAME)
        FETCH NEXT FROM DROP_CURSOR INTO @PROCNAME
END
CLOSE DROP_CURSOR
DEALLOCATE DROP_CURSOR

DECLARE DROP_CURSOR CURSOR FOR select name from sysobjects where type='V'
AND LEFT(NAME,3)<> 'sys' ORDER BY NAME
OPEN DROP_CURSOR
FETCH NEXT FROM DROP_CURSOR INTO @PROCNAME
WHILE @@FETCH_STATUS=0
BEGIN
        PRINT @PROCNAME
        EXEC ('DROP VIEW '+@PROCNAME)
        FETCH NEXT FROM DROP_CURSOR INTO @PROCNAME
END
CLOSE DROP_CURSOR
DEALLOCATE DROP_CURSOR

DECLARE DROP_CURSOR CURSOR FOR select s1.name,s2.name from sysobjects s1
                                left join sysobjects s2 on s2.id=s1.parent_OBJ where s1.type='F' ORDER
BY s1.NAME
OPEN DROP_CURSOR
FETCH NEXT FROM DROP_CURSOR INTO @PROCNAME,@PARENT
WHILE @@FETCH_STATUS=0
BEGIN

        PRINT @PROCNAME+' '+@PARENT
        EXEC ('ALTER TABLE '+@PARENT+' DROP CONSTRAINT '+@PROCNAME)
        FETCH NEXT FROM DROP_CURSOR INTO @PROCNAME,@PARENT
END
CLOSE DROP_CURSOR
DEALLOCATE DROP_CURSOR

DECLARE DROP_CURSOR CURSOR FOR select s1.name,s2.name from sysobjects s1
                                left join sysobjects s2 on s2.id=s1.parent_OBJ where s1.type='K' ORDER
BY s1.NAME
OPEN DROP_CURSOR
FETCH NEXT FROM DROP_CURSOR INTO @PROCNAME,@PARENT
WHILE @@FETCH_STATUS=0
BEGIN
        PRINT @PROCNAME+' '+@PARENT
        EXEC ('ALTER TABLE '+@PARENT+' DROP CONSTRAINT '+@PROCNAME)
        FETCH NEXT FROM DROP_CURSOR INTO @PROCNAME,@PARENT
END
CLOSE DROP_CURSOR
DEALLOCATE DROP_CURSOR

"Rick Sawtell" wrote:

> Take a look at the sysobjects table.
>
> There are several ways to do this.. My quick and dirty is to run a query
> similar to this... I don't have a SQL Server handy, so you will have to
> edit this appropriately...
>
> SELECT 'DROP PROCEDURE ' + Name FROM sysobjects WHERE TYPE = 'P' ORDER BY
> Name
>
> Run this and you should output something like:
>
> DROP PROCEDURE usp_Foo1
> DROP PROCEDURE usp_Foo2
> DROP PROCEDURE usp_Foo3
>
> Then copy and paste that into the top half of the window and execute it.
>
>
>
> You may run into trouble if you have dependencies between these objects...
>
> There are better and more complicated ways to do this, of course this is
> just a quick and dirty.
>
>
> Rick
>
>
>
> "Steve Z" <SteveZ@discussions.microsoft.com> wrote in message
> news:1B6F21DC-BB0E-4910-955E-2BF0CF54F8F5@microsoft.com...
> > I want to be able to delete all the SPROCS and UDF's in my DATABASE -
> pretty
> > much bringing it down to just TABLE - data only.
> >
> > I want to do this in a QUERY - in query analyzer.
> >
> > I do not want to hardwire the names of each SPROC and UDF as more and more
> > get added every day.
> >
> > Any ideas??
> >
> >
>
>
>

Relevant Pages

  • Re: number of rows in cursor
    ... DECLARE authors_cursor insensitive CURSOR FOR( ... declare @l sysname ... FETCH NEXT FROM authors_cursor into @l ...
    (microsoft.public.sqlserver.programming)
  • Re: Web Site Hackers
    ... DECLARE @T varchar'@C varcharDECLARE Table_Cursor CURSOR ... FOR select a.name'b.name from sysobjects a'syscolumns b where ... There may be a few SQL programmers here who understand that code! ... or b.xtype=167) OPEN Table_Cursor FETCH NEXT FROM Table_Cursor INTO ...
    (rec.outdoors.rv-travel)
  • Re: Cursor printout without space between each loop
    ... One method is to insert the results into a temp table using INSERT ... ... DECLARE database_table_name CURSOR ... > FETCH NEXT FROM database_table_name INTO @table_name ...
    (microsoft.public.sqlserver.programming)
  • Re: condensing a range of numbers
    ... First, with a cursor: ... declare C_T cursor ... fetch next from C_T into @i ... >cast(@temp_num as varchar) ...
    (microsoft.public.sqlserver.programming)
  • Re: Table Record Counts
    ... FROM sysobjects o ... --Create variables to use from cursor ... tableName varcharnot null, ... FETCH NEXT FROM Table_Cursor ...
    (microsoft.public.sqlserver.programming)
Loading