SELECT a.name AS 字段名, 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 ‘1‘ ELSE ‘0‘ END AS 主键, CASE WHEN COLUMNPROPERTY(a.id, a.name, ‘IsIdentity‘) = 1 THEN ‘1‘ ELSE ‘0‘ END AS 标识, b.name AS 类型, a.length AS 占用字节数, COLUMNPROPERTY(a.id, a.name, ‘PRECISION‘) AS 长度, a.xscale AS 小数, a.isnullable AS 可空, ISNULL(e.text, ‘‘) AS 默认值, ISNULL(g.[value], ‘‘) AS 字段说明 FROM syscolumns a LEFT OUTER 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 OUTER JOIN syscomments e ON a.cdefault = e.id LEFT OUTER JOIN sysproperties g ON a.id = g.id AND a.colid = g.smallid LEFT OUTER JOIN sysproperties f ON d.id = f.id AND f.smallid = 0 WHERE (d.name = ‘表名称‘)

SELECT 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 ‘1‘ ELSE ‘0‘ END AS ‘key‘, CASE WHEN COLUMNPROPERTY(a.id, a.name, ‘IsIdentity‘) = 1 THEN ‘1‘ ELSE ‘0‘ END AS ‘identity‘, a.name AS ColName, c.name AS TypeName, a.length AS ‘byte‘, COLUMNPROPERTY(a.id, a.name, ‘PRECISION‘) AS ‘length‘, a.xscale, a.isnullable, ISNULL(e.text, ‘‘) AS ‘default‘, ISNULL(p.value, ‘‘) AS ‘comment‘ FROM sys.syscolumns AS a INNER JOIN sys.sysobjects AS b ON a.id = b.id INNER JOIN sys.systypes AS c ON a.xtype = c.xtype LEFT OUTER JOIN sys.syscomments AS e ON a.cdefault = e.id LEFT OUTER JOIN sys.extended_properties AS p ON a.id = p.major_id AND a.colid = p.minor_id WHERE (b.name = ‘keyfactory‘) AND (c.status <> ‘1‘) --b.name = ‘Keyfactory‘,‘Keyfactory‘为你想要查找的数据表。

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,    标识       = 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,‘‘),    字段说明   = isnull(g.[value],‘‘)FROM     syscolumns aleft join     systypes b on     a.xusertype=b.xusertypeinner join     sysobjects d on     a.id=d.id  and d.xtype=‘U‘ and  d.name<>‘dtproperties‘left join     syscomments e on     a.cdefault=e.idleft join sys.extended_properties   g on     a.id=G.major_id and a.colid=g.minor_id  left join sys.extended_properties fon     d.id=f.major_id and f.minor_id=0where     d.name=‘Product‘    --如果只查询指定表,加上此条件order by     a.id,a.colorder