SQL SERVER查询表名下的所有字段信息
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 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 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='表名' --如果只查询指定表,加上此条件
order by a.id,a.colorder
如果JAVA代码内查询出现
不支持“variant”数据类型。
因为别名需要重新写
修改后为:
"SELECT " +
"convert(varchar(100), A.name) as name, " +
"convert(varchar(100), case when exists(SELECT 1 FROM sysobjects where xtype='PK' and name in ( " +
"SELECT name FROM sysindexes WHERE indid in( " +
"SELECT indid FROM sysindexkeys WHERE id = A.id AND colid=A.colid " +
"))) then 'true' else 'false' end)as isKey, " +
"convert(varchar(100), B.name) as type, " +
"convert(varchar(100), isnull(G.[value],'') ) as remark " +
"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=?"