Tags: column, database, dbwhile, delete, frombeing, idea, microsoft, mysql, oracle, prevent, script, server, sql, statistics, stats

delete all statistics in db

On Database » Microsoft SQL Server

2,721 words with 2 Comments; publish: Sat, 07 Jun 2008 21:05:00 GMT; (250187.50, « »)

Hi,

anybody have a script or an idea on how to delete all stats in a db?

while on the topic why do some stats (column I think) prevent column from

being upgraded via alter table statement? I would have expected stats to be

dropped as soon as column modified.

Thanks.

All Comments

Leave a comment...

  • 2 Comments
    • Try the following post from Dan Guzman:

      http://groups.google.com/group/micro...5ec3c61?hl=en&

      The requirement is listed in books online under the alter

      table topic in the section alter column - the limitation is

      from statistics generated by an explicit create statistics

      statement. I think you also can hit issues if you have

      hypothetical index left behind from the Index tuning wizard.

      From books online:

      The altered column cannot be:

      Used in statistics generated by the CREATE STATISTICS

      statement. First remove the statistics using the DROP

      STATISTICS statement. Statistics automatically generated by

      the query optimizer are automatically dropped by ALTER

      COLUMN.

      -Sue

      On Tue, 13 Sep 2005 15:03:01 -0700, "sysbox27"

      <sysbox27.sqlserver.questionfor.info.discussions.microsoft.com> wrote:

      >Hi,

      >anybody have a script or an idea on how to delete all stats in a db?

      >while on the topic why do some stats (column I think) prevent column from

      >being upgraded via alter table statement? I would have expected stats to be

      >dropped as soon as column modified.

      >Thanks.

      #1; Sat, 07 Jun 2008 21:07:00 GMT
    • Thanks very much Sue.

      "Sue Hoegemeier" wrote:

      > Try the following post from Dan Guzman:

      > http://groups.google.com/group/micro...5ec3c61?hl=en&

      > The requirement is listed in books online under the alter

      > table topic in the section alter column - the limitation is

      > from statistics generated by an explicit create statistics

      > statement. I think you also can hit issues if you have

      > hypothetical index left behind from the Index tuning wizard.

      > From books online:

      > The altered column cannot be:

      > Used in statistics generated by the CREATE STATISTICS

      > statement. First remove the statistics using the DROP

      > STATISTICS statement. Statistics automatically generated by

      > the query optimizer are automatically dropped by ALTER

      > COLUMN.

      > -Sue

      > On Tue, 13 Sep 2005 15:03:01 -0700, "sysbox27"

      > <sysbox27.sqlserver.questionfor.info.discussions.microsoft.com> wrote:

      >

      >

      #2; Sat, 07 Jun 2008 21:08:00 GMT