Get your favorite SSC scripts directly in SSMS with the free SQL Scripts addin.
Search for scripts directly from SSMS, and instantly access any saved scripts in your
SSC briefcase from the favorites tab.
Download now (direct download link)
Thank this author by sharing:
The reason I wrote this script was to help locate the queries using indexes. Sql Server 2005 allows you to see exactly how your indexes are being used, and sometimes you spot one that has only been used a few times, or maybe one that only gets scanned, with no seeks. You want to find where the indexes are being used so you can fix the queries somehow.
Note that the query ignores indexes in the sys schema, and it also ignores any index references it finds directly under "Update" nodes, so you can focus on where the indexes are actually being used.
This can take a long time to run. On my system ( a few hundred tables, 30Gb data size), it took almost 30 minutes and returned over 100,000 rows. You should select the contents into a temp table and go from there with your analysis.
Disabling index by using select query
defining query to use an existing index
Query about Partition the table & Index
Query to get tables with no clustered indexes(2K5,2K8,2K8 R2)