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 |
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. 2
An upvote isn't enough to express how much I second that suggestion!
(29 May '20, 02:32)
Volker Barth
|
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 |
There's an option in Sybase Central to compare 2 databases. Maybe that could help. |