March 18, 2005 at 4:19 am
how can I get the name of index and its associated table by a single query.pls suggest
March 18, 2005 at 5:24 am
Are you after something like this?
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 Kalis
Microsoft SQL Server MVP
Webmaster: http://www.insidesql.org/blogs
My blog: http://www.insidesql.org/blogs/frankkalis/[/url]
March 18, 2005 at 6:26 am
Or to meet just your criteria you could do
Select object_name([id]) table_name, [name] index_name, indid index_id From Sysindexes
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply