所有属性列名??
到底是属性还是列名?
如果列名:
select name from syscolumns where id in (select id from sysobjects where name='你的表' and type='U')
如果属性:
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 sysproperties g on a.id=g.id and a.colid=g.smallid
left join sysproperties f on d.id=f.id and f.smallid=0
where d.name='你的表'
order by a.id,a.colorder
到底是属性还是列名?
如果列名:
select name from syscolumns where id in (select id from sysobjects where name='你的表' and type='U')
如果属性:
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 sysproperties g on a.id=g.id and a.colid=g.smallid
left join sysproperties f on d.id=f.id and f.smallid=0
where d.name='你的表'
order by a.id,a.colorder
解决方案 »
- 查询问题
- 【请教】如何实现2个表单结合且需要递归
- 聚合不应出现在 UPDATE 语句的集合列表中。
- 再请教一个行列转换的存储过程问题
- 如何实现如下功能的sql
- windows7无法连接到远程数据库
- 求助:6个表的数据处理
- 一个简单问题,请赐教
- sql server游标问题
- 请教大侠我用SQL定义字段类型为YESNO时,怎么再确定具体是"是/否","真/假",和"开/关"?
- 无法在 sysdepends 中添加当前存储过程所对应的行,因为缺少该存储过程所依赖的对象 'forums_Forum_Permission_CreateUpdateDelete'。仍
- 027-87561931 87561932 87591933 87591934 87591935
--多少列?意思就how many fields? 那么
select count(name) from syscolumns where id in (select id from sysobjects where name='你的表' and type='U')
T-SQL的没有.
VB的有:Dim cat As New ADOX.Catalog
cat.ActiveConnection = "Provider=Microsoft.Jet.OLEDB.4.0;_
Persist Security Info=True;Data Source=" + “c:\r2k.mdb" + ";_
Jet OLEDB:Database Password='123456'"‘连接数据库
Dim i As Integer
Dim j As Integer
For i = 0 To cat.Tables.Count – 1 ‘循环所有表单
Debug.Print "表单名称:" + cat.Tables(i).Name
For j = 0 To cat.Tables(i).Columns.Count – 1 ‘循环表单所有字段
Debug.Print "字段名称:" + cat.Tables(i).Columns(j).Name
Debug.Print "字段类型:" + GetDataTypeEnum(cat.Tables(i).Columns(j).Type)
Debug.Print "字段大小:"; Format(cat.Tables(i).Columns(j).DefinedSize)
Next
Next
Set cat=Nothing
找出一個表的字段及其描述
*/select rtrim(b.name) as colname
,case when h.id is not null then 'PK' else '' end as primarykey
,type_name(b.xusertype) + case when b.colstat & 1 = 1 then '[ID(' + convert(varchar,ident_seed(a.name)) + ',' + convert(varchar,ident_incr(a.name)) + ')]' else '' end as type
,b.length
,case b.isnullable when 0 then 'N' else 'Y' end as [isnull]
,isnull(e.text,'') as [default]
,isnull(c.value,'') as descript
from sysobjects a,syscolumns b
left outer join sysproperties c on b.id = c.id and b.colid = c.smallid
left outer join syscomments e on b.cdefault = e.id
left outer join (select g.id,g.colid from sysindexes f,sysindexkeys g where f.id = g.id and f.indid = g.indid and f.indid > 0 and f.indid < 255 and (f.status & 2048)<>0) h on b.id = h.id and b.colid = h.colid
where a.id = b.id
and a.id = object_id('tablename') --tablename改成你要查的表名
order by b.colid
SELECT *
FROM ::fn_listextendedproperty (null, 'user', 'dbo', 'table', '你的表', 'column', default)