2011/01/20

カラムのコメントを取得する(mssql)


ここにコメント書いておけば、簡単なテーブル定義書出せるんじゃね?


declare @tablename varchar (30)
set @tablename = 'employee'

select
  cols.column_id as col_id
  ,left(cols.name, 20) as col_name
  ,left(types.name, 15) as type_name
  ,cols.max_length as col_length
  ,(case cols.is_nullable
    when 0 then 'no'
    when 1 then 'yes'
    else 'nothing'
    end) as nullable
  ,left(cast(ext.value as varchar), 50) as comment
from
  sys.columns as cols
left outer join
  (select
    name
    ,types.system_type_id
  from
    sys.types as types
  where
    is_user_defined = 0
  group by
    types.system_type_id
    ,types.name) as types
on
  types.system_type_id = cols.system_type_id
left join
  sys.objects as obj
on
  obj.object_id = cols.object_id
left join
  sys.extended_properties as ext
on
  cols.object_id = ext.major_id
and
  cols.column_id = ext.minor_id
where
  obj.name like @tablename
order by
  column_id
go

sp_tables
sp_columns
sp_pkeys
sp_fkeys

参考


SQLパズル 第2版 プログラミングが変わる書き方/考え方

0 件のコメント:

コメントを投稿