SELECT u.name + '.' + o.name tablename, i.indid, i.name index_name, CASE WHEN (i.status & 2)<>0 THEN 'yes' ELSE 'no' END unq, CASE WHEN (i.status & 2048)<>0 THEN 'yes' ELSE 'no' END prm, CASE WHEN (i.status & 16)<>0 THEN 'yes' ELSE 'no' END clu, o.crdate FROM sysusers u JOIN sysobjects o ON u.uid=o.uid JOIN sysindexes i ON o.id=i.id WHERE o.type='U' AND i.status & 64=0 AND i.indid < 255 ORDER BY tablename, prm DESC, i.indid
select indid from sysindexkeys where name='idx_no'--indid=1 聚集索引 indid>1 非聚集 indid=255 具有 text 或 image 数据的表条目
u.name + '.' + o.name tablename,
i.indid,
i.name index_name,
CASE WHEN (i.status & 2)<>0 THEN 'yes' ELSE 'no' END unq,
CASE WHEN (i.status & 2048)<>0 THEN 'yes' ELSE 'no' END prm,
CASE WHEN (i.status & 16)<>0 THEN 'yes' ELSE 'no' END clu,
o.crdate
FROM
sysusers u JOIN
sysobjects o ON u.uid=o.uid JOIN
sysindexes i ON o.id=i.id
WHERE
o.type='U' AND
i.status & 64=0 AND
i.indid < 255
ORDER BY
tablename,
prm DESC,
i.indid
indid>1 非聚集
indid=255 具有 text 或 image 数据的表条目
print '聚集索引'
else
print '非聚集索引'
能不能不用INDEXPROPERTY这个函数来判断呢
因为我用的是Sybase,在Sybase中没有这个函数
if exists(SELECT 1 FROM sysindexes WHERE id=OBJECT_ID('表名') AND name='索引'and indid=1)
print '聚集索引'
else
print '非聚集索引'