Showing columns of a table
select
sch.name sch_name,
tab.name tab_name,
col.name col_name,
-- col.user_type_id,
ust.name user_type
-- col.system_type_id,
-- syt.name system_type
from
sys.schemas sch join
sys.tables tab on sch.schema_id = tab.schema_id join
sys.columns col on tab.object_id = col.object_id join
sys.types ust on col.user_type_id = ust.user_type_id -- join
-- sys.types syt on col.system_type_id = syt.system_type_id
where
sch.name = 'dbo' and
tab.name = 'foo_bar'
order by
col.column_id
;
Find tables by column name
A similar query finds all tables that have a column whose name matches a given pattern:
select
tab.name table_,
sch.name schema_,
col.name column_
from
sys.tables tab join
sys.columns col on tab.object_id = col.object_id join
sys.schemas sch on tab.schema_id = sch.schema_id
where
col.name like '%ABC%';