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.

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

Solarwinds

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:

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

Solarwinds
, , , ,
Previous Post
How to Find DISTINCT COUNT of Column Rows Without Using Distinct Count Keywords? – Interview Question of the Week #157
Next Post
How Does TOP WITH TIES Work in SQL Server? – Interview Question of the Week #159

Related Posts

3 Comments. Leave new

  • Shaun Austin
    April 3, 2018 3:03 am

    “Additionally, it has a check to drop only STATISTICS which are user created”

    Thanks for sharing.

    Reply
  • Just a cosmetic change to the script above quoting the object names:

    SELECT DISTINCT ‘DROP STATISTICS ‘
    + QUOTENAME(SCHEMA_NAME(ob.Schema_id)) + ‘.’
    + QUOTENAME(OBJECT_NAME(s.object_id)) + ‘.’ +
    QUOTENAME(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

    Reply

Leave a Reply

Menu