Please be aware that the content in SAP SQL Anywhere Forum will be migrated to the SAP Community in June and this forum will be retired.

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

asked 28 May '20, 03:02

Bjarne%20Anker's gravatar image

Bjarne Anker
745323648
accept rate: 10%


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.

permanent link

answered 28 May '20, 09:32

Breck%20Carter's gravatar image

Breck Carter
32.5k5417261050
accept rate: 20%

2

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!

(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
permanent link

answered 28 May '20, 10:48

Chris%20Keating's gravatar image

Chris Keating
7.8k49128
accept rate: 32%

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

permanent link

answered 29 May '20, 02:16

Frank%20Vestjens's gravatar image

Frank Vestjens
1.3k354765
accept rate: 21%

edited 29 May '20, 02:17

Your answer
toggle preview

Follow this question

By Email:

Once you sign in you will be able to subscribe for any updates here

By RSS:

Answers

Answers and Comments

Markdown Basics

  • *italic* or _italic_
  • **bold** or __bold__
  • link:[text](http://url.com/ "title")
  • image?![alt text](/path/img.jpg "title")
  • numbered list: 1. Foo 2. Bar
  • to add a line break simply add two spaces to where you would like the new line to be.
  • basic HTML tags are also supported

Question tags:

×125
×23

question asked: 28 May '20, 03:02

question was seen: 1,153 times

last updated: 29 May '20, 02:33