Created
April 28, 2022 04:57
-
-
Save AnanthaRajuC/9bcbf8dfec6731a44d4f8de4151372e5 to your computer and use it in GitHub Desktop.
all-tables-in-mysql-schema
This file contains bidirectional Unicode text that may be interpreted or compiled differently than what appears below. To review, open the file in an editor that reveals hidden Unicode characters.
Learn more about bidirectional Unicode characters
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') | |
-- uncomment line below for current database only | |
-- and tab.table_schema = database() | |
-- uncomment line below and provide specific database name | |
and tab.table_schema = 'jhac' | |
order by tab.table_name, | |
col.ordinal_position; |
Sign up for free
to join this conversation on GitHub.
Already have an account?
Sign in to comment