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

唧唧

大小楼主 SQL Server 2000 和 2005 列出某数据库所有索引的SQL语句 Post by : 2010-11-17 18:11:57.0

幼儿园
UID：3
主题：342
帖子：781
加为好友发送短信

运行复制保存 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

　★★★★★★★★
　纵里寻她千百度，蓦然回首，那人却在，灯火阑珊处！
　MyBlog ：http://blog.javawind.net

唧唧精华：0 积分：0 金钱：3172 金币：0 威望：0 权限：2 用户资料用户帖子	大小楼主 SQL Server 2000 和 2005 列出某数据库所有索引的SQL语句 Post by : 2010-11-17 18:11:57.0
幼儿园 UID：3 主题：342 帖子：781 加为好友发送短信	运行复制保存 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
	★★★★★★★★ 　纵里寻她千百度，蓦然回首，那人却在，灯火阑珊处！　MyBlog ：http://blog.javawind.net