CODEWITHSUNDEEP
mysqlsql-timestamp
hbadger19042
hbadger19042

Reputation: 161

How to get all the column names whose datatypes are timestamp in MySQL?

In MySQL, I have tables with many columns. How can I get all the column names with the timestamp data type? I want to do it for all the tables in a database.

The data type of timestamp columns can be TIMESTAMP or TIMESTAMP(6).

The output would be something like below:

table_name | column_name | data type    |
-----------------------------------------
tabe01     | column01    | timestamp    |
tabe02     | column03    | timestamp(6) |
tabe03     | column02    | timestamp    |
tabe04     | column05    | timestamp    |

Upvotes: 1

Views: 1348

Answers (1)

finiteloop
finiteloop

Reputation: 493

@kevin012 would something like this work for your purposes? You may need to play around with it a bit to get it to your liking but hopefully this will work for you.

select tab.table_schema as database_schema,
    tab.table_name as table_name,
    col.ordinal_position as column_id,
    col.column_name as column_name,
    col.data_type as data_type,
    case when col.numeric_precision is not null
        then col.numeric_precision
        else col.character_maximum_length end as max_length,
    case when col.datetime_precision is not null
        then col.datetime_precision
        when col.numeric_scale is not null
        then col.numeric_scale
            else 0 end as 'precision'
from information_schema.tables as tab
    inner join information_schema.columns as col
        on col.table_schema = tab.table_schema
        and col.table_name = tab.table_name
where tab.table_type = 'BASE TABLE'
    and tab.table_schema not in ('information_schema','mysql',
        'performance_schema','sys')
and col.data_type = 'timestamp'
    -- uncomment line below for current database only
    -- and tab.table_schema = database() 
    -- uncomment line below and provide specific database name
    -- and tab.table_schema = 'your_database_name' 
order by tab.table_name,
    col.ordinal_position;

Modified from source: https://dataedo.com/kb/query/mysql/list-table-columns-in-database

Upvotes: 5

Related Questions