I ran this and it was fast, it's what i was looking for.

I am trying to figure out how to delete ONLY the user objects and not the
system objects.

By changing the WHERE condition to become:


WHERE xtype IN('P', 'TR') AND OBJECTPROPERTY (Id, 'IsMSShipped') = 0

I tried running it and it seems it did not delete the Stored Procedures with
TYPE = System.

I just want to make sure this condition I am using :

AND OBJECTPROPERTY (Id, 'IsMSShipped') = 0

is the right one, that I am not screwing other things unknowingly?

Thank you


"Dan Guzman" <guzmanda@nospam-online.sbcglobal.net> wrote in message
news:%uQ%c.9387$yp2.8834@newssvr30.news.prodigy.co m...
> You can run the following script in Query Analyzer. Be certain you are in
> the correct database.
>
> USE MyDatabase
> DECLARE @DropStatement nvarchar(4000)
> DECLARE DropStatements CURSOR
> LOCAL FAST_FORWARD READ_ONLY FOR
> SELECT N'DROP ' +
> CASE xtype
> WHEN 'P' THEN N'PROCEDURE '
> WHEN 'TR' THEN N'TRIGGER '
> END +
> QUOTENAME(USER_NAME(uid)) +
> N'.' +
> QUOTENAME(name)
> FROM sysobjects
> WHERE xtype IN('P', 'TR')
> OPEN DropStatements
> WHILE 1 = 1
> BEGIN
> FETCH NEXT FROM DropStatements INTO @DropStatement
> IF @@FETCH_STATUS = -1 BREAK
> EXEC(@DropStatement)
> END
> CLOSE DropStatements
> DEALLOCATE DropStatements
>
> --
> Hope this helps.
>
> Dan Guzman
> SQL Server MVP
>
> "serge" <sergea@nospam.ehmail.com> wrote in message
> news:VRP%c.25102$lP4.1520160@news20.bellglobal.com ...
> > How can i delete all user stored procedures and all table triggers very
> > fast
> > in
> > a single database?
> >
> > Thank you
> >
> >
> >
>
>