SEO

How can i delete all user stored procedures and all table triggers very fast
in
a single database?

Thank you

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

I will try this today.

Thank you

> 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

"serge" <sergea@nospam.ehmail.com> wrote in message
newsEg0d.30007$lP4.1961638@news20.bellglobal.com ...
> I will try this today.


As we discover Bank of America goes offline since their databases somehow
lost their entire schema in a hacker attack. :-)



>
> Thank you
>
> > 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
>
>

Well, at least the tables are still there :-)

--
Hope this helps.

Dan Guzman
SQL Server MVP

"Greg D. Moore (Strider)" <mooregr_deleteth1s@greenms.com> wrote in message
news:Xxh0d.21421$2s.7550@twister.nyroc.rr.com...
>
> "serge" <sergea@nospam.ehmail.com> wrote in message
> newsEg0d.30007$lP4.1961638@news20.bellglobal.com ...
>> I will try this today.
>
>
> As we discover Bank of America goes offline since their databases somehow
> lost their entire schema in a hacker attack. :-)
>
>
>
>>
>> Thank you
>>
>> > 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
>>
>>
>
>

What's the fastest way to delete tables?


> Well, at least the tables are still there :-)

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

>> Well, at least the tables are still there :-)
>
>

"serge" <sergea@nospam.ehmail.com> wrote in news:BRJ0d.35921$lP4.2446119
@news20.bellglobal.com:

> What's the fastest way to delete tables?
>
>


USE master
DROP DATABASE CriticalFinancialInfo
GO

> AND OBJECTPROPERTY (Id, 'IsMSShipped') = 0

Yes, this is correct. I should have included this in the script I posted.

--
Hope this helps.

Dan Guzman
SQL Server MVP

"serge" <sergea@nospam.ehmail.com> wrote in message
news:tdK0d.36158$lP4.2450872@news20.bellglobal.com ...
>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
>> >
>> >
>> >
>>
>>
>
>

My quest continues, maybe i should explain what i am trying to achieve.

There is a database with 3000+ Stored Procedures. We give copies to other
people
and we continue making updates to the *development* database. When we want
to give the other people our latest stored procedures, we have code that
deletes all
stored procedures one by one, thus taking maybe 30 minutes to delete.
Then we recreate all the SPs.

Now i wanted to find out if there was a way to speed this process, i
originally thought
that deleting all SPs one shot could do the trick. But the further i analyze
it, i see some complications.

For example, the other people could very well have created their own SPs,
how can
i NOT delete those SPs?

Do you or anyone else have any idea how i can accomplish this?

Thank you


> > AND OBJECTPROPERTY (Id, 'IsMSShipped') = 0
>
> Yes, this is correct. I should have included this in the script I posted.