Skip to content

Instantly share code, notes, and snippets.

@lionofdezert
Last active December 14, 2015 05:49
Show Gist options
  • Save lionofdezert/5037845 to your computer and use it in GitHub Desktop.
Save lionofdezert/5037845 to your computer and use it in GitHub Desktop.
Script to fix ALLOW_PAGE_LOCKS option on all indexes of all databases on a particular instance
/***********************************
Script By: Amna Asif
Purpose : To fix ALLOW_PAGE_LOCKS option on
all indexes of all databases on a particular instance
***********************************/
USE MASTER
GO
SET QUOTED_IDENTIFIER ON
GO
SET ARITHABORT ON
GO
DECLARE @DBName NVARCHAR(255)
DECLARE @IndexCount int
DECLARE @UpdateIndexQuery Varchar(500)
--- Table variable to hold intermediate result set
DECLARE @IndexsInfo TABLE
(
RowNo int identity(1,1),
DatabaseName varchar(100),
SchemaName VARCHAR(100),
TableName varchar(100),
IndexName varchar(100)
)
--- Cursor to work on each changeable index of each db on an instance
DECLARE DatabaseList CURSOR
FOR
SELECT Name
FROM sys.databases
WHERE state_desc = 'ONLINE'
AND is_read_only = 0
ORDER BY name
OPEN DatabaseList
FETCH NEXT FROM DatabaseList INTO @DBName
WHILE @@FETCH_STATUS = 0
BEGIN
INSERT INTO @IndexsInfo (DatabaseName,SchemaName,TableName,IndexName)
EXEC( ' SELECT '''+@DBName+''' AS DatabaseName,SCHEMA_NAME(schema_id) AS SchemanName, TABL.NAME AS TableName,indx.NAME AS IndexName
FROM '+@DBName+'.SYS.INDEXES indx
LEFT OUTER JOIN '+@DBName+'.SYS.TABLES TABL ON indx.[OBJECT_ID]=TABL.[OBJECT_ID]
WHERE ALLOW_PAGE_LOCKS = 0 -- where page lock option is not selected
AND indx.name NOT LIKE ''QUEUE%'' ' -- we need only user defined indices
)
FETCH NEXT FROM DatabaseList INTO @DBName
END
CLOSE DatabaseList
DEALLOCATE DatabaseList
-----Update allow_page_locks option for those indexes where we need
SET @IndexCount=(SELECT MAX(RowNo) FROM @IndexsInfo )
WHILE @IndexCount >0
BEGIN
SET @UpdateIndexQuery=( SELECT ' ALTER INDEX '+ IndexsInfo.IndexName +' ON ['+
IndexsInfo.DatabaseName+'].['+SchemaName+'].['+IndexsInfo.TableName+']
SET (
ALLOW_PAGE_LOCKS = ON
) ; '
FROM @IndexsInfo AS IndexsInfo
WHERE IndexsInfo.RowNo=@IndexCount)
EXEC(@UpdateIndexQuery)
SET @IndexCount=@IndexCount-1
END
Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment