Question:
The goal is to watch queries for their use, nonuse, or abuse of indices (using SQL Server 6.5). One of the problems is to be sure that a given index is no longer in use. If an index is dropped, is there any way to watch for a table scan–either on this particular table or in general.

Answer:
The best way I know of would be to use the trace utility provided with SQL Server to save a log of activity before the index drop and then compare it to a trace log generated after the index is dropped. The trace log shows the SQL from each statement and the response time. You would look for queries that use that table whose response times have changed significantly. I would suggest loading the trace log into a SQL table to help facilitate the comparison. You can find documentation on the table structure in the online books.

You could then run those same queries in isqlw using both showplan and show statistics to test whether a table scan is occurring.