sql表输出class类

sql表输出class类

仅为个人理解，有空请参观迷恋自留地

好像还没人发布过,有个问题是 字段的 《 描述》 不能为空

declare @tableName nvarchar(200)

-------------修改表名和对应的库名--------------
set @tableName='t_sys_navigation'


SELECT EX.VALUE, T.NAME , C.NAME, 
N'
/// <summary>
/// '+CONVERT(NVARCHAR(2000),EX.VALUE)+'
/// </summary>
public virtual '
+ CASE WHEN T.NAME IN ('nvarchar','varchar','char','nchar','text','image') THEN 'string'
WHEN T.NAME IN ('double','float','decimal') THEN 'float'
WHEN T.NAME IN ('bit') THEN 'bool'
WHEN T.NAME IN ('datetime') THEN 'DateTime'
else T.Name end + ' ' + C.NAME+ ' { get; set; } '	
FROM SYS.COLUMNS C 
LEFT OUTER JOIN SYS.EXTENDED_PROPERTIES EX ON EX.MAJOR_ID = C.OBJECT_ID AND EX.MINOR_ID = C.COLUMN_ID AND EX.NAME = 'MS_DESCRIPTION' 
LEFT OUTER JOIN SYSTYPES T ON C.SYSTEM_TYPE_ID=T.XTYPE 

WHERE T.NAME<>'SYSNAME' AND OBJECTPROPERTY(C.OBJECT_ID, 'ISMSSHIPPED')=0 
AND OBJECT_NAME(C.OBJECT_ID) =@tableName
AND C.NAME NOT IN ('description','sortNo','createTime','updateTime','valid','deleted','createrId','createrName','ID')
ORDER BY COLUMN_ID