CODEWITHSUNDEEP
sqlsql-servert-sqldynamic-sqladventureworks
jetbenny
jetbenny

Reputation: 1

Execute T-SQL Alter Statements created from SELECT statement?

End Goal: Change specific options of specific indexes in T-SQL.

Setup: SQL Server Express Install / SQL Server Management Studio 19 / Database = AdventureWorks2019

I have the following SELECT statement that queries for the indexes I want to change the row_locking/page_locking options of

select 'alter index '+ I.name +' on ' + s.name +'.'+ t.name + ' set (allow_row_locks = off, allow_page_locks = off);' 
FROM sys.dm_db_index_physical_stats (DB_ID(), NULL, NULL, NULL, NULL) AS DDIPS
INNER JOIN sys.tables T on T.object_id = DDIPS.object_id
INNER JOIN sys.schemas S on T.schema_id = S.schema_id
INNER JOIN sys.indexes I ON I.object_id = DDIPS.object_id
AND DDIPS.index_id = I.index_id
WHERE DDIPS.database_id = DB_ID()
and I.name is not null
AND DDIPS.avg_fragmentation_in_percent > 0;

This creates a series of results that I would want to execute.

Example row:

alter index PK_EmployeePayHistory_BusinessEntityID_RateChangeDate on HumanResources.EmployeePayHistory set (allow_row_locks = off, allow_page_locks = off);

As far as I know right now is that I would have to manually copy the result set from the select statement then execute it as its own query, I was looking for a way to automate this process into one or more scripts.

Is this possible to do?

Upvotes: -1

Views: 391

Answers (1)

jetbenny
jetbenny

Reputation: 1

Based on the comment from user "sticky bit" - I was able to make the script to include sp_executesql and iterate over the ALTER commands.

Below is how I got it to do the trick.

USE AdventureWorks2019;
GO

DECLARE @SQLString NVARCHAR(255);
DECLARE Index_Cursor CURSOR FOR
select 'alter index '+ I.name +' on ' + s.name +'.'+ t.name + ' set (allow_row_locks = off, allow_page_locks = off);' 
FROM sys.dm_db_index_physical_stats (DB_ID(), NULL, NULL, NULL, NULL) AS DDIPS
INNER JOIN sys.tables T on T.object_id = DDIPS.object_id
INNER JOIN sys.schemas S on T.schema_id = S.schema_id
INNER JOIN sys.indexes I ON I.object_id = DDIPS.object_id
AND DDIPS.index_id = I.index_id
WHERE DDIPS.database_id = DB_ID()
and I.name is not null
AND DDIPS.avg_fragmentation_in_percent > 0;

OPEN Index_Cursor;

FETCH NEXT FROM Index_Cursor INTO @SQLString;

WHILE @@FETCH_STATUS = 0
BEGIN
    EXECUTE sp_executesql @SQLString;

    FETCH NEXT FROM Index_Cursor INTO @SQLString;
END

CLOSE Index_Cursor;
DEALLOCATE Index_Cursor;
GO

Upvotes: 0

Related Questions