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 Administration
 sys.indexes

Author  Topic 

ahmeds08
Aged Yak Warrior

737 Posts

Posted - 2011-04-12 : 01:58:37
hello everybody,
when i do SELECT * FROM SYS.INDEXES i get all the indexes on database,is the index_id there is not unique?
thanx in advance

raghuveer125
Constraint Violating Yak Guru

285 Posts

Posted - 2011-04-12 : 02:32:19
Try this and let me know what are you looking for

This works on MSSQL-2005

select * from sys.dm_db_index_physical_stats(db_id(),null,null,null,'detailed') s inner join sys.indexes I on s.index_id=I.index_id and s.object_id=I.object_id
where s.index_id=I.index_id and s.object_id=I.object_id

Raghu' S
Go to Top of Page

ahmeds08
Aged Yak Warrior

737 Posts

Posted - 2011-04-12 : 02:50:17
quote:
Originally posted by raghuveer125

Try this and let me know what are you looking for

This works on MSSQL-2005

select * from sys.dm_db_index_physical_stats(db_id(),null,null,null,'detailed') s inner join sys.indexes I on s.index_id=I.index_id and s.object_id=I.object_id
where s.index_id=I.index_id and s.object_id=I.object_id

Raghu' S



Thank you Raghu,I got good info from your post
Go to Top of Page

GilaMonster
Master Smack Fu Yak Hacker

4507 Posts

Posted - 2011-04-12 : 06:27:17
Index id is only unique within a table, same as index name, not in the database as a whole.

--
Gail Shaw
SQL Server MVP
Go to Top of Page

Jahanzaib
Posting Yak Master

115 Posts

Posted - 2011-04-16 : 00:39:05
Index Id should be unique for a specific table like

If Table A have 4 index then index idies are 1,2,3,4

If Table B have 4 index then index ideies are 1,2,3,4

same but it can be bifurcate with the help of table(object_id)

Regards,
Syed Jahanzaib Bin Hassan
MCTS,MCITP,OCA,OCP,OCE,SCJP,IBMCDBA
Go to Top of Page
   

- Advertisement -