Michael,
Thanks for the code example and information. As I wrote to Sergey for his suggestion, I have been given an opportunity to prototype, in VFP, what my co-workers call a "4GL" (we'd call it a framework) for them to use in a re-write of our system.
Gary
>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