Object modified timestamp SAP ASE 16

[rafaelbahia]

How do I return the last modified date for an object in the database? Say that I want to grab a list of all objects added/changed in the database in the last week, how do I do that? The list needs to include views, tables, procedures, functions; basically all objects that can suffer DDL changes.

I'm using Adaptive Server Enterprise/16.0 SP04 PL01/EBF 29704 SMP/P/x86_64/SLES 12.4/ase160sp04pl01x/3473/64-bit/FBO/Tue Jul 20 07:58:11 2021

[Mark_A_Parsons · Accepted solution]

There is no 'easy' way to obtain what you're looking for.

While some system tables include a crdate (creation date) column a modification date column just plain doesn't exist (in system tables).

In order of viability ...

The typical approach for capturing DDL changes is to enable auditing for the DDL operations of interest.  This in turn requires a) the creation of the sybsecurity database and b) a means of periodically extracting and archiving (from the sybsecurity database) audit items of interest. If you need to know the identity of the login that performed the DDL operation then you'll definitely want to look into auditing.

A second option would be a custom process that a) periodically (daily? weekly?) takes a snapshot (eg, bcp from a custom set of views) of the appropriate system tables and b) provides the ability to compare a pair of such snapshots to generate a list of diff's that took place in the period between when the two snapshots were taken. [Doable as long as you give some thought to storing the info in a way that's easily processed by the comparison step; I went this route on a replication project where I had to keep track of DDL modifications in Oracle (primary) and HANA (replicate) databases - diffs between 2x Oracle snapshots, diffs between 2x HANA snapshots, diffs between corresponding Oracle and HANA snapshots, diffs between the Oracle/HANA snapshots and associated Data Assurance comparison jobs.] [Personally, since I've done this before, I'd probably go with this option instead of auditing; once you get the design figured out it's much less headache than maintaining the sybsecurity database.]

A third option (a variation of the second option) would be the periodic process to reverse engineer your DDL (eg, ddlgen) into SQL scripts; a simple diff of the SQL scripts would show you differences.

A fourth option would be to constantly scan the monSysSQLText (MDA) table for DDL commands of interest but  there's a near-100% chance of missing/losing some of those commands unless you know with 100% certainty that all DDL commands are only issued during periods of relative inactivity in the dataserver thus giving you plenty of time to poll monSysSQLText.

A fifth option that comes to mind (though not tested), and assuming you're replicating out of the database in question, would be a custom function string to convert replicated DDL commands into text inserted into a replicate database table. [Probably not doable but could be interesting if you've got a few free hours for testing purposes ...]

A sixth (and last!) option that comes to mind is to enforce the application of all DDL through a change-control environment.  Come on! Who are we kidding? That's like herding cats ... and likely why you're here asking this question, right?

[rafaelbahia · Accepted solution]

Thank you for the detailed answer, Mark. We do have auditing enabled and with a bit of tweaking we can use it to address our need. I'll try that approach.

[rafaelbahia · Accepted solution]

... and btw you're absolutely right about herding cats in the sixth solution! The whole idea of this project is to commit the changes to a source control so we can easily identify what changed. Ideally we would have a process where changes are commited to the change control and reviewed first before pushing things to production but we're not there yet (this requires a well rounded process to work). Thanks again for the help.