SQLServer 抓表名與表註解 | SQLServer select table name and table comments

SELECT 
    c.name '欄位名稱',
    ep.value AS '欄位備註',
    t.Name '欄位型態',
    c.max_length '最大byte',
    c.precision '總位數',
    c.scale '小數位數',
    c.is_nullable '是否可為空',
    ISNULL(i.is_primary_key, 0) '是否是PK',
 '///<summary> ' + cast(ep.value as nvarchar(max)) +' </summary>'+'\n'
 + 'public ' 
 + (case when t.Name in ('char','varchar','nvarchar') then 'string'
 when t.Name in ('datetime','date','time') then 'DateTime'
 when t.Name = 'decimal' then 'decimal'
 when t.Name = 'float' then 'float'
 when t.Name = 'int' then 'int'
 when t.Name = 'bit' then 'bool'
 else 'object' end )
 +' '+ c.name + '{get;set;}'+'\n' as '類別屬性宣告'
FROM    
    sys.columns c
INNER JOIN 
    sys.types t ON c.user_type_id = t.user_type_id
INNER JOIN sys.objects ON sys.objects.object_id = c.object_id
LEFT OUTER JOIN 
    sys.index_columns ic ON ic.object_id = c.object_id AND ic.column_id = c.column_id
LEFT OUTER JOIN 
    sys.indexes i ON ic.object_id = i.object_id AND ic.index_id = i.index_id
OUTER APPLY fn_listextendedproperty(default,
                  'SCHEMA', schema_name(sys.objects.schema_id),
                  'TABLE', sys.objects.name, 'COLUMN', c.name) ep
WHERE
    c.object_id = OBJECT_ID('你的表名字')

20180713 更新加入C#類別屬性宣告字串 用法:

1. 複製到class內
2. 選取剛剛貼上的部分
3. CTRL+H 打開取代
4. 按下正則表達選項
5. 搜尋\\n 取代為 \n
6. CTRL+E+D 排版