"serge" <sergea@nospam.ehmail.com> wrote in
news:zZM0d.39176$lP4.2491585@news20.bellglobal.com :

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


You *need* version control.

I suppose the simplest way without spending any money would be to add a
table with the current version number of each stored procedure. Your
"update db with new stored procedures" would have to check that version
number and decide whether to drop and recreate the stored procedure.

Taking the idea a step further, the version table would also contain the
complete source of the stored procedure. Then another stored procedure
could cursor over the table, doing the drops and creates. Distributing
your new procedures would involve exporting this table to an external file
(say an MDB) and distributing it to the other recipients.

serge (sergea@nospam.ehmail.com) writes:
> 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?

First of all, does their license permit them to add their own stored
procedures?

As Ross said, you need version control.

In our shop we have all our stored procedures, triggers, tables, in short
all database objects under version control in SourceSafe, and SourceSafe
is the master for all building efforts.

To build and install we have a toolset, called AbaPerls. One tool is
DBBUILD which builds an entire database from scripts, that is tables,
stored procedures, el todo. DBBUILD is also what we add when we add
a new component, or subsystem as we call it, to the database. Then we
have another tool DBUPDGEN which reads SourceSafe, and finds all changes
between two labels and that produces an update script. (For changed tables
you get a template to move over the data, but in most cases you have to
modify the generated code.) Furthermore, AbaPerls has its own set of
tables, so we know what we have loaded into a database. There is also
a stored procedure which lists mismatches between AbaPerls and SQL Server's
own system tables. Customer-added code would end up there.

I have made AbaPerls available as freeware on http://www.abaris.se/abaperls/
But as Ross outlined, you can achieve something a lot simpler with quite
easy means, and it may be enough for your organization.

--
Erland Sommarskog, SQL Server MVP, esquel@sommarskog.se

Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techinf...2000/books.asp

I understand and I do agree your idea is very good. But at the moment, I am
trying
and hoping that there is a simpler way to rely on the WHERE Condition and
using
some custom field or something in the sysobjects table to decide whether
that SP
is one of our own SP and not delete it.

Thanks for your idea. I'll have to keep this in mind, I might have to use
this
approach in the future.

> You *need* version control.
>
> I suppose the simplest way without spending any money would be to add a
> table with the current version number of each stored procedure. Your
> "update db with new stored procedures" would have to check that version
> number and decide whether to drop and recreate the stored procedure.
>
> Taking the idea a step further, the version table would also contain the
> complete source of the stored procedure. Then another stored procedure
> could cursor over the table, doing the drops and creates. Distributing
> your new procedures would involve exporting this table to an external file
> (say an MDB) and distributing it to the other recipients.

We do have SourceSafe on our end but not to go into details here,
that wouldn't be a viable solution at the time being. Maybe in the future.

Like i just replied to Ross, at the moment, I am trying and hoping that
there is a simpler way to rely on the WHERE Condition and using
some custom field or something in the sysobjects table to decide whether
that SP is one of our own SP and not delete it.

It's very appreciated that you've made your AbaPerls toolset freeware for
everyone to use. I have taken note of the link and I will have to look at
this
in great length in the future. At this moment, it's very hard to switch to
using
tools for what i require. If i can get away with a simple WHERE condition
to NOT delete SPs not created by us, I'll be very interested to use.

Due to time constraints, I don't want to add a new project to work on.
This task of deleting SPs fast is already not part of my regular work, add
to that I have to figure out ways to improve some slow SPs we have (again
not part of my regular work).


Thank you

> First of all, does their license permit them to add their own stored
> procedures?
>
> As Ross said, you need version control.
>
> In our shop we have all our stored procedures, triggers, tables, in short
> all database objects under version control in SourceSafe, and SourceSafe
> is the master for all building efforts.
>
> To build and install we have a toolset, called AbaPerls. One tool is
> DBBUILD which builds an entire database from scripts, that is tables,
> stored procedures, el todo. DBBUILD is also what we add when we add
> a new component, or subsystem as we call it, to the database. Then we
> have another tool DBUPDGEN which reads SourceSafe, and finds all changes
> between two labels and that produces an update script. (For changed tables
> you get a template to move over the data, but in most cases you have to
> modify the generated code.) Furthermore, AbaPerls has its own set of
> tables, so we know what we have loaded into a database. There is also
> a stored procedure which lists mismatches between AbaPerls and SQL
Server's
> own system tables. Customer-added code would end up there.
>
> I have made AbaPerls available as freeware on
http://www.abaris.se/abaperls/
> But as Ross outlined, you can achieve something a lot simpler with quite
> easy means, and it may be enough for your organization.

serge (sergea@nospam.ehmail.com) writes:
> Like i just replied to Ross, at the moment, I am trying and hoping that
> there is a simpler way to rely on the WHERE Condition and using
> some custom field or something in the sysobjects table to decide whether
> that SP is one of our own SP and not delete it.

There is no such custom field. Possibly you could add some condition which
looked in syscomments for things you recognize, but that would be completely
bizarre to do.

You would have to have list of known procedures to delete. Then again, that
is not very difficult to make effecient:

CREATE PROCEDURE drop_till_you_bop @procs ntext AS
DECLARE @proc sysname
DECLARE drop_cur INSENSITIVE CURSOR FOR
SELECT nstr FROM iter_charlist_to_tbl(@procs, DEFAULT) i
JOIN sysobjects o ON i.nstr = o.name
WHERE o.xtype = 'P'
OPEN drop_cur
WHILE 1 = 1
FETCH drop_cur INTO @proc
IF @@fetch_status <> 0
BREAK
EXEC ('DROP PROCEDURE ' + @proc)
END
DEALLOCATE drop_cur

iter_charlist_to_tbl is on
http://www.sommarskog.se/arrays-in-s...ist-of-strings

--
Erland Sommarskog, SQL Server MVP, esquel@sommarskog.se

Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techinf...2000/books.asp

As already mentioned, a manifest and/or version control to probably the best
long term solution. In the interim. you might consider adding extended
properties to the objects you own so that you can more easily identify
these. You'll need to include the extended properties in your DDL scripts.

CREATE PROC MyProcedure
AS
SELECT 'MyProcedure'
GO

EXEC sp_addextendedproperty
'OwnedBy',
'MyApplication',
'USER',
'dbo',
'PROCEDURE',
'MyProcedure'
GO

--this
SELECT *
FROM sysobjects o
JOIN ::fn_listextendedproperty(
'OwnedBy',
'USER',
'dbo',
'PROCEDURE',
NULL,
NULL,
NULL
) ep ON o.name = ep.objname
WHERE o.xtype = 'P' AND
ep.value = 'MyApplication'

--
Hope this helps.

Dan Guzman
SQL Server MVP

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

Thanks again for the post. I'll have to look into this more closely
in the next few days (hopefully).

"Erland Sommarskog" <esquel@sommarskog.se> wrote in message
news:Xns95636012ECE1Yazorman@127.0.0.1...
> serge (sergea@nospam.ehmail.com) writes:
> > Like i just replied to Ross, at the moment, I am trying and hoping that
> > there is a simpler way to rely on the WHERE Condition and using
> > some custom field or something in the sysobjects table to decide whether
> > that SP is one of our own SP and not delete it.
>
> There is no such custom field. Possibly you could add some condition which
> looked in syscomments for things you recognize, but that would be
completely
> bizarre to do.
>
> You would have to have list of known procedures to delete. Then again,
that
> is not very difficult to make effecient:
>
> CREATE PROCEDURE drop_till_you_bop @procs ntext AS
> DECLARE @proc sysname
> DECLARE drop_cur INSENSITIVE CURSOR FOR
> SELECT nstr FROM iter_charlist_to_tbl(@procs, DEFAULT) i
> JOIN sysobjects o ON i.nstr = o.name
> WHERE o.xtype = 'P'
> OPEN drop_cur
> WHILE 1 = 1
> FETCH drop_cur INTO @proc
> IF @@fetch_status <> 0
> BREAK
> EXEC ('DROP PROCEDURE ' + @proc)
> END
> DEALLOCATE drop_cur
>
> iter_charlist_to_tbl is on
> http://www.sommarskog.se/arrays-in-s...ist-of-strings
>
> --
> Erland Sommarskog, SQL Server MVP, esquel@sommarskog.se
>
> Books Online for SQL Server SP3 at
> http://www.microsoft.com/sql/techinf...2000/books.asp

Looks interesting. Having extended properties to the SP.
I'll have to investigate this further, hopefully soon.

Thank you again.


"Dan Guzman" <guzmanda@nospam-online.sbcglobal.net> wrote in message
news:rnh1d.10373$yp2.9988@newssvr30.news.prodigy.c om...
> As already mentioned, a manifest and/or version control to probably the
best
> long term solution. In the interim. you might consider adding extended
> properties to the objects you own so that you can more easily identify
> these. You'll need to include the extended properties in your DDL
scripts.
>
> CREATE PROC MyProcedure
> AS
> SELECT 'MyProcedure'
> GO
>
> EXEC sp_addextendedproperty
> 'OwnedBy',
> 'MyApplication',
> 'USER',
> 'dbo',
> 'PROCEDURE',
> 'MyProcedure'
> GO
>
> --this
> SELECT *
> FROM sysobjects o
> JOIN ::fn_listextendedproperty(
> 'OwnedBy',
> 'USER',
> 'dbo',
> 'PROCEDURE',
> NULL,
> NULL,
> NULL
> ) ep ON o.name = ep.objname
> WHERE o.xtype = 'P' AND
> ep.value = 'MyApplication'