PCB MS SQL 通过表名查询各字段信息和vb.net C# module类代码-白红宇

PCB MS SQL 通过表名查询各字段信息和vb.net C# module类代码

阅读量：4876 次

发布时间：2019-06-11

本文共 8910 字，大约阅读时间需要 29 分钟。

正式表：各字段内容获取

DECLARE @tabname VARCHAR(20)SET @tabname = 'ppeflow'SELECT  @tabname AS '表名'     ,(CASE             WHEN T.name = 'varchar' or T.name = 'char' THEN T.name + '(' + CAST(COLUMNPROPERTY(C.id,C.name,'PRECISION') as  VARCHAR(20))+ ')'            WHEN T.name = 'numeric' THEN T.name + '(' + CAST(COLUMNPROPERTY(C.id,C.name,'PRECISION') as  VARCHAR(20)) + ',' +  CAST(isnull(COLUMNPROPERTY(c.id,c.name,'Scale'),0) as  VARCHAR(20)) + ')'            WHEN T.name = 'decimal' THEN T.name + '(' + CAST(COLUMNPROPERTY(C.id,C.name,'PRECISION') as  VARCHAR(20)) + ',' +  CAST(isnull(COLUMNPROPERTY(c.id,c.name,'Scale'),0) as  VARCHAR(20)) + ')'             ELSE T.name      END) AS '字段类型与容量'      ,C.name as [字段名],T.name as [字段类型]       ,convert(bit,C.IsNullable)  as [可否为空]       ,convert(bit,case when exists(SELECT 1 FROM sysobjects where xtype='PK' and parent_obj=c.id and name in (           SELECT name FROM sysindexes WHERE indid in(               SELECT indid FROM sysindexkeys WHERE id = c.id AND colid=c.colid))) then 1 else 0 end)                    as [是否主键]       ,convert(bit,COLUMNPROPERTY(c.id,c.name,'IsIdentity')) as [自动增长]       ,C.Length as [占用字节]        ,COLUMNPROPERTY(C.id,C.name,'PRECISION') as [长度]       ,isnull(COLUMNPROPERTY(c.id,c.name,'Scale'),0) as [小数位数]       ,ISNULL(CM.text,'') as [默认值]       ,isnull(ETP.value,'') AS [字段描述]       ,('Property ' + C.name + ' AS ' +         CASE T.name                  WHEN  'varchar' THEN  'String'                   WHEN  'nvarchar' THEN  'String'                   WHEN  'char' THEN  'String'                   WHEN  'numeric' THEN  'Decimal'                   WHEN  'decimal' THEN  'Decimal'                   WHEN  'datetime' THEN  'DateTime'                   WHEN  'int' THEN  'Integer'                   WHEN  'smallint' THEN  'Short'                   WHEN  'real' THEN  'Single'                   WHEN  'float' THEN  'Double'                    WHEN  'bit' THEN  'Boolean'                    WHEN  'uniqueidentifier' THEN  'Guid'          END ) 'VB.net'     ,('public ' +        CASE T.name                  WHEN  'varchar' THEN  'string'                   WHEN  'nvarchar' THEN  'string'                   WHEN  'char' THEN  'string'                   WHEN  'numeric' THEN  'decimal'                   WHEN  'decimal' THEN  'decimal'                   WHEN  'datetime' THEN  'DateTime'                   WHEN  'int' THEN  'int'                   WHEN  'smallint' THEN  'short'                   WHEN  'real' THEN  'float'                   WHEN  'float' THEN  'double'                    WHEN  'bit' THEN  'bool'                    WHEN  'uniqueidentifier' THEN  'Guid'          END         + ' ' + C.name  + ' { get; set; }') 'C#'     --,ROW_NUMBER() OVER (ORDER BY C.name) AS [Row]       --,REPLACE(REPLACE(REPLACE('row_T_WF_MAIN("'+c.name+'") = '  +  STUFF(STUFF(isnull(CM.text,''),1,1,''),LEN(STUFF(isnull(CM.text,''),1,1,'')),1,''),'''','"'),'getdate()','Date.Now()'),'newid()','Guid.NewGuid')AS'row'FROM syscolumns C  INNER JOIN systypes T ON C.xusertype = T.xusertype   left JOIN sys.extended_properties ETP   ON  ETP.major_id = c.id AND ETP.minor_id = C.colid AND ETP.name ='MS_Description'   left join syscomments CM on C.cdefault=CM.id  WHERE C.id = object_id(@tabname)  --AND convert(bit,C.IsNullable) = 0  --不为空--AND ISNULL(CM.text,'') = ''--默认值为空

正式表：各字段内容获取（加注释，复制到C#中再将###正则替换为\n）

DECLARE @tabname VARCHAR(200)SET @tabname = 'T_RuleTableCell'SELECT  @tabname AS '表名'     ,(CASE             WHEN T.name = 'varchar' or T.name = 'char' THEN T.name + '(' + CAST(COLUMNPROPERTY(C.id,C.name,'PRECISION') as  VARCHAR(20))+ ')'            WHEN T.name = 'numeric' THEN T.name + '(' + CAST(COLUMNPROPERTY(C.id,C.name,'PRECISION') as  VARCHAR(20)) + ',' +  CAST(isnull(COLUMNPROPERTY(c.id,c.name,'Scale'),0) as  VARCHAR(20)) + ')'            WHEN T.name = 'decimal' THEN T.name + '(' + CAST(COLUMNPROPERTY(C.id,C.name,'PRECISION') as  VARCHAR(20)) + ',' +  CAST(isnull(COLUMNPROPERTY(c.id,c.name,'Scale'),0) as  VARCHAR(20)) + ')'             ELSE T.name      END) AS '字段类型与容量'      ,C.name as [字段名],T.name as [字段类型]       ,convert(bit,C.IsNullable)  as [可否为空]       ,convert(bit,case when exists(SELECT 1 FROM sysobjects where xtype='PK' and parent_obj=c.id and name in (           SELECT name FROM sysindexes WHERE indid in(               SELECT indid FROM sysindexkeys WHERE id = c.id AND colid=c.colid))) then 1 else 0 end)                    as [是否主键]       ,convert(bit,COLUMNPROPERTY(c.id,c.name,'IsIdentity')) as [自动增长]       ,C.Length as [占用字节]        ,COLUMNPROPERTY(C.id,C.name,'PRECISION') as [长度]       ,isnull(COLUMNPROPERTY(c.id,c.name,'Scale'),0) as [小数位数]       ,ISNULL(CM.text,'') as [默认值]       ,isnull(ETP.value,'') AS [字段描述]       ,('Property ' + C.name + ' AS ' +         CASE T.name                  WHEN  'varchar' THEN  'String'                   WHEN  'nvarchar' THEN  'String'                   WHEN  'char' THEN  'String'                   WHEN  'numeric' THEN  'Decimal'                   WHEN  'decimal' THEN  'Decimal'                   WHEN  'datetime' THEN  'DateTime'                   WHEN  'int' THEN  'Integer'                   WHEN  'smallint' THEN  'Short'                   WHEN  'real' THEN  'Single'                   WHEN  'float' THEN  'Double'                    WHEN  'bit' THEN  'Boolean'                    WHEN  'uniqueidentifier' THEN  'Guid'          END ) 'VB.net'     ,( '///'    + '###' +       '///'    + CAST(isnull(ETP.value,'') AS VARCHAR(200)) +  '###' +        '///
'    + '###' +          'public ' +        CASE T.name                  WHEN  'varchar' THEN  'string'                   WHEN  'nvarchar' THEN  'string'                   WHEN  'char' THEN  'string'                   WHEN  'numeric' THEN  'decimal'                   WHEN  'decimal' THEN  'decimal'                   WHEN  'datetime' THEN  'DateTime'                   WHEN  'int' THEN  'int'                   WHEN  'smallint' THEN  'short'                   WHEN  'real' THEN  'float'                   WHEN  'float' THEN  'double'                    WHEN  'bit' THEN  'bool'                    WHEN  'uniqueidentifier' THEN  'Guid'          END         + ' ' + C.name  + ' { get; set; }') 'C#'     --,ROW_NUMBER() OVER (ORDER BY C.name) AS [Row]       --,REPLACE(REPLACE(REPLACE('row_T_WF_MAIN("'+c.name+'") = '  +  STUFF(STUFF(isnull(CM.text,''),1,1,''),LEN(STUFF(isnull(CM.text,''),1,1,'')),1,''),'''','"'),'getdate()','Date.Now()'),'newid()','Guid.NewGuid')AS'row'FROM syscolumns C  INNER JOIN systypes T ON C.xusertype = T.xusertype   left JOIN sys.extended_properties ETP   ON  ETP.major_id = c.id AND ETP.minor_id = C.colid AND ETP.name ='MS_Description'   left join syscomments CM on C.cdefault=CM.id  WHERE C.id = object_id(@tabname)  --AND convert(bit,C.IsNullable) = 0  --不为空--AND ISNULL(CM.text,'') = ''--默认值为空

临时表：各字段内容获取

SELECT       C.name as [字段名],T.name as [字段类型]       ,convert(bit,C.IsNullable)  as [可否为空]       ,convert(bit,case when exists(SELECT 1 FROM sysobjects where xtype='PK' and parent_obj=c.id and name in (           SELECT name FROM sysindexes WHERE indid in(               SELECT indid FROM sysindexkeys WHERE id = c.id AND colid=c.colid))) then 1 else 0 end)                    as [是否主键]       ,convert(bit,COLUMNPROPERTY(c.id,c.name,'IsIdentity')) as [自动增长]       ,C.Length as [占用字节]        ,COLUMNPROPERTY(C.id,C.name,'PRECISION') as [长度]       ,isnull(COLUMNPROPERTY(c.id,c.name,'Scale'),0) as [小数位数]       ,ISNULL(CM.text,'') as [默认值]       ,isnull(ETP.value,'') AS [字段描述]     ,('Property ' + C.name + ' AS ' +         CASE T.name                  WHEN  'varchar' THEN  'string'                   WHEN  'char' THEN  'string'                   WHEN  'numeric' THEN  'Decimal'                   WHEN  'decimal' THEN  'Decimal'                   WHEN  'datetime' THEN  'DateTime'                   WHEN  'int' THEN  'Integer'                   WHEN  'real' THEN  'Single'                   WHEN  'float' THEN  'Double'          END ) 'VB.net'     ,('public ' +        CASE T.name                  WHEN  'varchar' THEN  'string'                   WHEN  'char' THEN  'string'                   WHEN  'numeric' THEN  'decimal'                   WHEN  'decimal' THEN  'decimal'                   WHEN  'datetime' THEN  'DateTime'                   WHEN  'int' THEN  'int'                   WHEN  'real' THEN  'float'                   WHEN  'float' THEN  'double'                    WHEN  'bit' THEN  'bool'          END         + ' ' + C.name  + ' { get; set; }') 'C#'     --,ROW_NUMBER() OVER (ORDER BY C.name) AS [Row]  FROM tempdb..syscolumns C  INNER JOIN systypes T ON C.xusertype = T.xusertype   left JOIN sys.extended_properties ETP   ON  ETP.major_id = c.id AND ETP.minor_id = C.colid AND ETP.name ='MS_Description'   left join syscomments CM on C.cdefault=CM.id  WHERE C.id = object_id('tempdb..#tab2')

获取内容样式如下：