desc

SQL Server 2008 R2 (SP1)

select coalesce(cast(i1.key_ordinal as char(2)), '') as PK,
       coalesce(cast(i2.key_ordinal as char(2)), '') as UQ,
       y1.name, c1.max_length, c1.precision, c1.scale,
       case when c1.is_nullable = 0 then 'NOT NULL' else '' end as nullable,
       coalesce(cast(d1.definition as nvarchar(20)), '') as [default],
       c1.name
  from sys.columns as c1
  left join sys.types as y1
    on c1.user_type_id = y1.user_type_id
  left join sys.key_constraints as k1
    on k1.parent_object_id = c1.object_id
   and k1.type = 'PK'
  left join sys.index_columns as i1
    on c1.object_id = i1.object_id
   and c1.column_id = i1.column_id
   and k1.parent_object_id = i1.object_id
   and k1.unique_index_id = i1.index_id
  left join sys.key_constraints as k2
    on k2.parent_object_id = c1.object_id
   and k1.type = 'UQ'
  left join sys.index_columns as i2
    on c1.object_id = i2.object_id
   and c1.column_id = i2.column_id
   and k1.parent_object_id = i2.object_id
   and k1.unique_index_id = i2.index_id
  left join sys.default_constraints as d1
    on c1.object_id = d1.parent_object_id
   and c1.column_id = d1.parent_column_id
 where c1.object_id = object_id('テーブル名', 'U')
 order by c1.column_id

select data_type, character_maximum_length, character_octet_length,
       numeric_precision, numeric_precision_radix, numeric_scale,
       is_nullable, column_name
  from jpbdb.information_schema.columns
 where table_name = 'テーブル名'
 order by ordinal_position

sp_help 'テーブル名'

sp_columns 'テーブル名'