How to Reindex only tables with more than 1000 records

Question

How to Reindex only tables with more than 1000 records

Mias12

SSC Rookie

Points: 32
More actions
April 26, 2012 at 11:04 am

#392126

Hi, I was wondering if there is a way to Re-index only the tables in my database that have more than 1000 records.

Viewing 5 posts - 1 through 4 (of 4 total)

You must be logged in to reply to this topic. Login to reply

chrisfradenburg SSCrazy Eights Points: 9592 More actions · Answer 1

You can write a script to look at sys.partitions and then call the alter index command only on objects with more than 1000 rows. A better bet may be to look at Ola Hallengren's maintenance plans[/url] or SQL Fool's Index Defrag Script.[/url]

Geoff A SSChampion Points: 11511 More actions · Answer 2

declare @table varchar(128)

declare @cmd varchar(500)

declare @dbname varchar(128)

declare @RowCnt int

declare @myvalues table (dbname varchar(128), RowCnt int)

declare tables cursor for

select table_name from information_schema.tables

where table_type = 'base table'

open tables

fetch next from tables into @table

while @@fetch_status = 0

begin

set @cmd = 'select ''' + @table + ''', count(*) from ' + @table

insert into @myvalues

exec (@cmd)

fetch next from tables into @table

end

CLOSE tables

DEALLOCATE tables

declare Results cursor for

select * from @myvalues

open Results

fetch next from Results into @dbname, @RowCnt

while @@fetch_status = 0

begin

BEGIN

declare @mycmd varchar(200)

set @mycmd = 'DBCC DBREINDEX ("'+@dbname+'", " ", 90)'

if @RowCnt > 1000

exec (@mycmd)

END

fetch next from Results into @dbname, @RowCnt

end

CLOSE Results

DEALLOCATE Results

Gail Shaw SSC Guru Points: 1004504 More actions · Answer 3

Mias12 (4/26/2012)
Hi, I was wondering if there is a way to Re-index only the tables in my database that have more than 1000 records.

The general recommendation for what tables to reindex is 1000 pages, not 1000 records.

Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

We walk in the dark places no others will enter
We stand on the bridge and no one may pass

Mias12 SSC Rookie Points: 32 More actions · Answer 4

Mias12

SSC Rookie

Points: 32

April 26, 2012 at 2:23 pm

#1480083

Thank you so much for the help!