August 25, 2004 at 8:21 am
I currently have a job running monthly that reindexes all the tables in a DB. I'd like to be able to on reindex a select number of tables. I was thinking of creating a table of tables that need to be re-indexed (or something along those lines). Does anyone know of a script out there that owuld do this for me?
ie: Let's say my DB has 5 tables.
Table1, Table2, Table3, Table4, Table5
I only want to reindex Table 3 and Table 5.
But, I don't want to have the manual names entered in the script if possible. It would be easier to call it from a another table perhaps.
Ideas?
August 25, 2004 at 1:59 pm
Here's a very simple script to do what you are asking. I also have a quite elaborate stored procedure that allows you to reindex the worst (number) of indexes or worst (percentage) of indexes that have a scandensity less that a user defined amount and are weighted according to size. If you would like to have that, send me a private message with your e-mail address, and I'll send it to you.
Steve
declare
@command varchar(500),
@db sysname,
@owner sysname,
@table sysname
-- temporary table for testing and demonstration
create table #reindex_list_table (dbname sysname, owner sysname, tablename sysname)
insert #reindex_list_table
values ('mydb', 'dbo', 'table01')
insert #reindex_list_table
values ('mydb', 'dbo', 'table02')
insert #reindex_list_table
values ('mydb', 'dbo', 'table03')
declare table_cur cursor for
select dbname, owner, tablename from #reindex_list_table
order by dbname, owner, tablename
open table_cur
fetch next from table_cur into @db, @owner, @table
if @@fetch_status = -1
begin
close table_cur
deallocate table_cur
end
else
begin
while @@fetch_status = 0
begin
set @command = 'DBCC DBREINDEX (''' + @db + '.' + @owner + '.' + @table + ''')'
print @command
-- exec (@command)
fetch next from table_cur into @db, @owner, @table
end -- while
close table_cur
deallocate table_cur
end -- if
drop table #reindex_list_table
August 26, 2004 at 1:57 pm
You can also try SQL Server Enterprise Manager -> Tools -> Database Maintenance Planner -> Update Data Optimization Information -> Check Reorganize data and index pages option.
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply