2

I want to find how many columns from the table are timestamp columns. I have tried a simple query like below to retired columns with the timestamp by hardcoding the name of the columns

SELECT time1, time2 FROM givenTable;

but I don't want to hard code the name of the timestamp columns. Is there any way in SQL which tell me the name of the columns in the table is the timestamp column?

LukStorms
  • 28,916
  • 5
  • 31
  • 45
NoviceProgrammer
  • 37
  • 1
  • 5

3 Answers3

4
select * from SYS.ALL_TAB_COLUMNS
where table_name = 'TABLE_XYZ'
and owner = USER
and data_type like 'TIMESTAMP%'

you should use like by filtering on data_type, because you could also have a timestamp with precision like TIMESTAMP(6)

hotfix
  • 3,376
  • 20
  • 36
  • If there are multiple tables with the same name belonging to different users then this will not differentiate between them. Either add a filter on `owner` or, for the current user, use `USER_TAB_COLUMNS`. – MT0 Aug 09 '18 at 12:14
3

For oracle the USER_TAB_COLUMNS will provide all the details with respect to the table.

SELECT table_name, column_name, data_type, data_length
FROM USER_TAB_COLUMNS
WHERE table_name = 'MYTABLE'
Ramji
  • 375
  • 2
  • 14
2

Try this.

SELECT   column_name, data_type FROM all_tab_columns where table_name = 'givenTable';

identify exact data type for timestamp and add more accurate filter.

e.g  and upper(data_type) like '%TIMESTAMP%'
Deepesh kumar Gupta
  • 884
  • 2
  • 11
  • 29
  • 1
    If there are multiple tables with the same name belonging to different users then this will not differentiate between them. Either add a filter on `owner` or, for the current user, use `USER_TAB_COLUMNS`. – MT0 Aug 09 '18 at 12:15
  • then add owner as well in filter – Deepesh kumar Gupta Aug 09 '18 at 12:31