I have a requirement to find out "new" tables that we created in one DB-say abc, in last one month. I querried dbc.tables and listed down the tables based on createtimestamp field that are in DB-abc.
Now, i have some tables that were already existed and they were dropped and recreated as there were some changes in the DDL of these table. Those table also have a create time stamp of last month. They too are being counted in my previous approach.
Is there a way to filter such tables and get only the tables that we created new and they never existed.
Its little urgent, so a quick reply will be highly appreciated.
As per my knowledge once we dropped existing table, it will automatically removes all the information from the data dictionary.
So once we dropped and recreated the table with the same name , the latest time only will be updated in DBC.Tables.
Forum friends, Please let me know if I am wrong here!
That is correct because data dictionary keeps track of what exists in the DB. Once the tables are dropped the dictionary entried are removed.
If you want to keep an AUDIT of tables then probably you have to write scripts to extract the required information from DBQL keeping track of the tables that are dropped and created...
thanks qaisar. Now is it possible to get those details from dbql ?
for a long term solution, what we can do is, before dropping any table . i will create a metadata table and insert a row with the details of table that is being dropped and recreated.
There is a DBQLOGTBL you can refer to. See the column QueryText - this contains all the sqls that have been executed. You need to filter out the records by selecting only CREATE and DROP table statements...