how to check the creation date of tables in SQL?

Question

The question is:

List of the DW1_ tables from the USER_OBJECTS with table names, creation date and time in ISO standard.

that's what I have so far, but it does not work.

select table_name, to_char(create_date, 'yyyy-mm-dd') from all_tables 
where table_name like 'DW1%'
group by table_name;

It said create_date is invalid identifier.

Can anyone help me with this question?

Create Date is not a field in all_tables from my understanding... — sgeddes
– sgeddes, Commented Mar 15, 2013 at 3:04
should be from user_objects not all_tables, but I still dont know which method I could use here. — user2169562
– user2169562, Commented Mar 15, 2013 at 3:06
select object_name, to_char(created, 'yyyy-mm-dd') "Creation date" from user_objects where object_name like 'DW1%'; thx everyone anyways — user2169562
– user2169562, Commented Mar 15, 2013 at 3:10
If you're looking for table names that begin with "DW1_", what you need is: table_name like 'DW1_%' escape '\' — David Aldridge
– David Aldridge, Commented Mar 15, 2013 at 8:17

sgeddes · Accepted Answer · 2013-03-15 03:09:15Z

7

Is this what you're looking for?

select object_name, created 
from user_objects 
where object_name LIKE 'DW1%' 
    and object_type = 'TABLE';

SQL Fiddle Demo

answered Mar 15, 2013 at 3:09

sgeddes

62.9k7 gold badges67 silver badges85 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

t0r0X Over a year ago

Why do I prefer this answer, using user_objects, compared to those using dba_objects? Because often there are no read permissions on dba_objects, but on user_objects.

Art · Accepted Answer · 2013-03-15 12:45:11Z

1

select object_name, to_char(created, 'yyyy-mm-dd') AS created -- 'AS' is optional
  from user_objects 
 where object_name like 'EMP%' -- replace with your table
/

answered Mar 15, 2013 at 12:45

Art

5,8222 gold badges24 silver badges22 bronze badges

Comments

rs. · Accepted Answer · 2013-03-15 03:07:56Z

0

try this

SELECT object_name tablename, created FROM dba_objects
WHERE object_name like 'DW1%'
and object_type = 'TABLE'

answered Mar 15, 2013 at 3:07

rs.

27.6k13 gold badges71 silver badges93 bronze badges

Collectives™ on Stack Overflow

how to check the creation date of tables in SQL?

3 Answers 3

1 Comment

Comments

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

1 Comment

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related