Here's a script that I use to retrieve index info. You should be albe to modify it to meet your needs:
SET NOCOUNT ON
DECLARE @i int
DECLARE @indexoffset TABLE (i int)
SET @i = 1
WHILE @i <= 16
BEGIN
INSERT INTO @indexOffset VALUES (@i)
SET @i = @i + 1
END
SELECT
o.name
,i.name
,io.i AS column_sequence
,INDEX_COL(o.name, i.indid, io.i) AS column_name
,CASE INDEXKEY_PROPERTY(o.id, i.indid, io.i, 'isDescending')
WHEN 0 THEN 'ASC'
WHEN 1 THEN 'DESC'
END AS column_direction
FROM
sysobjects o
INNER JOIN sysindexes i ON o.id = i.id
CROSS JOIN @indexOffset io
WHERE
OBJECTPROPERTY(o.id, 'isUserTable') = 1
AND i.indid BETWEEN 1 AND 254
AND INDEX_COL(o.name, i.indid, io.i) IS NOT NULL
AND INDEXPROPERTY(o.id, i.name, 'IsStatistics') = 0
ORDER BY
o.name
,i.name
,io.i
>>I want to know in order to prevent a possible LONG query that's not using an indexed field.
Having a column as a member of an index does not mean that SQL Server will use the index to solve the query. In order for an index to really be useful, the first column (high-order column) of the index should be referenced by the query, perferable in the WHERE clause.
As an example, take the following query:
SELECT * FROM employee WHERE name_last = @p1
An index in the form of (name_last, name_first) would prove to be useful (let's assume that name_last is selective) while (name_first, name_last) is not has useful. In the former case, SQL Server will likely perform an Index Seek while in the latter, an Index Scan. Now an Index Scan is better than a table scan (non-clustered indexes of course) but still not as good as the Index Seek.
-Mike
>Does anyone know how to determine if a field from a SQL Server table is part of an index on that table? I want to know in order to prevent a possible LONG query that's not using an indexed field. I have been studying the system tables, but can't seem to figure it out how to determine that fact. Thanks
>
>Gary