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

来源：互联网发布：熊族看刀路软件下载编辑：程序博客网时间：2024/06/05 15:19
/********************************************* 根据表名得到表信息，包括字段说明，生成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'       RETURNEND   --筛选相似列名IF (@ColumnLike IS NULL)    SET @ColumnLike = ''   DECLARE @ColumnTable TABLE(cName VARCHAR(200))   INSERT @ColumnTable  (    cName  )SELECT a.nameFROM   syscolumns a,sysobjects dWHERE  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.nameend+' '+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
0 0