select * from syscolumns aleft join sysobjects b on a.id=b.id where b.name='TableName'
select * from syscolumns a left join sysobjects b on a.id=b.id where b.xtype='U'
参考: strSql = "SELECT "; strSql += " 表名=case when a.colorder=1 then d.name else '' end, "; strSql += " 表说明=case when a.colorder=1 then isnull(f.value,'') else '' end, "; strSql += " 字段序号=a.colorder, "; strSql += " 字段名=a.name, "; strSql += " 标识=case when COLUMNPROPERTY( a.id,a.name,'IsIdentity')=1 then '√'else '' end, "; strSql += " 主键=case when exists(SELECT 1 FROM sysobjects where xtype='PK' and parent_obj=a.id and name in ( "; strSql += " SELECT name FROM sysindexes WHERE indid in( "; strSql += " SELECT indid FROM sysindexkeys WHERE id = a.id AND colid=a.colid "; strSql += " ))) then '√' else '' end, "; strSql += " 类型=b.name, "; strSql += " 占用字节数=a.length, "; strSql += " 长度=COLUMNPROPERTY(a.id,a.name,'PRECISION'), "; strSql += " 小数位数=isnull(COLUMNPROPERTY(a.id,a.name,'Scale'),0), "; strSql += " 允许空=case when a.isnullable=1 then '√'else '' end, "; strSql += " 默认值=isnull(e.text,''), "; strSql += " 字段说明=isnull(g.[value],'') "; strSql += " FROM syscolumns a "; strSql += " left join systypes b on a.xusertype=b.xusertype "; strSql += " inner join sysobjects d on a.id=d.id and d.xtype='U' and d.name<>'dtproperties' "; strSql += " left join syscomments e on a.cdefault=e.id "; strSql += " left join sysproperties g on a.id=g.id and a.colid=g.smallid "; strSql += " left join sysproperties f on d.id=f.id and f.smallid=0 "; strSql += " where d.name='" + strTableName + " ' "; strSql += " order by a.id,a.colorder";
select [name] from syscolumns a left join sysobjects b on a.id=b.id where b.xtype='U'
select * from sysobjects where xtype='u' --讀取所有用戶表 select * from syscolumns --讀取所有columns
5楼的很全,不过用StringBuilder就更好了
select * from syscolumns a left join sysobjects b on a.id=b.id where b.name='TableName'
create table ta(id int,name varchar(30),date datetime)create table tb(id int,name varchar(30)) select a.name '表名',b.name '字段名',c.name '类型',b.length '长度' from sysobjects a,syscolumns b,systypes c where a.id=b.id and a.xtype='u' and b.xtype=c.xtype order by a.name表名 字段名 类型 长度 ta id int 4 ta name varchar 30 ta date datetime 8 tb id int 4 tb name varchar 30
代碼不是我寫的,好像是SQL版邹健大佬寫的SELECT (case when a.colorder=1 then d.name else '' end)表名, a.colorder 字段序号, a.name 字段名, (case when COLUMNPROPERTY( a.id,a.name,'IsIdentity')=1 then '√'else '' end) 标识, (case when (SELECT count(*) FROM sysobjects WHERE (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))))))) AND (xtype = 'PK'))>0 then '√' else '' end) 主键, b.name 类型, a.length 占用字节数, COLUMNPROPERTY(a.id,a.name,'PRECISION') as 长度, isnull(COLUMNPROPERTY(a.id,a.name,'Scale'),0) as 小数位数, (case when a.isnullable=1 then '√'else '' end) 允许空, isnull(e.text,'') 默认值, isnull(g.[value],'') AS 字段说明 FROM syscolumns a left join systypes b on a.xtype=b.xusertypeinner join sysobjects d on a.id=d.id and d.xtype='U' and d.name<>'dtproperties'left join syscomments eon a.cdefault=e.idleft join sysproperties gon a.id=g.id AND a.colid = g.smallid order by a.id,a.colorder
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
select * from syscolumns aleft join sysobjects b on a.id=b.id
where b.name='TableName'
left join sysobjects b on a.id=b.id
where b.xtype='U'
strSql += " 表名=case when a.colorder=1 then d.name else '' end, ";
strSql += " 表说明=case when a.colorder=1 then isnull(f.value,'') else '' end, ";
strSql += " 字段序号=a.colorder, ";
strSql += " 字段名=a.name, ";
strSql += " 标识=case when COLUMNPROPERTY( a.id,a.name,'IsIdentity')=1 then '√'else '' end, ";
strSql += " 主键=case when exists(SELECT 1 FROM sysobjects where xtype='PK' and parent_obj=a.id and name in ( ";
strSql += " SELECT name FROM sysindexes WHERE indid in( ";
strSql += " SELECT indid FROM sysindexkeys WHERE id = a.id AND colid=a.colid ";
strSql += " ))) then '√' else '' end, ";
strSql += " 类型=b.name, ";
strSql += " 占用字节数=a.length, ";
strSql += " 长度=COLUMNPROPERTY(a.id,a.name,'PRECISION'), ";
strSql += " 小数位数=isnull(COLUMNPROPERTY(a.id,a.name,'Scale'),0), ";
strSql += " 允许空=case when a.isnullable=1 then '√'else '' end, ";
strSql += " 默认值=isnull(e.text,''), ";
strSql += " 字段说明=isnull(g.[value],'') ";
strSql += " FROM syscolumns a ";
strSql += " left join systypes b on a.xusertype=b.xusertype ";
strSql += " inner join sysobjects d on a.id=d.id and d.xtype='U' and d.name<>'dtproperties' ";
strSql += " left join syscomments e on a.cdefault=e.id ";
strSql += " left join sysproperties g on a.id=g.id and a.colid=g.smallid ";
strSql += " left join sysproperties f on d.id=f.id and f.smallid=0 ";
strSql += " where d.name='" + strTableName + " ' ";
strSql += " order by a.id,a.colorder";
left join sysobjects b on a.id=b.id
where b.xtype='U'
select * from syscolumns --讀取所有columns
where b.name='TableName'
select a.name '表名',b.name '字段名',c.name '类型',b.length '长度' from sysobjects a,syscolumns b,systypes c
where a.id=b.id and a.xtype='u' and b.xtype=c.xtype order by a.name表名 字段名 类型 长度
ta id int 4
ta name varchar 30
ta date datetime 8
tb id int 4
tb name varchar 30
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