dbリバースエンジニアリング

ER図は、visioでできるっぽい。

http://www.atmarkit.co.jp/fdotnet/visiouml/visiouml03/visiouml03_04.html

http://blogs.wankuma.com/naka/archive/2004/06/11/3094.aspx

http://beausoir.blog100.fc2.com/blog-entry-31.html


http://www3.ezbbs.net/cgi/bbs?id=visio&dd=02&p=7

テーブル定義は、なかなかよさそうなものがなさそうなので、sqlを作ってみる。

select obj.name
,isnull((select ex.value from sys.extended_properties as ex where col.object_id = ex.major_id and ex.minor_id = 0 and ex.name = 'MS_Description' ),'') as '表コメント'
,isnull((select '○' from sys.indexes ii inner join sys.index_columns iic on ii.object_id=iic.object_id and ii.index_id=iic.index_id where ii.is_primary_key=1 and ii.object_id = obj.object_id and iic.column_id = Col.column_id),'') as PK
,case Col.is_nullable
when 1 then '○'
else ''
end 'NULL'
,Col.name as '列名'
,(Select name From sys.types Where sys.types.user_type_id = Col.user_type_id) as '型'
,case Col.max_length
when -1 then 'max'
else cast(Col.max_length as char(4))
end '列内最大長'
,case isnull(Col.scale,0)
when 0 then ' '
else cast( Col.scale as char(10) )
end '小数部'
,isnull((select definition from sys.default_constraints where object_id = Col.default_object_id),'') as 'デフォルト'
,isnull((select ex.value from sys.extended_properties as ex where col.object_id = ex.major_id and ex.minor_id = col.column_id and ex.name = 'MS_Description' ),'') as '列コメント'
From sys.columns as Col
inner join sys.objects as obj on Col.object_id = obj.object_id
Where obj.type='U' and obj.name <> 'sysdiagrams'
order by obj.name asc,col.column_id asc

こんな感じでいいかなぁ？