SQL Server Performance Forum – Threads Archive

Indexes Scripts

i have to write a queries the get the indexes name created on different table in a database. i have wrote below queries. Can any one let me know is the right way to get index name(both cluster and noncluster indexes). select o.name,i.name from sysobjects o,sysindexes i
where o.id=i.id
and i.indid=0
and o.xtype=’U’ Thanks and Regards
Ravi Kumar

---

from sp_help and sp_helpindex , you can get the indexes propeties.
following also will do
select name,object_Name(id) from sysindexes where indid < 255

---

Run below command for each databases: -<br /><br />EXEC sp_msforeachtable @command1= "PRINT ‘?’ EXEC sp_helpindex @objname =’?’"<br /><br /><br /><img src=’/community/emoticons/emotion-1.gif’ alt=’‘ /><br /><br /><br /><br />Deepak Kumar<br /><br />–An eye for an eye and everyone shall be blind

---

Is this helpful?
select *
from INFORMATION_SCHEMA.CONSTRAINT_COLUMN_USAGE
Madhivanan Failing to plan is Planning to fail

---

Check this topic, may this proves to be fruitful
http://www.sql-server-performance.com/forum/topic.asp?TOPIC_ID=5449

---

I think it is. I use type = ‘u’ instead of xtype = ‘u’. I don’t have MSSQL Server installed here, so I can’t check in BOL if there is a difference between type and xtype related to your query. Btw, Microsoft discourages using system tables (some of them are actually views) because they may make them obsolate in further releases. They recommend using system stored procedures or informationSchema views (as mentioned above).

---

Agreed,
This should be used as last option not the first option

---

Hello, If you see in sysobjects table, it contain object types in xtype and type column. Only difference which i can see in both the column is that if object type of Unique key in Xtype and type column.Please let me know in case you know more about it.
Thanks for points you made about system tables. Thanks and Regards
Ravi Kumar

---

]]>