last updated date on stored procs

Hi
I've change quite a few stored procs and wondered if there's any way of
getting a last updated date. I only want to upload the changed procs to the
live server and don't want to transfer all of them.
Cheers
JamesThere is no easy way to do this because there no 'last changed date' stored
with the sp... There is a schema_ver field which changes when the sp is
altered... You would have to keep a copy yourself in a table and compare it
to the field in sysobjects...You could also add local variables to each SP
with a last changed date that must be incremented by the changer...but that
is all a pain in the butt... There are programs like(Red Gate ) which can
compare differences and let you know... But the bottom line is that you get
no help from SQL...
"James Brett" <james.brett@.unified.co.uk> wrote in message
news:%233A4OlYiDHA.2748@.TK2MSFTNGP11.phx.gbl...
> Hi
> I've change quite a few stored procs and wondered if there's any way of
> getting a last updated date. I only want to upload the changed procs to
the
> live server and don't want to transfer all of them.
> Cheers
> James
>|||Not if you are altering procedures. Only the creation date
is available. SQL Server doesn't track the modification
dates. You could do something like use a third party tool to
compare the differences between development and production
to find the stored procedures that are different between the
two environments. But you can easily have modified objects
in dev that really aren't intended to be moved to the live
server so you have to be careful using this approach.
Red Gate has tools to compare databases:
http://www.red-gate.com/
-Sue
On Fri, 3 Oct 2003 09:41:20 +0100, "James Brett"
<james.brett@.unified.co.uk> wrote:
>Hi
>I've change quite a few stored procs and wondered if there's any way of
>getting a last updated date. I only want to upload the changed procs to the
>live server and don't want to transfer all of them.
>Cheers
>James
>|||Hey Wayne,
FYI...Didn't mean to step on your response or anything. It
will be nice when the servers are back in shape and we can
see other replies posted with less latency before posting
our own.
The joys of Swen...
-Sue
On Fri, 3 Oct 2003 08:18:21 -0400, "Wayne Snyder"
<wsnyder@.computeredservices.com> wrote:
>There is no easy way to do this because there no 'last changed date' stored
>with the sp... There is a schema_ver field which changes when the sp is
>altered... You would have to keep a copy yourself in a table and compare it
>to the field in sysobjects...You could also add local variables to each SP
>with a last changed date that must be incremented by the changer...but that
>is all a pain in the butt... There are programs like(Red Gate ) which can
>compare differences and let you know... But the bottom line is that you get
>no help from SQL...
>"James Brett" <james.brett@.unified.co.uk> wrote in message
>news:%233A4OlYiDHA.2748@.TK2MSFTNGP11.phx.gbl...
>> Hi
>> I've change quite a few stored procs and wondered if there's any way of
>> getting a last updated date. I only want to upload the changed procs to
>the
>> live server and don't want to transfer all of them.
>> Cheers
>> James
>>
>

Last Date Stored Proc Updated??

Is there such a date/time?

I see the Created date on the list of stored procs, but really want a
Date Last Updated. After changing code for 3 hours, I tend to forget
which procs I've worked on, and which need to be move to production.
any simple way to keep track of the last procs played with?

thanks in advance...
john@.ViridianTech.comDo you mean you've used ALTER PROC and you want to see when it was
ALTERed? If so, then this isn't currently possible in MSSQL (although
it is in SQL 2005).

In any case, you should hopefully be using some sort of source control
system to store your object creation scripts, and a deployment process
which would help to track your changes. You might also want to consider
a database comparison tool, which can quickly show you the differences
between databases.

Simon|||Actually, we're not using any source control on the stored procs, like
we do on the project source code. How would you do that? is there
something in MSSQL for that? the .NWT IDE for VB make it easy to
integrate with Source Safe, what do you use for stored procs, views and
table creation scripts? You advice would be much appreciated.

john|||<John@.ViridianTech.com> wrote in message
news:1120484863.856415.206040@.o13g2000cwo.googlegr oups.com...
> Actually, we're not using any source control on the stored procs, like
> we do on the project source code. How would you do that? is there
> something in MSSQL for that? the .NWT IDE for VB make it easy to
> integrate with Source Safe, what do you use for stored procs, views and
> table creation scripts? You advice would be much appreciated.
> john

Personally, I simply check code out of VSS and work with it in Query
Analyzer - there's no source control integration in the MSSQL tools
themselves. I believe Visual Studio has some sort of support for SQL code
and VSS, although I don't use VS often myself, so I may be wrong about that.

Even using just QA and VSS, a few scripts can make things easier - the
Customize menu in QA allows you to pass a few useful parameters to batch
files or other programs, so it's not too difficult to script checking in and
out of VSS (SQL 2005 has source control integration in the Management
Studio).

Erland has an interesting toolset for working with SQL source code and VSS,
written in Perl, which might be worth looking at if you want to develop your
own solution or just need some ideas about managing and deploying SQL code:

http://www.abaris.se/abaperls/index.html

Simon|||(John@.ViridianTech.com) writes:
> Actually, we're not using any source control on the stored procs, like
> we do on the project source code. How would you do that?

You just do it!

> is there something in MSSQL for that? the .NWT IDE for VB make it easy
> to integrate with Source Safe,

Hrmpf! Nothing in Visual Studio is easy. (I understand less and less of
it for each new version they come out with.) And in our shop, you may
use the SourceSafe integration for the VB code, but if you mess up,
our build people will tell you to stop doing it.

The absolutely best too to work with SourceSafe is the VSS Explorer.

> what do you use for stored procs, views and
> table creation scripts? You advice would be much appreciated.

Actually, we don't even use QA for editing, but use Textpad instead,
simply because it's a better editor.

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

Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techin.../2000/books.asp|||If you're not going to have access to a source control tool or
methodology any time soon, here's a workaround; but there's nothing
foo-proof about this, so you still have to be careful:

Just drop and create your stored procedures instead of altering them.
This modifies the crdate in sysobjects, which is reflected in
Enterprise Manager.

drop procedure proc_mytest
go

create procedure proc_mytest
as
< whatever
hth,

victor dileo|||vjdileo (vic_technews@.yahoo.com) writes:
> If you're not going to have access to a source control tool or
> methodology any time soon, here's a workaround; but there's nothing
> foo-proof about this, so you still have to be careful:
> Just drop and create your stored procedures instead of altering them.
> This modifies the crdate in sysobjects, which is reflected in
> Enterprise Manager.

And there is actually a way of detecting that a procedure have
been altered. sysobjects.schema_ver is incremented with 16 each
you alter the procedure.

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

Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techin.../2000/books.asp|||Guys, there really is a great way of working with your SQL code in just
the same way as you do for your application code and it's called DB
Ghost (www.dbghost.com). It's the ONLY SQL Server tool on the market
that can build a brand new database from a set of object creation
scripts taking care of all dependencies. This database is then used by
D Ghost to compare and upgrade any target database.

The upshot of our approach is that you have ALL database objects as
'create' scripts under source control and just modify those. You work
in a familiar manner and the source control system becomes your friend
rather than a necessary evil. Most other approaches to having SQL in
source control involve having to do two things for each update:
1. Update the create script.
2. Write an ALTER script.

DB Ghost does away with the second of these not just for stored
procedures but for EVERY database object.

Honestly, you may think this approach cannot work but it does and our
customers use phrases like 'religious experience' when they talk about
it. You just have to take the time to 'get' it...|||We just do a generate sql script for all procs (separate file for each)
and add them to a source safe project and use that through visual
studio. Pretty easy and works great. If you make the project a database
project in visual studio it will let you execute them from there as
well.