正式表：各字段内容获取

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,‘‘) = ‘‘--默认值为空

  临时表：各字段内容获取

 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‘) 

获取内容样式如下：