--select * from syscolumns where [name]='invest'
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('invest') --tablename改成你要导出的表的名称
order by b.colid
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('invest') --tablename改成你要导出的表的名称
order by b.colid
解决方案 »
- 谁知道怎么把sql server 表中的数据导出到xml文件中吗?
- SQL SERVER 中事务提交问题 求高手指点!!!
- 用存储过程实现同时插入三个表
- 关于类似split函数的实现,请教
- 日期时间的sql语句,对你们来说简单!
- MSSQL中,有没有能够替代Exec的函数?
- 问一个很简单的问题!
- 触发器如何执行exec('select .. from inserted')
- 急!在delphi中为什么不能查询到sysobjects等系统表的“name”字段?请各位帮忙!
- 在存储过程中,表名可不可以用字符串??
- 各为英雄,这回这个问真的很急呀!HELP!
- 我想将这个insert语句,做成1个储存过程,这个储存过程该怎样写?谢谢!
select * from INFORMATION_SCHEMA.COLUMNS where TABLE_NAME='你的表名'
INFORMATION_SCHEMA.COLUMNS 视图里有你要的所有东西
返回列名。COL_LENGTH
返回列长度。
学习以上两个函数吧
SELECT COL_NAME(OBJECT_ID('表名'), 1)
---'1'代表你这个表的第一个字段,可以是'2'那就是第二个.......
EXEC sp_columns @table_name = '你的表名'
或
sp_columns '表名'
或
EXEC sp_columns @table_name = '表名'