Drop user statistics

,

This script drops manually added statistics. By setting the @debug flag to 1, this script shows the actual statement without executing it.

You can add a table filtercriteria, by default all tables are processed. Note statistics on table [queue_messages] are ignored (you can't drop these).

NOTE: Make sure you realize the consequences if you run this script. Statistics are a base source for the optimizer. After you've dropped statistics, performance might be an issue.

 

 

if exists (select 1 from information_schema.routines where routine_name = 'usp_drop_userstats' and routine_type ='procedure')
    drop procedure usp_drop_userstats 
go

create procedure usp_drop_userstats @tablename sysname = '%', @verbose bit = 1, @debug bit = 0
as
    declare @SQLCmd nvarchar(max)
    declare @statstable sysname
    declare @statsname sysname

    declare c_drop_userstats cursor for
        select object_name(object_id), name
        from sys.stats
        where user_created = 1
        and object_name(object_id) not like 'queue_messages%'
        and object_name(object_id) like @tablename

    open c_drop_userstats
    fetch next
    from c_drop_userstats
    into @Statstable, @statsname

    while @@fetch_status = 0

        begin

            set @SQLCmd = 'drop statistics ' + quotename(@statsTable) + '.' + quotename(@StatsName)
            if @debug = 0    
                begin
                    if @verbose = 1 print 'Dropping statistics ' + @StatsName + ' on table ' + @statsTable
                    exec (@SQLCmd)
                end
            else
                print @SQLCmd

            fetch next
            from c_drop_userstats
            into @statstable, @statsname

        end

    close c_drop_userstats
    deallocate c_drop_userstats
GO

Rate

Share

Share

Rate