Joseph Michael Pesch
VP Programming

SQL Table/Column Schema Select

by 26. February 2010 15:28

select
  sch.name [Schema Name]
, tbl.name [Table Name]
, col.name [Column Name]
, typ.name
  + case when col.system_type_id = 167 then ' (' + convert(varchar, col.max_length) + ')' else '' end
  + case when col.precision + col.scale <> 0 then ' Precision: ' + convert(varchar, col.precision) + '; Scale: ' + convert(varchar, col.scale) else '' end
  [Column Type]
, case col.is_nullable when 1 then 'true' else 'false' end [Allow Null]
from sys.tables tbl
inner join sys.schemas sch
on tbl.schema_id = sch.schema_id
inner join sys.columns col
on tbl.object_id = col.object_id
inner join sys.types typ
on col.system_type_id = typ.system_type_id
where tbl.type = 'U'
order by
  sch.name
, tbl.name
, col.name
 

Tags:

SQL Server

Comments are closed