Skip to content

Instantly share code, notes, and snippets.

@kurochan
Created May 29, 2022 09:11
Show Gist options
  • Save kurochan/2eb72ee289f90476f7a0ce86e71a0162 to your computer and use it in GitHub Desktop.
Save kurochan/2eb72ee289f90476f7a0ce86e71a0162 to your computer and use it in GitHub Desktop.
select
TABLE_CATALOG AS DB_NAME,
TABLE_SCHEMA AS SCHEMA_NAME,
TABLE_NAME AS TABLE_NAME,
COLUMN_NAME AS COLUMN_NAME,
COLUMN_DEFAULT AS DEFAULT_VALUE,
IS_NULLABLE AS IS_NULLABLE,
CASE WHEN DATA_TYPE in (
'VARCHAR', 'CHAR', ' CHARACTER', 'STRING',
'TEXT'
) THEN CONCAT(
DATA_TYPE, '(', CHARACTER_MAXIMUM_LENGTH,
')'
) WHEN DATA_TYPE in ('BINARY', 'VARBINARY') THEN concat(
DATA_TYPE, '(', CHARACTER_OCTET_LENGTH,
')'
) WHEN DATA_TYPE in ('NUMBER', 'DECIMAL', 'NUMERIC') THEN concat(
DATA_TYPE, '(', NUMERIC_PRECISION,
',', NUMERIC_SCALE, ')'
) ELSE DATA_TYPE END AS DATA_TYPE,
COMMENT AS COMMENT
from
information_schema.columns
where
table_schema != 'INFORMATION_SCHEMA'
order by
SCHEMA_NAME,
TABLE_NAME,
ordinal_position
Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment