Tuesday, August 28, 2007

Getting an index's information

I was asked by one of the client to get all info about the indexes i.e name of the index , column's name, table's name etc. Please take a look at following script to return the data for SQL Server 2000.
use pubs

SELECT tbl = object_name(i.id), i.name as index_name, c.name as column_name,
isunique = indexproperty(i.id, i.name, 'IsUnique'),
isclustered = indexproperty(i.id, i.name, 'IsClustered'),
constrtype = CASE o.type
WHEN 'PK' THEN 'PRIMARY KEY'
WHEN 'UQ' THEN 'UNIQUE'
END
FROM sysindexes i
JOIN syscolumns c on i.id = c.id
JOIN sysindexkeys k on i.id = k.id
and i.indid = k.indid
and c.colid = k.colid
LEFT JOIN sysobjects o ON o.name = i.name
AND o.xtype in ('PK', 'UQ')
AND o.parent_obj = i.id
WHERE indexproperty(i.id, i.name, 'IsHypothetical') = 0
AND indexproperty(i.id, i.name, 'IsStatistics') = 0
AND indexproperty(i.id, i.name, 'IsAutoStatistics') = 0
AND objectproperty(i.id,'IsMSShipped')=0
ORDER BY tbl, i.name, k.keyno

For SQL Server 2005 I use the script written by Kalen Delaney.Remember there is new type of index called INCLUDE.

CREATE VIEW get_index_columns
AS
SELECT object_name(ic.object_id) as object_name , index_name = i.name,
'column' = c.name,
'column usage' = CASE ic.is_included_column
WHEN 0 then 'KEY'
ELSE 'INCLUDED'
END
FROM sys.index_columns ic JOIN sys.columns c
ON ic.object_id = c.object_id
AND ic.column_id = c.column_id
JOIN sys.indexes i
ON i.object_id = ic.object_id
AND i.index_id = ic.index_id

After creating the view, you can select from it, and it will give you the
KEY columns and the INCLUDED columns in all the indexes in all the tables.
Or, you can add a WHERE clause for your own table or index:

SELECT * FROM get_index_columns
WHERE object_name = 'mytable'

2 comments:

haiyan said...

Delivering a new dimension for hardcore Maple Story players with the addition of a fourth job and some free maple mesos, this is launched today. Maple Story players will have reached a high enough level to achieve the 4th Job or some necessary maplestory mesos, which begins this week. Many people can cost their own cheap mesos to gain themed dessert and costumes among their many posts. The addition of the 4th Job provides further depth to an already intricate and complex maple story mesos in game. At Leafre, players can find new upgrades for some of the games more powerful weapons can receive from mesos and also party with other elite players.

aiya said...

Office 2010is powerful!
Microsoft Office 2010is the best software in the world.
Microsoft wordis so great!
Office 2007makes life great!
Many people likeMicrosoft Office.
Microsoft Office 2007is welcomed by the whole world.
Office 2007 keyis available here.
Office 2007 downloadis on sale now!
Office 2007 Professionalbring me so much convenience.
Outlook 2010 make life wonderful!
Microsoft outlook is my love.
Microsoft outlook 2010 is convenient!
Windows 7 is convenient!