根据表名得到表信息，包括字段说明，生成C#类属性

/********************************************  
* 根据表名得到表信息，包括字段说明，生成C#类属性      
********************************************/     
Create PROC [dbo].[sp_help_table]   
(@tableName VARCHAR(200), @ColumnLike VARCHAR(200) = NULL)           
AS     
--如果表名不存在，就直接选出相似表  
IF NOT EXISTS(  
       SELECT 1  
       FROM   sysobjects  
       WHERE  id = OBJECT_ID(@tableName)  
              AND TYPE = 'U'  
   )  
BEGIN  
    SELECT NAME FROM   sysobjects  
    WHERE  NAME LIKE '%' + @tableName + '%' AND TYPE = 'U'     
    RETURN  
END   
   
   
--筛选相似列名  
IF (@ColumnLike IS NULL)  
    SET @ColumnLike = ''  
     
DECLARE @ColumnTable TABLE(cName VARCHAR(200))     
INSERT @ColumnTable  
  (  
    cName  
  )  
SELECT a.name  
FROM   syscolumns a,sysobjects d  
WHERE  a.id = d.id  
       AND d.name = @tableName  
       AND a.name LIKE '%' + @ColumnLike + '%'     
       
--查询表结构信息             
SELECT 表名 = CASE  
                   WHEN a.colorder = 1 THEN d.name  
                   ELSE ''  
              END,  
       表说明 = CASE  
                     WHEN a.colorder = 1 THEN ISNULL(f.value, '')  
                     ELSE ''  
                END,  
       字段序号 = a.colorder,  
       字段名 = a.name,  
       字段说明 = ISNULL(g.[value], ''),  
       标识 = CASE  
                   WHEN COLUMNPROPERTY(a.id, a.name, 'IsIdentity') = 1 THEN '√'  
                   ELSE ''  
              END,  
       主键 = CASE  
                   WHEN EXISTS(  
                            SELECT 1 FROM   sysobjects WHERE  xtype = 'PK' AND parent_obj = a.id  
                                   AND NAME   IN (SELECT NAME FROM   sysindexes  
                                                  WHERE  indid   IN (SELECT indid FROM sysindexkeys  
                                                                     WHERE  id = a.id AND  colid = a.colid))  
                        ) THEN '√'  
                   ELSE ''  
              END,  
       类型 = b.name,  
       占用字节数 = a.length,  
       长度 = COLUMNPROPERTY(a.id, a.name, 'PRECISION'),  
       小数位数 = ISNULL(COLUMNPROPERTY(a.id, a.name, 'Scale'), 0),  
       允许空 = CASE WHEN a.isnullable = 1 THEN '√'  
                     ELSE ''  
                END,  
       默认值 = ISNULL(e.text, ''),  
        [c# 类字段] =    
        case when g.[value] is not null then '/// <summary>'+CHAR(13)+'/// '+cast(g.[value] as nvarchar) + CHAR(13)+'/// </summary>' + CHAR(13) else '' end  
        + '[DataMember]' + CHAR(13) + 'public '  
        + case when b.name='bigint' then 'long'   
                when b.name='nvarchar' or b.name='varchar' then 'string'  
                when b.name='int' or b.name='tinyint' then 'int'  
                when b.name='bit' then 'bool'  
                when b.name='datetime' then 'DateTime'  
        else b.name end +' '+a.name+' { get; set; } '+ CHAR(13)  
FROM   syscolumns a  
       LEFT   JOIN systypes b  
            ON  a.xusertype = b.xusertype  
       INNER   JOIN sysobjects d  
            ON  a.id = d.id  
            AND d.xtype = 'U'  
            AND d.name <> 'dtproperties'  
       LEFT   JOIN syscomments e  
            ON  a.cdefault = e.id  
       LEFT   JOIN sys.extended_properties g  
            ON  a.id = g.major_id  
            AND a.colid = g.minor_id  
       LEFT   JOIN sys.extended_properties f  
            ON  d.id = f.major_id  
            AND f.minor_id = 0  
                --where   d.name='要查询的表'         --如果只查询指定表,加上此条件  
WHERE  d.name = @tableName  
       AND EXISTS(  
               SELECT 1  
               FROM   @ColumnTable  
               WHERE  cname = a.name  
           )  
ORDER BY a.id,a.colorder  

转自：https://blog.csdn.net/guochunyang/article/details/50580705