How to find out the table create date?

Database
Enthusiast

How to find out the table create date?

Please tell me how could I find out which tables or views recently created or changed? Is there a timestamp for it? Thanks!
5 REPLIES
Enthusiast

Re: How to find out the table create date?

Perhaps a query like this is what you want:

-- List all tables that have been created or altered within the last 10 days

select DatabaseName, TableName, LastAlterTimeStamp
from dbc.tables
where LastAlterTimeStamp > Current_TimeStamp - interval '10' day;
Enthusiast

Re: How to find out the table create date?

hope it would suffice to solve your prob....

select tablename, databasename, createtimestamp, lastaltertimestamp from dbc.tables where tablekind in ('T','V')
order by 4 desc
Enthusiast

Re: How to find out the table create date?

Thank you very much!

Re: how to check only newly created tables for today and not altered tables

Hi All,

 

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
FROM dbc.tables
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.

 

Regards,

Bhavini.

Senior Apprentice

Re: how to check only newly created tables for today and not altered tables

Hi,

If a table is 'dropped and created' daily then it will be 'new' every day - hence they're included.

 

Either

- 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.)

 

Cheers,

Dave

 

Ward Analytics Ltd - information in motion
www: http://www.ward-analytics.com