Index Rebuilds: SQL vs. PowerShell

I thought this conversation worth reporting…Sean and I were comparing our scripts to create rebuild / reorgs, and he sent this:

DECLARE @currSchema VARCHAR(100) ,
@currTableName VARCHAR(100) ,
@SQL NVARCHAR(200)

DECLARE Tables CURSOR read_only
FOR
SELECT SS.name ,
   SO.name
   FROM sys.objects SO
   INNER JOIN sys.schemas SS ON SO.schema_id = SS.schema_id
   WHERE type = 'U'
   ORDER BY SO.name

–You could easily use the Schema_Name() function, but I already had it written this way.

OPEN Tables
FETCH NEXT FROM Tables INTO @currSchema, @currTableName
WHILE ( @@fetch_status <> –1 )
BEGIN

   SET @SQL = 'ALTER INDEX ALL ON ' + @currSchema + '.' + @currTableName
   + ' REBUILD;'
   –EXEC (@SQL)
   PRINT @SQL
   FETCH NEXT FROM Tables INTO @currSchema, @currTableName
END

CLOSE Tables
DEALLOCATE Tables

I replied with mine (I have some customizations I like, but that's another blog post), and then he wrote, "Of course, here's the PowerShell version of my script, very lightweight and quick… However, makes you put it in a txt file instead of working with sql in the sql editor… could be useful if you wanna ship it to another box in order to make your ssis pkg… Run this from the tables dir of your DB and you're good…"

dir | %{$Schema = $_.Schema; $Name = $_.Name; "ALTER INDEX ALL ON [$Schema]`.[$Name] REBUILD;" | out-file c:\Reindex.txt}

The difference in the amount of code it takes is just amazing.

Speaking of how awesome PowerShell is, here's the video of Sean's Ground-Zero PowerShell webcast from earlier this month. Enjoy!

-Jen McCown, http://www.MidnightDBA.com

1 thought on “Index Rebuilds: SQL vs. PowerShell”