Procedure last updated

[bjanker77]

Hi.

Is there a way to list all procedures and functions with a timestamp for "LastUpdated". We have a lot of code in the database and we need to create a script for our customers. And I just want to include the procedures/functions that has been updated since last script.

br,

Bjarne

[Breck_Carter]

The column SYSOBJECT.creation_time WHERE SYSPROCEDURE.object_id = SYSOBJECT.object_id will give you the UTC TIMESTAMP when the procedure was CREATEd, but not when it is ALTERed.

However, if you always DROP and re-CREATE your procedures, SYSOBJECT.creation_time will give you what you are asking for.

Note that the object_id value stays the same in this example, BUT you may not be able to count on it surviving a DROP CREATE.

CREATE PROCEDURE xxx() BEGIN MESSAGE 'Hello' TO CONSOLE; END;

SELECT SYSPROCEDURE.proc_name  AS procedure_name,
       SYSOBJECT.object_id     AS object_id,
       SYSOBJECT.creation_time AS last_updated_UTC_TIMESTAMP
  FROM SYSPROCEDURE INNER JOIN SYSOBJECT
          ON SYSPROCEDURE.object_id = SYSOBJECT.object_id
 WHERE SYSPROCEDURE.proc_name = 'xxx';

WAITFOR DELAY '00:00:10';

DROP PROCEDURE xxx;
CREATE PROCEDURE xxx() BEGIN MESSAGE 'World' TO CONSOLE; END;

SELECT SYSPROCEDURE.proc_name  AS procedure_name,
       SYSOBJECT.object_id     AS object_id,
       SYSOBJECT.creation_time AS last_updated_UTC_TIMESTAMP
  FROM SYSPROCEDURE INNER JOIN SYSOBJECT
          ON SYSPROCEDURE.object_id = SYSOBJECT.object_id
 WHERE SYSPROCEDURE.proc_name = 'xxx';

procedure_name,object_id,last_updated_UTC_TIMESTAMP
'xxx',32748,'2020-05-28 09:24:47.000'

procedure_name,object_id,last_updated_UTC_TIMESTAMP
'xxx',32748,'2020-05-28 09:24:57.000'

---

HOWEVER...

I very strongly suggest you maintain the original source code for all your stored procedures, triggers, events and services in text files outside the database, and always use those source code text files to update (compile) the objects into the database.

Then you can apply the same source control processes you use for application program code; e.g., keep track of every single modification.

Then, you will be able to automate (or semi-automate) the process of building the update script for customers.

[VolkerBarth]

I very strongly suggest you maintain the original source code for all your stored procedures, triggers, events and services in text files outside the database, and always use those source code text files to update (compile) the objects into the database. [...]

An upvote isn't enough to express how much I second that suggestion!

[fvestjens]

There's an option in Sybase Central to compare 2 databases. Maybe that could help.

[chris_keating]

This should work:

select 
    sp.proc_name, 
    so.creation_time 
from sysprocedure sp join sysobject so
    on sp.object_id = so.object_id 
where
    so.object_type = 6