Find and Kill All System-Named Statistics

I hate system-named objects.

Some of this is to be expected from someone like me, who, depending on the decade, could be called "persnickety", "perfectionist", "OCD", or "The Anal Retentive Chef." But there's also a very practical reason to name all your database objects.

If you're using a third-party tool to synch up database schemas, you may find that every time you copy a system-named object from one side of the synch to the other, an entirely new name is generated. For example, you might have a statistic object named dbo.Customers._WA_Sys_00000001_45544755 on the left-hand side of the synch, but when you tell the tool to copy it to the right-hand side, you get a statistic named dbo.Customers._WA_Sys_00000001_436BFEE3.

Well, that won't do.

Below is a stored procedure that locates and drops all system named statistics. After you run this, just create a new statistic object with a name that follows your SQL nomenclature guidelines. (Whaddaya mean you don't have any? Get some!)

-- Comment. 
SET QUOTED_IDENTIFIER ON
GO
SET ANSI_NULLS ON
GO


-------------------------------------------------------------------------------
-- Drops all system-named statistics.                                          
-------------------------------------------------------------------------------

CREATE PROCEDURE dbo.usp_CleanupStatisticsWithSysNames
AS
BEGIN
    SET NOCOUNT ON

    DECLARE @Cmd        NVARCHAR(MAX) = ''
    DECLARE @SchemaName sysname       = ''
    DECLARE @TableName  sysname       = ''
    DECLARE @StatName   sysname       = ''

    DECLARE cur CURSOR FOR
        SELECT m.name   AS SchemaName
             , t.name   AS TableName
             , s.name   AS StatName
          FROM sys.stats           s
          JOIN sys.stats_columns   sc
            ON s.object_id = sc.object_id
           AND s.stats_id  = sc.stats_id
          JOIN sys.tables          t
            ON s.object_id = t.object_id
          JOIN sys.schemas         m
            ON t.schema_id = m.schema_id
         WHERE (s.name LIKE '\_WA\_Sys\_%' ESCAPE '\'  OR  s.auto_created = 1)
           AND s.object_id > 100
      ORDER BY SchemaName
             , TableName
             , StatName

    OPEN cur
    FETCH NEXT FROM cur INTO @SchemaName, @TableName, @StatName

    WHILE @@FETCH_STATUS = 0
    BEGIN
        SET @Cmd = 'DROP STATISTICS ' + QUOTENAME(@SchemaName) + '.' + QUOTENAME(@TableName) + '.' + QUOTENAME(@StatName)
        RAISERROR(@Cmd, 10, 1) WITH NOWAIT
        EXEC sp_executesql @Cmd
        FETCH NEXT FROM cur INTO @SchemaName, @TableName, @StatName
    END

    CLOSE cur
    DEALLOCATE cur
END

Rumor has it that the "WA" in the system-generated names stands for "Washington", as in "Redmond".