January 25, 2016 at 6:22 am
Dear all,
I'm trying to turn ON all indexes to ALLOW_PAGE_LOCKS = ON by running the following quesry so there would not be any issues when running indexing jobs but I get an error message "Cannot find the object "[database name].dbo.[serverallocation" because it does not exist or you do not have permissions.
USE
MASTER
GO
SET
QUOTED_IDENTIFIER ON
GO
SET
ARITHABORT ON
GO
DECLARE
@DBName VARCHAR(255)
DECLARE
@IndexCount int
DECLARE
@UpdateIndexQuery Varchar(500)
DECLARE @IndexsInfo TABLE
(
RowNo
int identity(1,1),
DatabaseName
varchar(200),
TableName
varchar(200),
IndexName
varchar(200)
)
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,TableName,IndexName)
EXEC( ' SELECT '''+@DBName+''' AS DatabaseName,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
AND indx.name NOT LIKE ''QUEUE%'' '
)
FETCH NEXT FROM DatabaseList INTO @DBName
END
CLOSE DatabaseList
DEALLOCATE DatabaseList
SET @IndexCount=(SELECT MAX(RowNo) FROM @IndexsInfo )
WHILE @IndexCount >0
BEGIN
SET @UpdateIndexQuery=( SELECT 'ALTER INDEX '+ IndexsInfo.IndexName +' ON ['+
IndexsInfo
.DatabaseName+'].dbo.['+IndexsInfo.TableName+']
SET (
ALLOW_PAGE_LOCKS = ON
) ; '
FROM @IndexsInfo AS IndexsInfo
WHERE IndexsInfo.RowNo=@IndexCount)
EXEC(@UpdateIndexQuery)
SET @IndexCount=@IndexCount-1
END
Thank you on advance!
January 25, 2016 at 6:43 am
Add a filter for OBJECTPROPERTYEX(object_id, 'IsUserObject') = 1, or change the join to sys.tables to an inner join.
As it is, you're getting all the system tables as well as the user tables, and they can't be altered.
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
Viewing 2 posts - 1 through 1 (of 1 total)
You must be logged in to reply to this topic. Login to reply