select 列名1,列名2 as 新列名 into 新表名 from 表
解决方案 »
- sql order by 问题,大家帮我!
- 关于存储过程的参数的数据类型
- 条件中加"主键 is not null"对效率有多大的影响?
- 求教:在同一张表中,有些记录字段A和字段B相等,求将字段A,B相同的记录合并成一条记录的方法。搞了几天没搞定,真心感谢.
- 求一个存储过程(建表和插入在一个存储过程中) 当前时间为表名,有id,name两个属性,同时插入另一个表的数据,的存储过程
- 非常奇怪的问题--sql是否有高手知道
- 查询分析器如何修改表的只读属性
- 挑战高手, 巨难问题. 有关不同系统(ACCESS, MSSQLSERVER) 的SQL语句兼容问题
- 班主,我的专家分怎么莫名其妙地多了4分? 怎样提高我提问的时候可以用的分数?
- 请问如何备份数据库中的单张表?
- oledb sample northwindoledb error
- pb开发
FROM syscolumns INNER JOIN
sysobjects ON syscolumns.id = sysobjects.id
WHERE (sysobjects.xtype = 'u')
可能对你有用。
select a.name as objname ,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.xtype='U'order by a.name
FROM syscolumns a ,sysobjects b
where a.id = b.id and b.xtype = 'u'
order by b.name,a.name
(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.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
order by a.id,a.colorder
select a.name
FROM syscolumns a ,sysobjects b
where b.id = id('表名') and a.id = b.id and b.xtype = 'u'