Code to write code (part 2)

This is completely done in SSMS.  This is a real snippet of code I created to adjust the fill factor on one of my systems.  In the Esri system, we have used some outdated standards for databases, and they like the Fill factor for indexes to be lower than 80%. The tool forces this on the system.  And when we update our map extracts from Esri to our public safety system, I adjusted it 100%.  The map tech does not communicate the changes well. I have written a script to check and see what indexes we created with the lower amount and make the updates.

My process here is the create a select the looks like the alter index command and return a list of altering commands I can copy and paste into the SSMS query window to make the required updates. Like the previous blog article, I mix the select data with hard-coded text into a valid command.

Step 1, Identify the rows that need to be updated

SELECT 4 AS ord,

i.name,

s.name,

o.name

FROM sys.indexes i

join sys.objects o ON i.object_id = o.object_id

join sys.schemas s ON o.schema_id = s.schema_id

WHERE fill_factor = 100

AND fill_factor <> 0

AND is_disabled = 0

AND is_hypothetical = 0

This has all the elements we need but none of the static text like ‘ALTER INDEX ‘ (notice the white space matters here)

Step 2, manually write the alter command using the data from the select

 ALTER INDEX IX_Reputation ON dbo.Users REBUILD WITH (FILLFACTOR = 100);

IX_Reputation is my i.name in the step 1 code.  s.name is the schema name. And o.name is the source table name.  When this is mixed together:

 'ALTER INDEX '+i.name+ ' ON '+s.name+'.'+o.name+ ' REBUILD WITH (FILLFACTOR = 100);’

Step 3, Write a new select the combined the steps 1 and 2 into one command.

 SELECT 4 AS ord,

'ALTER INDEX '+i.name+ ' ON '+s.name+'.'+o.name+' REBUILD WITH (FILLFACTOR = 100); '

FROM sys.indexes i

join sys.objects o ON i.object_id = o.object_id

join sys.schemas s ON o.schema_id = s.schema_id

WHERE fill_factor = 100

AND fill_factor <> 0

AND is_disabled = 0

AND is_hypothetical = 0

ORDER BY ord;

The results are:

ALTER INDEX IX_Reputation ON dbo.Users REBUILD WITH (FILLFACTOR = 100);

That looks good.  What is the ord field for?  We will use this below when we make it into a full script.  I need to add a little more automation into this to make it work better yet.

Step 4, adding a few UNION commands, creates a script that easily is dropped into another query window.

 USE StackOverflow2013;

GO

SELECT 1 AS ord, 'USE '+db_name()+';' AS code

UNION

SELECT 2 AS ord, 'GO'

UNION

SELECT 3 AS ord, ' '

UNION

SELECT 4 AS ord,

'ALTER INDEX '+i.name+ ' ON '+s.name+'.'+o.name+ ' REBUILD WITH (FILLFACTOR = 100); '

FROM sys.indexes i

join sys.objects o ON i.object_id = o.object_id

join sys.schemas s ON o.schema_id = s.schema_id

WHERE fill_factor = 100

AND fill_factor <> 0

AND is_disabled = 0

AND is_hypothetical = 0

ORDER BY ord;

GO

Select all the rows in the code column and paste them into a new query window, and they will run just fine.  But first, can we go another step forward and reduce the copy, paste, and new window? Yes, and this makes the process a single step.  Run the script.

In the next step, I created a bit of code to have each index process until all the indexes are complete and updated to 100%.  My first attempt was to run them as a batch.  I have 200 indexes on this database (2-5 per table), and running as a batch caused performance issues.  To fix this, I run one at a time. I used a cursor, but you could easily use a temp table also.  I am running this only once every 2-3 weeks, so I have left it as a cursor.

Step 6, Run each line independently and get the next command.

DECLARE @idx SYSNAME
DECLARE @SQL NVARCHAR(300)
DECLARE cur_FillFactor CURSOR FOR
SELECT
N'ALTER INDEX '+i.name+ N' ON '+s.name+N'.'+o.name+ N' REBUILD WITH (FILLFACTOR = 100); '
FROM sys.indexes i
join sys.objects o on i.object_id = o.object_id
join sys.schemas s on o.schema_id = s.schema_id
--join sys.databases d on i.object_id = d.
WHERE fill_factor < 80
AND fill_factor <> 0
AND is_disabled = 0
AND is_hypothetical = 0;

OPEN cur_FillFactor
FETCH NEXT FROM cur_FillFactor INTO @idx

WHILE @@FETCH_STATUS = 0
BEGIN
SELECT @SQL = @idx
EXEC sp_executesql @SQL
PRINT @SQL

FETCH NEXT FROM cur_FillFactor INTO @idx
END
CLOSE cur_FillFactor
DEALLOCATE cur_FillFactor
PRINT 'Complete'

A couple of notes.  First, I have debugged code left in place, with Print @SQL and Print @Complete.  This helps understand what it did.

The messages tab will show (if you have 200 in your database like mine, you would have a lot longer list):

Second, the cursor, not the best choice, but it will stay. Cursor stores the commands for us to use in the loop.

Third, the EXEC sp_executesql will run the stored command.

Notice that this code is not database independent.  This is resolved in the next blog article. But we take this a step further and create code that also runs for each database as needed.  After that, we need to create a framework to make this easily repeatable, Making your databases fast, secure and reliable! Are you ready to make your processed simple, fast, secure, reliable?