Select only stand alone statistics to drop

• Lowell

  SSC Guru

  Points: 323518

  More actions

  March 2, 2005 at 3:28 pm

  #545049

  apparently a hind_ can be either a hypothetical index or a hypotehetical statistic; note how the tweak the dynamic sql based on the objectproperty:

  from the same MS KB article:

   

  DECLARE @strSQL nvarchar(1024)

  DECLARE @objid int

  DECLARE @indid tinyint

  DECLARE ITW_Stats CURSOR FOR SELECT id, indid FROM sysindexes WHERE name LIKE 'hind_%' ORDER BY name

  OPEN ITW_Stats

  FETCH NEXT FROM ITW_Stats INTO @objid, @indid

  WHILE (@@FETCH_STATUS <> -1)

  BEGIN

  SELECT @strSQL = (SELECT case when INDEXPROPERTY(i.id, i.name, 'IsStatistics') = 1 then 'drop statistics [' else 'drop index [' end + OBJECT_NAME(i.id) + '].[' + i.name + ']'

  FROM sysindexes i join sysobjects o on i.id = o.id

  WHERE i.id = @objid and i.indid = @indid AND

  (INDEXPROPERTY(i.id, i.name, 'IsHypothetical') = 1 OR

  (INDEXPROPERTY(i.id, i.name, 'IsStatistics') = 1 AND

  INDEXPROPERTY(i.id, i.name, 'IsAutoStatistics') = 0)))

  EXEC(@strSQL)

  FETCH NEXT FROM ITW_Stats INTO @objid, @indid

  END

  CLOSE ITW_Stats

  DEALLOCATE ITW_Stats

  Lowell

  ---

  --help us help you! If you post a question, make sure you include a CREATE TABLE... statement and INSERT INTO... statement into that table to give the volunteers here representative data. with your description of the problem, we can provide a tested, verifiable solution to your question! asking the question the right way gets you a tested answer the fastest way possible!