SQL Server Performance Forum – Threads Archive

Index Details of a Table in SQL Server

If i want to see the Index deatils for a given table say ‘ABC’ how can i do it? I need INDEX_NAME, COLUMN_NAME, TABLE_NAME, INDEX_TYPE columns for
In oracle i am able to find it out from the query mentioned below,
select B.INDEX_NAME, B.COLUMN_NAME, B.TABLE_NAME, A.INDEX_TYPE from USER_INDEXES A, USER_IND_COLUMNS B
where B.TABLE_NAME = ‘ABC’ AND A.INDEX_NAME = B.INDEX_NAME
order by 3;
Please tell me how to do it in SQL Server? My mail ID is, [email protected]
Regards…
Sujit (moved from Analaysis DW forum)

---

This might be a starter
SELECT
CAST(SO.[name] AS CHAR(20)) AS TableName
, CAST(SI.[name] AS CHAR(30)) AS IndexName
, CAST(SC.[name] AS CHAR(15)) AS ColName
, CAST(ST.[name] AS CHAR(10)) AS TypeVal
, CASE
WHEN (SI.status & 16)<>0 THEN ‘Yes’ ELSE ‘No’
END AS ClusteredIndex
FROM
SYSOBJECTS SO
INNER JOIN
SYSINDEXES SI
INNER JOIN
SYSINDEXKEYS SIK
ON
SIK.[id] = SI.[id]
AND
SIK.indid = SI.indid
INNER JOIN
SYSCOLUMNS SC
INNER JOIN
SYSTYPES ST
ON
SC.xtype = ST.xtype
ON
SIK.[id] = SC.[id]
AND
SIK.colid = SC.colid
ON
SO.[id] = SI.[id]
WHERE
SO.xtype = ‘u’
AND
SI.indid > 0
AND
SI.indid < 255
AND
(SI.status & 64)=0
ORDER BY
TableName
, IndexName
, SIK.keyno —
–Frank
http://www.insidesql.de

---

SP_HELPINDEX returns the informaton about the indexes on a table or view. Satya SKJ
Moderator
http://www.SQL-Server-Performance.Com/forum
This posting is provided “AS IS” with no rights for the sake of knowledge sharing.

---

If you also need statistics and filegroup, let me know. Luis Martin
Moderator
SQL-Server-Performance.com All postings are provided “AS IS” with no warranties for accuracy.

---

You mean something like this, Luis?<br /><pre><br />USE PUBS<br />GO<br />SELECT <br /> CAST(OBJECT_NAME(sysind.id) AS CHAR(20)) AS TableName<br /> , CAST(sysind.name AS CHAR(30)) AS IdxName<br /> , CAST(sysfg.groupname AS CHAR(10)) AS GroupName<br />FROM <br /> sysindexes sysind <br />INNER JOIN <br /> sysfilegroups sysfg<br />ON <br /> sysind.groupid = sysfg.groupid<br /> INNER JOIN <br /> sysobjects sysobj<br /> ON <br /> sysind.id = sysobj.id<br />WHERE <br /> sysobj.xtype &lt;&gt; ‘S’ <br />AND <br /> sysind.name NOT LIKE ‘_WA%’ <br />ORDER BY<br /> sysind.TableName<br /></pre><br />Or this<br /><pre><br />SELECT<br /> sysFile.groupid AS GroupID<br /> , SUBSTRING(sysFile.groupname,1,30) AS FilegroupName<br /> , SUBSTRING(sysObj.name,1,30) AS ObjectName<br />FROM <br /> sysobjects sysObj<br />INNER JOIN <br /> sysindexes sysIdx <br />ON sysObj.id = sysIdx.id<br /> INNER JOIN <br /> sysfilegroups sysFile<br /> ON <br /> sysIdx.groupid = sysFile.groupid<br />WHERE<br /> sysIdx.indid = 0 and sysObj.xtype = ‘U’ <br />ORDER BY<br /> sysFile.groupname, sysObj.ObjectName <br /></pre><br /><br />[<img src=’/community/emoticons/emotion-2.gif’ alt=’‘ />]<br /><br /><br />–<br />–Frank<br /<a target="_blank" href=http://www.insidesql.de>http://www.insidesql.de</a><br />

---

Thanx FrankKalis, The query was really helpful.
Regards….
Sujit

---

Indeed Frank.
Luis Martin
Moderator
SQL-Server-Performance.com All postings are provided “AS IS” with no warranties for accuracy.

---

Hello, I am reading your queries as i got similar requirement to get index name. I just want to know what do you means by below line "(SI.status & 16)<>0" Please let me know. Thanks & Regards
Ravi Kumar

---

It gets obvious from the context. status & 16 <> 0 identifies a clustered index.
But to be honest, that is *very* deep inside the system tables, I wouldn’t use this in production code —
Frank Kalis
SQL Server MVP
http://www.insidesql.de

---

I suppose below command is the safest to be run on production server, it provides all required infomations for index_name, index_description and index_keys at one go.. EXEC sp_msforeachtable @command1= "PRINT ‘?’ EXEC sp_helpindex @objname =’?’" Deepak Kumar –An eye for an eye and everyone shall be blind

---

]]>