How to Drop All the User Created Statistics by SQL Server? – Interview Question of the Week #158

Question: How to Drop All the User Created Statistics by SQL Server?

Answer: A very popular question I often see being discussed in various forums.

SQL Server automatically creates the statistics when needed if you have enabled “auto create statistics” settings at the database level. However, sometimes due to various reasons, DBAs and Developer creates the statistics manually. Additionally, if you were a fan of the Database Tuning Advisor, you may have followed the suggestions and created few indexes. If you are not sure about how your own statistics are doing. You may consider to drop them using following script.

The script above makes sure that we accidentally do not drop any statistics for the system database. Additionally, it has a check to drop only indexes which are user created. When you run above script it will generate T-SQL which eventually you can run to drop all the user-generated indexes.

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 = 1

Let me know if you have any other similar script which you are using to manage your indexes. If you post your script in the comments section, I will publish it on the blog with due credit to you.

Here are few other related blog posts to indexes:

• How to Find Outdated Statistics? – Interview Question for the Week #081
• SQL SERVER – Find Statistics Update Date – Update Statistics

Reference: Pinal Dave (https://blog.sqlauthority.com)