sysindexkeys
包含索引中的键或列的信息。该表存储在每个数据库中。列名 数据类型 描述
id int 表 ID
indid smallint 索引 ID
colid smallint 列 ID
keyno smallint 该列在索引中的位置
包含索引中的键或列的信息。该表存储在每个数据库中。列名 数据类型 描述
id int 表 ID
indid smallint 索引 ID
colid smallint 列 ID
keyno smallint 该列在索引中的位置
from sysindexkeys a
join sysobjects b on a.indid=b.id
join sysobjects c on a.id=c.id
join syscolumns d on a.id=d.id and a.colid=d.colid
select [name] from sysobjects where xtype = 'u' and status > 0
然后游标
DBCC DBREINDEX (@tableName)
就可以了重建索引了
sp_helpindex authors可进一步查到详细的表的索引信息
from sysindexkeys a
join sysobjects b on a.indid=b.id
join sysobjects c on a.id=c.id
,表名=c.name
,索引字段名=d.name
from sysindexes a
join sysindexkeys b on a.id=b.id and a.indid=b.indid
join sysobjects c on b.id=c.id
join syscolumns d on b.id=d.id and b.colid=d.colid
where a.indid not in(0,255)
order by c.name,a.name,d.name
,表名=c.name
from sysindexes a
join sysindexkeys b on a.id=b.id and a.indid=b.indid
join sysobjects c on b.id=c.id
where a.indid not in(0,255)
and c.xtype='U' and c.status>0
order by c.name,a.name
from sysindexkeys a
join sysobjects b on a.indid=b.id
join sysobjects c on a.id=c.id
where c.name in (
select [name] from sysobjects where xtype = 'u' and status > 0)
from sysindexkeys a
join sysobjects b on a.indid=b.id
join sysobjects c on a.id=c.id
where c.name in (
select [name] from sysobjects where xtype = 'u' and status > 0)
and b.name not like 'sys%'
不行呀,有很多不是的
,表名=c.name
from sysindexes a
join sysindexkeys b on a.id=b.id and a.indid=b.indid
join sysobjects c on b.id=c.id
left join sysobjects d on a.name=d.name
where a.indid not in(0,255)
and c.xtype='U' --and c.status>0
and (d.xtype is null or d.xtype<>'PK')
order by c.name,a.name