查询用户表及表结构
SELECT 表名=case when a.colorder is not null then d.name 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.xtype=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 sysproperties g on a.id=g.id and a.colid=g.smallid WHERE d.name='table1' -- 数据表名 ORDER BY a.colorder -- 排序查询用户表：
select 表名=name from sysobjects where xtype='U' and name<>'dtproperties' 1 sp_tables
获取sqlserver中数据库的表集合
2 sp_pkeys
获取当前数据库某数据表的主键
3 sp_primarykeys
获取远程数据库某数据表的主键
SELECT 表名 = CASE a.colorder WHEN 1 THEN c.name ELSE '' END,
序 = a.colorder,
字段名 = a.name,
标识 = CASE COLUMNPROPERTY(a.id,a.name,'IsIdentity') WHEN 1 THEN '√' ELSE '' END,
主键 = CASE
WHEN EXISTS ( SELECT *
FROM sysobjects
WHERE xtype='PK'
AND name IN (SELECT [name]
FROM sysindexes
WHERE id=a.id
AND indid IN (SELECT indid
FROM sysindexkeys
WHERE id=a.id
AND colid IN (SELECT colid
FROM syscolumns
WHERE id=a.id
AND name=a.name))))
THEN '√' ELSE '' END,
类型 = b.name,
字节数 = a.length,
长度 = COLUMNPROPERTY(a.id,a.name,'Precision'),
小数 = CASE ISNULL(COLUMNPROPERTY(a.id,a.name,'Scale'),0) WHEN 0 THEN '' ELSE CAST(COLUMNPROPERTY(a.id,a.name,'Scale') AS VARCHAR) END,
允许空 = CASE a.isnullable WHEN 1 THEN '√' ELSE '' END,
默认值 = ISNULL(d.[text],''),
说明 = ISNULL(e.[value],'')
FROM syscolumns a
LEFT JOIN systypes b ON a.xtype=b.xusertype
INNER JOIN sysobjects c ON a.id=c.id AND c.xtype='U' AND c.name<>'dtproperties'
LEFT JOIN syscomments d ON a.cdefault=d.id
LEFT JOIN sys.extended_properties e ON a.id=e.class AND a.colid=e.minor_id
ORDER BY c.name, a.colorder
select o.name tablename, c.name fieldname, t.name fieldtype, columnproperty(c.id,c.name,'PRECISION') fieldlen, c.Scale,c.length,
c.colid fieldorder, c.isnullable,
case when c.colid in(select ik.colid
from sysindexes i, Sysindexkeys ik, sysobjects oo
where i.id=ik.id and i.indid=ik.indid
and i.name=oo.name and oo.xtype='PK' --主键
and o.id=i.id
) then 1 else 0 end isPrimaryKey,
case when c.colid in(select ik.colid
from sysindexes i, Sysindexkeys ik
where i.id=ik.id and i.indid=ik.indid
and o.id=i.id and i.indid=1 --聚类索引
) then 1 else 0 end isClusterKey,
columnproperty( c.id, c.name,'IsIdentity') IsIdentity,
isnull(m.text,'') defaultvalue
from sysobjects o, syscolumns c, systypes t, syscomments m
where o.xtype='U'
and o.id=c.id
and c.xtype=t.xtype
and c.cdefault*=m.id
order by o.name, c.colid
程序使用到的版本是否用0/1表示, 如果用在文档整理, 难看得要命, 优化了个文档版本的,在查询分析器找出来还是蛮好看的.
select case when c.colid=1 then o.name else '' end 表名,
c.colid 顺序,
c.name 字段名,
t.name 字段类型,
columnproperty(c.id,c.name,'PRECISION') 字段长度,
isnull(c.Scale,'') 小数位,
c.length 占用字节,
case when c.isnullable=1 then '√' else '' end 可为空,
case when c.colid in(select ik.colid
from sysindexes i, Sysindexkeys ik, sysobjects oo
where i.id=ik.id and i.indid=ik.indid
and i.name=oo.name and oo.xtype='PK' --主键
and o.id=i.id
) then '√' else '' end 主键,
case when c.colid in(select ik.colid
from sysindexes i, Sysindexkeys ik
where i.id=ik.id and i.indid=ik.indid
and o.id=i.id and i.indid=1 --聚类索引
) then '√' else '' end 聚类索引,
case when columnproperty( c.id, c.name,'IsIdentity')=1 then '√' else '' end 自增长,
isnull(m.text,'') 默认值
from sysobjects o, syscolumns c, systypes t, syscomments m
where o.xtype='U'
and o.id=c.id
and c.xtype=t.xtype
and c.cdefault*=m.id
order by o.name, c.colid
