dropping all statistics

Hi,
I'm wondering if there is a command I can use to drop all system & user
created statistics on a table? And then I can loop through the database and
get rid of all the statistics for the entire database. Thus, if it's not a
command but some SQL code then I can use that too.
I'm running some upgrade scripts for my application and it often fails on
dropping or altering tables based on statistics it doesn't know about.
Thanks,
mattTry below. Untested, just wrote it. Change the PRINT to EXEC(@.sql) to actually execute the DROP
statements:
DECLARE @.tblname sysname, @.statname sysname, @.sql nvarchar(2000)
DECLARE c CURSOR FOR
SELECT object_name(id), name FROM sysindexes WHERE INDEXPROPERTY(id, name, 'IsStatistics') = 1
OPEN c
FETCH NEXT FROM c INTO @.tblname, @.statname
WHILE @.@.FETCH_STATUS = 0
BEGIN
SET @.sql = 'DROP STATISTICS [' + @.tblname + '].[' + @.statname + ']'
PRINT @.sql
FETCH NEXT FROM c INTO @.tblname, @.statname
END
CLOSE c
DEALLOCATE c
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
http://www.sqlug.se/
"matt" <matt@.discussions.microsoft.com> wrote in message
news:6784F0CC-E641-45C8-A3B6-355C537E1FEC@.microsoft.com...
> Hi,
> I'm wondering if there is a command I can use to drop all system & user
> created statistics on a table? And then I can loop through the database and
> get rid of all the statistics for the entire database. Thus, if it's not a
> command but some SQL code then I can use that too.
> I'm running some upgrade scripts for my application and it often fails on
> dropping or altering tables based on statistics it doesn't know about.
> Thanks,
> matt|||Thanks! That works perfectly. All I needed was the index property to check
for stats, but I'll take the script. All worked well.
Matt
"Tibor Karaszi" wrote:
> Try below. Untested, just wrote it. Change the PRINT to EXEC(@.sql) to actually execute the DROP
> statements:
> DECLARE @.tblname sysname, @.statname sysname, @.sql nvarchar(2000)
> DECLARE c CURSOR FOR
> SELECT object_name(id), name FROM sysindexes WHERE INDEXPROPERTY(id, name, 'IsStatistics') = 1
> OPEN c
> FETCH NEXT FROM c INTO @.tblname, @.statname
> WHILE @.@.FETCH_STATUS = 0
> BEGIN
> SET @.sql = 'DROP STATISTICS [' + @.tblname + '].[' + @.statname + ']'
> PRINT @.sql
> FETCH NEXT FROM c INTO @.tblname, @.statname
> END
> CLOSE c
> DEALLOCATE c
>
> --
> Tibor Karaszi, SQL Server MVP
> http://www.karaszi.com/sqlserver/default.asp
> http://www.solidqualitylearning.com/
> http://www.sqlug.se/
>
> "matt" <matt@.discussions.microsoft.com> wrote in message
> news:6784F0CC-E641-45C8-A3B6-355C537E1FEC@.microsoft.com...
> > Hi,
> >
> > I'm wondering if there is a command I can use to drop all system & user
> > created statistics on a table? And then I can loop through the database and
> > get rid of all the statistics for the entire database. Thus, if it's not a
> > command but some SQL code then I can use that too.
> >
> > I'm running some upgrade scripts for my application and it often fails on
> > dropping or altering tables based on statistics it doesn't know about.
> >
> > Thanks,
> > matt
>
>