Table level reindexing - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

General SQL Server Forums

New to SQL Server Administration

Table level reindexing

Author

Topic

Ravikumarc
Yak Posting Veteran

51 Posts

Posted - 2010-06-18 : 19:22:21

Dear All,

I have 100 tables in a database and here I have to do table level reindexing.Please let me know how to do reindexing for first 50 tables and then other half of 50 tables.if there is any script to achieve this pls let me know.

Thanks & Regards
Ravi

robvolk
Most Valuable Yak

15732 Posts

Posted - 2010-06-18 : 20:40:49

[code]declare @sql varchar(max)

set @sql=''
select top 50 percent @sql=@sql + 'ALTER INDEX ALL ON ' +
quotename(object_schema_name(object_id)) + '.' +
quotename(name) + ' REBUILD'
from sys.tables
order by name asc

exec(@sql)

set @sql=''
select top 50 percent @sql=@sql + 'ALTER INDEX ALL ON ' +
quotename(object_schema_name(object_id)) + '.' +
quotename(name) + ' REBUILD'
from sys.tables
order by name desc

exec(@sql)[/code]

Kristen
Test

22859 Posts

Posted - 2010-06-19 : 04:33:43

What does SQL do with 50% if there are an odd number Rob?

I guess reindex twice is ok, but if piggy-in-the-middle didn't get reindexed (until another table was created

) bit of a shame.


IF OddNumberOfTables
BEGIN
   IF EXISTS TableCalled("FooBar")
       DROP TABLE "FooBar"
   ELSE
       CREATE TABLE "FooBar"
END

robvolk
Most Valuable Yak

15732 Posts

Posted - 2010-06-19 : 14:09:03

I thought about that but didn't test it to see if it overlaps or skips it, Adventureworks has an even number of tables.

Kristen
Test

22859 Posts

Posted - 2010-06-19 : 16:04:49

[code]
DECLARE @Test TABLE
(
ID int NOT NULL
)

INSERT INTO @Test(ID)
VALUES(1),(2),(3),(4),(5),(6),(7),(8),(9),(10),(11),(12),(13),(14),(15),(16),(17),(18),(19),(20)

SELECT TOP 50 PERCENT ID
FROM @Test
ORDER BY ID

SELECT TOP 50 PERCENT ID
FROM @Test
ORDER BY ID DESC

INSERT INTO @Test(ID)
VALUES (21)

SELECT TOP 50 PERCENT ID
FROM @Test
ORDER BY ID

SELECT TOP 50 PERCENT ID
FROM @Test
ORDER BY ID DESC
[/code]
First record sets 1-10 and 11-20

Second recordsets 1-11 and 11-21

Sample size of only one! but that one did work such that it would fail-safe

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources