cancel
Showing results for 
Search instead for 
Did you mean: 

Procedure last updated

bjanker77
Participant
1,665

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

Accepted Solutions (0)

Answers (3)

Answers (3)

Breck_Carter
Participant

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
Contributor

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
Participant

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

chris_keating
Product and Topic Expert
Product and Topic Expert

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