This section describes the crdate column of the system catalogs.
Adaptive Server version 12.5 includes the following new columns in the system catalogs:
sysusages.crdate
sysindexes.crdate
These system catalogs store creation dates for some objects, and are useful for tracking schema changes at a high level.
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 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.
