Dropping the Statistics

[Adam]

Does anyone have any generic scripts that Drop all the statistics that SQL auto generates?? I have hundreds of '_WA_....'
indicies that are auto created by SS7 and I just want to get rid of ALL of them. Thanks!

[Sergei Didur]

Try this SP:

use master
go

IF EXISTS (SELECT * FROM sysobjects WHERE id = OBJECT_ID('sp_drop_statistic&#39 AND sysstat & 0xf = 4)
DROP PROCEDURE sp_drop_statistic
GO

CREATE PROCEDURE sp_drop_statistic
(@tab_name sysname = '%&#39

AS

DECLARE @idx_name sysname,
@cmd varchar(255)

DECLARE CUR_Statistic CURSOR LOCAL FOR
SELECT
tab_name = sysobjects.name,
idx_name = sysindexes.name
FROM sysobjects
INNER JOIN sysindexes ON sysindexes.id = sysobjects.id
WHERE sysobjects.type = 'U'
AND sysobjects.name LIKE @tab_name
AND sysindexes.status & 32 = 32
ORDER BY
sysobjects.name,
sysindexes.name

OPEN CUR_Statistic

FETCH NEXT FROM CUR_Statistic INTO @tab_name, @idx_name
WHILE (@@FETCH_STATUS = 0) BEGIN
SET @cmd = 'DROP STATISTICS ' + @tab_name + '.' + @idx_name
PRINT @cmd
EXECUTE (@cmd)
IF (@@error != 0) BREAK

FETCH NEXT FROM CUR_Statistic INTO @tab_name, @idx_name
END

CLOSE CUR_Statistic
DEALLOCATE CUR_Statistic

GO

------------
Adam at 11/17/00 11:56:56 AM

Does anyone have any generic scripts that Drop all the statistics that SQL auto generates?? I have hundreds of '_WA_....'
indicies that are auto created by SS7 and I just want to get rid of ALL of them. Thanks!

[Ray Miao]

It may affect the performance by dropping them.


------------
Adam at 11/17/00 11:56:56 AM

Does anyone have any generic scripts that Drop all the statistics that SQL auto generates?? I have hundreds of '_WA_....'
indicies that are auto created by SS7 and I just want to get rid of ALL of them. Thanks!

[Adam]

I'm aware of that. We already generate our own indicies. The system generate ones probably were created during the development process of the app.
Our application searches on the specific indicies we created. Thanks for the info though.


------------
Ray Miao at 11/17/00 1:26:11 PM

It may affect the performance by dropping them.


------------
Adam at 11/17/00 11:56:56 AM

Does anyone have any generic scripts that Drop all the statistics that SQL auto generates?? I have hundreds of '_WA_....'
indicies that are auto created by SS7 and I just want to get rid of ALL of them. Thanks!