[thelist] ADOX question

Dave-

Use "sp_helptext <your_stored_proc>" to see all the various versions and
code associated with a particular sp.  Example scenario:

You have add_event;1
You want to know if there are more versions of that sp
"sp_helptext add_event" will tell you

If you want to define more versions of a stored proc, just create them with
the version number appended to the name:

create procedure my_proc;1 as
	...

go

create procedure my_proc;2 as
	...

etc.

What this does is essentially just group the stored procs together by name.
You can call each individually using the ;[n] notation.  If you need to drop
the "my_proc" sp above, "drop procedure my_proc" will remove both versions,
since they're contained in the same group.

Can you tell me where this version info is showing up?  Are you using
sp_helptext to display the procs right now?  If you look through EM at the
database itself, do you see version numbers on the names of the procedures?

Chris Blessing
webguy@mail.rit.edu
http://www.330i.net

> >add_event;1 is the 1st version of the add_event sp.  You could
> also have an
> >add_event;2 and ;3 and so-on.
>
> Interesting, I didn't know it did that. How do I update the
> version number?
> I've modified most of them over time, but they all still report
> version 1...
>
> >How did you create these stored procs?
>
> Manually using Query Analyzer, e.g. "create proc foobar @yadda yadda
> yadda..."
>
> There's about 250 of them.
>
> I'm trying to gather metrics on them and find similarities, what is and
> isn't being used, etc so I can trim the database. I figure I can probably
> lose 25% of the procedures due to redundancy, testing/experimenting, or
> because they are orphaned and no longer used.
>
> Thanks for your help, it answered one of my questions! :)
> -dave