December 28, 2014 at 12:58 pm
I have 10k indexes I need to rebuild and each time the script reaches an error it stops all further activity. How can I append 'GO' to the end of each line
so it will continue on error messages?
Once I have the syntax I can do a find and replace function in Notepad++
USE [AdventureWorks2014] + char(13) + char(10) + GO
ALTER INDEX [IX_Person] ON [Person].[Person] REBUILD PARTITION = ALL WITH (PAD_INDEX = OFF) + char(13) + char(10) + GO
ALTER INDEX [IX_Emp] ON [HumanResources].[Employee] REBUILD PARTITION = ALL WITH (PAD_INDEX = OFF) + char(13) + char(10) + GO
************** Truncate ***********
December 29, 2014 at 12:00 am
I used to do same in SQL server itself
December 29, 2014 at 7:56 am
You can do a find/replace on the USE, the ALTER and the CREATE statement. That will save you probably over 80% of the error messages. Add a GO + new-line before these commands (which basically is a GO after the previous command), so:
USE ... becomes GO + CHAR(10) + CHAR(13) + USE ...
CREATE ... becomes GO + CHAR(10) + CHAR(13) + CREATE ...
ALTER ... becomes GO + CHAR(10) + CHAR(13) + ALTER ...
January 1, 2015 at 1:12 pm
You can dynamically create the rebuild scrip with a "GO" statement in between alter index lines.
If you want to rebuild all the indexes in Adventureworks database you can do the following :
USE Adventureworks
GO
SET NOCOUNT ON
GO
SELECT 'ALTER INDEX [' + I.NAME + '] ON [' +T.NAME + ']' + ' REBUILD PARTITION = ALL WITH (PAD_INDEX = OFF)' +
CHAR(13) +CHAR(10) +'GO'
FROM SYS.TABLES T
JOIN SYS.INDEXES I
ON T.OBJECT_ID= I.OBJECT_ID
WHERE I.NAME IS NOT NULL
Once you run the above you will copy the result set to a new window. You will see the "GO" statement in a new line .
When I run it I prefer to get the result in "Results to TEXT" instead of " Results to GRID"
Note: you have to modify the script so that it will capture all the schema
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply