on 2020 May 28 4:02 AM
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
Request clarification before answering.
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.
You must be a registered user to add a comment. If you've already registered, sign in. Otherwise, register and sign in.
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!
There's an option in Sybase Central to compare 2 databases. Maybe that could help.
You must be a registered user to add a comment. If you've already registered, sign in. Otherwise, register and sign in.
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
You must be a registered user to add a comment. If you've already registered, sign in. Otherwise, register and sign in.
User | Count |
---|---|
96 | |
11 | |
9 | |
9 | |
7 | |
5 | |
4 | |
4 | |
4 | |
4 |
You must be a registered user to add a comment. If you've already registered, sign in. Otherwise, register and sign in.