List of all indexes - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

General SQL Server Forums

New to SQL Server Programming

List of all indexes

Author

Topic

abc123
Starting Member

47 Posts

Posted - 2009-03-19 : 03:35:25

I want the give list of all indexes (clustered and nonclustered)
created on the all user created table

I have written following query

select b.name,a.name
from sysobjects a
inner join sysindexes b
on a.id = b.id
left join INFORMATION_SCHEMA.TABLE_CONSTRAINTS c
on a.name = c.TABLE_NAME
and c.CONSTRAINT_NAME = b.name
and c.CONSTRAINT_TYPE <> 'PRIMARY KEY'
where a.xtype='U'

It is returning folllowing data

PK_table1 table1
_WA_Sys_0000000C_22AA2996 table1
_WA_Sys_00000002_22AA2996 table1

i want only 1st row don't want 2 and 3 .

robvolk
Most Valuable Yak

15732 Posts

Posted - 2009-03-19 : 07:00:13

select b.name,a.name
from sysobjects a
inner join sysindexes b
on a.id = b.id
left join INFORMATION_SCHEMA.TABLE_CONSTRAINTS c
on a.name = c.TABLE_NAME
and c.CONSTRAINT_NAME = b.name
and c.CONSTRAINT_TYPE <> 'PRIMARY KEY'
where a.xtype='U'
and Indexproperty(b.id, b.name, 'IsStatistics')=0

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources