SQL Server 2000 和 2005 列出某数据库所有索引的SQL语句 -- 风信Java论坛 -- Powered by JavaWind.Net

运行复制保存 sql代码

--for SQL Server 2005+
SELECT IndexName=IDX.Name,TableName=O.Name,ColumnName=C.Name,
Sort=CASE INDEXKEY_PROPERTY(IDXC.[object_id],IDXC.index_id,IDXC.index_column_id,'IsDescending')
WHEN 1 THEN 'DESC' WHEN 0 THEN 'ASC' ELSE '' END
FROM sys.indexes IDX
INNER JOIN sys.index_columns IDXC
ON IDX.[object_id]=IDXC.[object_id] AND IDX.index_id=IDXC.index_id
INNER JOIN sys.objects O ON O.[object_id]=IDX.[object_id]
INNER JOIN sys.columns C
ON O.[object_id]=C.[object_id] AND O.type='U' AND O.is_ms_shipped=0 AND IDXC.Column_id=C.Column_id
Order by IDX.Name
--for SQL Server 2000+
--by www.javawind.net
select a.name as IndexName,c.name as ColumnName,d.name as TableName,
(CASE INDEXKEY_PROPERTY(d.id,a.indid,c.colid,'IsDescending')
WHEN 1 THEN 'DESC' WHEN 0 THEN 'ASC' ELSE '' END) as Sort
from sysindexes a inner join
sysindexkeys b on a.indid=b.indid And a.id=b.id and b.keyno=1 and a.name not like '_WA_Sys_%'
inner join syscolumns c on c.colid=b.colid And c.id=b.id
inner join sysobjects d on d.id=c.id and d.xtype='U' and d.name<>'dtproperties'
Order by a.name