I need to automate a process which will generate a report for newly created tables in a day (in production)
I've tried with below query:
SELECT DatabaseName, TableName, CreateTimeStamp,LastAlterTimeStamp
WHERE CreateTimeStamp = Current_TimeStamp - interval '1' day
But it is listing those tables also which full refresh (Daily drop and create).
My requirement is show only those tables which are created only today and want to exclude these full refersh tables.
Kindly advise on how i can proceed with this.
If a table is 'dropped and created' daily then it will be 'new' every day - hence they're included.
- don't drop and create daily (IMHO preferable solution - see here for why DELETE - don't DROP)
- or have your own table which holds a list of tables "that were there yesterday" and then compare the content of today's dictionary with yesterdays.
(And can I suggest that you start using DBC.TablesV and not DBC.Tables.)