Table Creation Data

{question}

What is the best way to determine a table or view creation date?

{question} 

{answer}

 NuoDB offers two ways to determine the Date that a table or view was created:
  1. Use DDL-audit logging.  For more information, click here
  2. Use a trigger on an 'after creating a table' event. The trigger will insert to a table that you keep a DDL audit. The DDL-audit table may include the following columns: object_name, schema_name, and the creation_date.

Here is an example:

create table audit_log ( TABLENAME string, SCHEMA string, TYPE varchar (16), CREATION_DATE DATE);

SET DELIMITER @
CREATE TRIGGER ddl_trigger_after_create_table ON DATABASE AFTER CREATE TABLE AS
INSERT INTO audit_log VALUES (DDL_OBJECT_NAME, DDL_SCHEMA_NAME, DDL_OPERATION_TYPE, now());
END_TRIGGER;
@

Test it:

create table tab1 (ID number, description string);

select * from audit_log;

TABLENAME SCHEMA    TYPE         CREATION_DATE
----------      -------     ------------    --------------

TAB1                USER     create_table    2020-04-10

 

 

 If you have any questions, please reach out by clicking here

{answer}

Have more questions? Submit a request

Comments