If this is your first visit, be sure to
check out the FAQ by clicking the
link above. You may have to register
before you can post: click the register link above to proceed. To start viewing messages,
select the forum that you want to visit from the selection below.

DECLARE @Database_Name NVARCHAR(512)
DECLARE Database_Cursor CURSOR FOR
SELECT name FROM sys.databases
WHERE name NOT IN ('master','model','msdb','tempdb')
AND state_desc LIKE 'online'
OPEN Database_Cursor
FETCH NEXT FROM Database_Cursor INTO @Database_Name
WHILE @@FETCH_STATUS = 0
BEGIN
EXECUTE('
USE ' + @Database_Name + '
PRINT ''Running on '' + DB_NAME() + ''...''
EXEC SP_MSFOREACHTABLE @command1= ''ALTER INDEX all ON ? SET ( ALLOW_PAGE_LOCKS = ON )''
PRINT ''OK!''
')
IF @@ERROR <> 0
BREAK
FETCH NEXT FROM Database_Cursor INTO @Database_Name
END
CLOSE Database_Cursor
DEALLOCATE Database_Cursor

DECLARE @Database_Name NVARCHAR(512)
DECLARE Database_Cursor CURSOR FOR
SELECT name FROM sys.databases
WHERE name NOT IN ('master','model','msdb','tempdb')
AND state_desc LIKE 'online'
OPEN Database_Cursor
FETCH NEXT FROM Database_Cursor INTO @Database_Name
WHILE @@FETCH_STATUS = 0
BEGIN
EXECUTE('
USE ' + @Database_Name + '
PRINT ''Running on '' + DB_NAME() + ''...''
EXEC SP_MSFOREACHTABLE @command1= ''ALTER INDEX all ON ? SET ( ALLOW_PAGE_LOCKS = ON )''
PRINT ''OK!''
')
IF @@ERROR <> 0
BREAK
FETCH NEXT FROM Database_Cursor INTO @Database_Name
END
CLOSE Database_Cursor
DEALLOCATE Database_Cursor

Hi Theo2f, I tried your code to get page locking to work but it didn't seem to do anything. Maybe I'm putting the code in the wrong place. Where should I put this exactly?