This is just an aide memoire for a function I use seemingly every week as part of my data integration work. It’s often easier to work through an Excel spreadsheet when you are doing data mapping rather than a SQL tool, as you can search for text and the like to help find a field.

This is just the SQL for doing that for the major SQL databases that you can come across.

Microsoft SQL

select schema_name(tab.schema_id) as schema_name,
tab.name as table_name,
col.column_id,
col.name as column_name,
t.name as data_type,
col.max_length,
col.precision
from sys.tables as tab
inner join sys.columns as col
on tab.object_id = col.object_id
left join sys.types as t
on col.user_type_id = t.user_type_id
order by schema_name,
table_name,
column_id;

Oracle

SELECT *  FROM all_tab_cols ORDER BY table_name, column_name, column_id

DB2

Select c.tabschema as schema_name, c.tabname as table_name, c.colname as column_name, c.colno as position, c.typename as data_type, c.length, c.scale, c.remarks as description, case when c.nulls = ‘Y’ then 1 else 0 end as nullable, default as default_value, case when c.identity =‘Y’ then 1 else 0 end as is_identity, case when c.generated =” then 0 else 1 end as is_computed, c.text as computed_formula from syscat.columns c inner join syscat.tables t on t.tabschema = c.tabschema and t.tabname = c.tabname where t.type = ‘T’ order by schema_name, table_name

MYSQL

select column_name from information_schema.columns
where table_schema = ‘your_db’
order by table_name,ordinal_position