Tracking create dates for columns

This section describes the crdate column of the system catalogs.

Adaptive Server version 12.5 includes the following new columns in the system catalogs:

These system catalogs store creation dates for some objects, and are useful for tracking schema changes at a high level.


sysdatabases.crdate and sysusages.crdate

sysdatabases.crdate stores the database’s creation date. sysusages.crdate includes information about when the databases were altered by alter database, which allows you to track the growth rate of your database.


sysobjects.crdate and sysindexes.crdate

sysobjects.crdate reports the date that objects (tables, views, procedures, defaults, rules, constraints, and so on) were created. The sysobjects.crdate remains fixed through the life of the object.

For example, the following query displays the creation date for objects, ordered by type:

select id, type, name, crdate from sysobjects order by type
 id          type  name                       crdate
----------- ----   -------------------------- --------------------------
288001026    P     sp_procxmode               Jun  8 2001  9:43AM
304001083    P     sp_validlang               Jun  8 2001  9:43AM
320001140    P     sp_getmessage              Jun  8 2001  9:43AM
...
240000855    U     spt_ijdbc_conversion       Jun  8 2001  9:43AM
272000969    U     ijdbc_function_escapes     Jun  8 2001  9:43AM

Objects such as unique constraints, and referential constraints created as part of a create table statement have the same crdate in sysobjects and sysindexes.

You can track creation dates of constraints added by alter table subsequent to the creation of the table using the sysobjects.crdate for the constraint’s tableid, which you can retrieve from sysconstraints.

sysindexes.crdate displays the date you re-created an index. For a newly created index, sysindexes.crdate reports the date when you built the index. If you rebuild existing indexes, sysindexes.crdate reports this date. For example, you drop and rebuild a clustered index, and then rebuilt all non-clustered indexes, the crdate value in sysindexes is the same for all indexes you rebuilt as part of this operation. Other DDL operations like reorg rebuild, alter table lock change, or alter table to modify a schema along with data copy are performed, will also cause indexes to be rebuilt.

For DOL tables, as the index ID entry 0 is never changed, the crdate value in sysobjects will be the same as that in sysindexes, unless the table’s locking scheme was modified from allpages to datarows or datapages.

If the sysindexes.crdate is more recent than sysobjects.crdate, it means that the index was either built or rebuilt much after the initial table creation.