Covering indexes in SQL Server 2005

Hi,
To look up information about indexes for a given table in SQL Server
2005, I can use:
sp_helpindex TableName
or
select * from sys.indexes where object_id = object_id('TableName')
But this just gives me information about the column or columns on which
the index has been defined. How do I find out what columns may have
been included in the index?
For example, if I created the following index:
CREATE INDEX IX_1 ON TableName(ColA) INCLUDE(ColB)
Where will I find complete information about IX_1 - which also
indicates whether this is a covering index and if yes - what columns
were included?
Thanks much,
SmithaHi Smitha
You'll need to join sys.indexes with sys.index_columns, and sys.columns.
Here is a view I created to do that:
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
So after creating this view, you can use it as follows:
SELECT * FROM get_index_columns
WHERE object_name = 'TableName'
--
HTH
Kalen Delaney, SQL Server MVP
<smithabreddy@.gmail.com> wrote in message
news:1155047736.933287.185730@.i42g2000cwa.googlegroups.com...
> Hi,
> To look up information about indexes for a given table in SQL Server
> 2005, I can use:
> sp_helpindex TableName
> or
> select * from sys.indexes where object_id = object_id('TableName')
> But this just gives me information about the column or columns on which
> the index has been defined. How do I find out what columns may have
> been included in the index?
> For example, if I created the following index:
> CREATE INDEX IX_1 ON TableName(ColA) INCLUDE(ColB)
> Where will I find complete information about IX_1 - which also
> indicates whether this is a covering index and if yes - what columns
> were included?
> Thanks much,
> Smitha
>|||Hi
Check out sys.index_columns and the is_included_column bit/
John
"smithabreddy@.gmail.com" wrote:
> Hi,
> To look up information about indexes for a given table in SQL Server
> 2005, I can use:
> sp_helpindex TableName
> or
> select * from sys.indexes where object_id = object_id('TableName')
> But this just gives me information about the column or columns on which
> the index has been defined. How do I find out what columns may have
> been included in the index?
> For example, if I created the following index:
> CREATE INDEX IX_1 ON TableName(ColA) INCLUDE(ColB)
> Where will I find complete information about IX_1 - which also
> indicates whether this is a covering index and if yes - what columns
> were included?
> Thanks much,
> Smitha
>

Covering indexes in SQL Server 2005

Hi,
To look up information about indexes for a given table in SQL Server
2005, I can use:
sp_helpindex TableName
or
select * from sys.indexes where object_id = object_id('TableName')
But this just gives me information about the column or columns on which
the index has been defined. How do I find out what columns may have
been included in the index?
For example, if I created the following index:
CREATE INDEX IX_1 ON TableName(ColA) INCLUDE(ColB)
Where will I find complete information about IX_1 - which also
indicates whether this is a covering index and if yes - what columns
were included?
Thanks much,
SmithaHi Smitha
You'll need to join sys.indexes with sys.index_columns, and sys.columns.
Here is a view I created to do that:
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
So after creating this view, you can use it as follows:
SELECT * FROM get_index_columns
WHERE object_name = 'TableName'
HTH
Kalen Delaney, SQL Server MVP
<smithabreddy@.gmail.com> wrote in message
news:1155047736.933287.185730@.i42g2000cwa.googlegroups.com...
> Hi,
> To look up information about indexes for a given table in SQL Server
> 2005, I can use:
> sp_helpindex TableName
> or
> select * from sys.indexes where object_id = object_id('TableName')
> But this just gives me information about the column or columns on which
> the index has been defined. How do I find out what columns may have
> been included in the index?
> For example, if I created the following index:
> CREATE INDEX IX_1 ON TableName(ColA) INCLUDE(ColB)
> Where will I find complete information about IX_1 - which also
> indicates whether this is a covering index and if yes - what columns
> were included?
> Thanks much,
> Smitha
>|||Hi
Check out sys.index_columns and the is_included_column bit/
John
"smithabreddy@.gmail.com" wrote:

> Hi,
> To look up information about indexes for a given table in SQL Server
> 2005, I can use:
> sp_helpindex TableName
> or
> select * from sys.indexes where object_id = object_id('TableName')
> But this just gives me information about the column or columns on which
> the index has been defined. How do I find out what columns may have
> been included in the index?
> For example, if I created the following index:
> CREATE INDEX IX_1 ON TableName(ColA) INCLUDE(ColB)
> Where will I find complete information about IX_1 - which also
> indicates whether this is a covering index and if yes - what columns
> were included?
> Thanks much,
> Smitha
>