Link to home
Create AccountLog in
Avatar of Ted Penner
Ted PennerFlag for United States of America

asked on

Expand SQL statement to include the last date and time that each table was changed

This follows an earlier discussion started here https://www.experts-exchange.com/dashboard/#/questions/29264896. I am looking to expand this SQL statement.

select TABLES.TABLE_SCHEMA,TABLES.TABLE_NAME,TABLE_ROWS,group_concat(COLUMN_NAME) COLUMNS,group_concat(DATA_TYPE) DATATYPES  from information_schema.TABLES join information_schema.COLUMNS on TABLES.TABLE_NAME = COLUMNS.TABLE_NAME and TABLES.TABLE_SCHEMA = COLUMNS.TABLE_SCHEMA   where TABLES.TABLE_SCHEMA like '%' group by TABLE_SCHEMA,TABLE_NAME

Open in new window

I would to change the output in the screenshot below to include the following.

1) The date and time of the last modification for each table in this format Mon Sep 18, 2023 at 1:46 p.m (UTC-5)

2) Each field should have its datatype in parenthesis next to it, thus eliminating the need for the extra datatypes column.

3) Ideally, we should have the following simplified column names for the output

SCHEMA
TABLES
RECORDS
FIELDS


User generated image


ASKER CERTIFIED SOLUTION
Avatar of Tomas Helgi Johannsson
Tomas Helgi Johannsson
Flag of Iceland image

Link to home
membership
Create a free account to see this answer
Signing up is free and takes 30 seconds. No credit card required.
See answer
Avatar of Ted Penner

ASKER

Thanks again for the help Tomas

I ran them both and this screencast shows my experience https://www.screencast.com/t/3EpKbKJG8
Avatar of slightwv (䄆 Netminder)
slightwv (䄆 Netminder)

>>select TABLES.TABLE_SCHEMA SCHEMA,TABLES.TABLE_NAME TABLES

Aliases are the correct way to change column names in a result set.

However, they cannot be reserved words.

You 'can' fix this with double quotes like:
select TABLES.TABLE_SCHEMA "SCHEMA",TABLES.TABLE_NAME "TABLE" ,TABLE_ROWS RECORDS
...

Open in new window


But it is a bad habit to get into and will cause you problems someday.

Best to just use an alias that isn't a reserved word.
If SCHEMA counts as a reserved word then maybe it makes sense to just stick to what the RESERVED_WORDS are and not try to alias at all. How do we simplify so that aliasing is not needed at all and we just take on the reserved words themselves for the column names?

Hi,

You are right. I used reserved word by accident. (even I make silly mistakes once in a while ) :S
I changed the second query in my last comment so that it returns the data.

Best regards,
    Tomas Helgi