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