As a SQL Server user, you may have come across situations where you need to drop user-created statistics. While SQL Server automatically creates statistics when needed, there are instances where DBAs and developers manually create statistics for various reasons. Additionally, if you have used the Database Tuning Advisor, you might have followed its suggestions and created a few indexes.
If you are unsure about the performance of your own statistics or if you simply want to clean up your database, you can use the following script to drop all the user-created statistics:
SELECT DISTINCT 'DROP STATISTICS '
+ SCHEMA_NAME(ob.Schema_id) + '.'
+ OBJECT_NAME(s.object_id) + '.' +
s.name DropStatisticsStatement
FROM sys.stats s
INNER JOIN sys.Objects ob ON ob.Object_id = s.object_id
WHERE SCHEMA_NAME(ob.Schema_id) <> 'sys'
AND Auto_Created = 0 AND User_Created = 1This script ensures that you do not accidentally drop any statistics for the system database. It also includes a check to drop only indexes that are user-created.
Once you run the script, it will generate the T-SQL statements that you can execute to drop all the user-generated indexes.
If you have any other scripts that you use to manage your indexes, feel free to share them in the comments section. I will be happy to publish them on the blog with due credit to you.
For more information on indexes and related topics, you may find the following blog posts helpful:
- How to Find Outdated Statistics? – Interview Question for the Week #081
- SQL SERVER – Find Statistics Update Date – Update Statistics