Page level locking

[bhosalenarayan]

Hello All,

My problem is that I want to allow page level locking for all the databases.


For one database, I am using below query:


sp_msforeachtable 'ALTER INDEX all ON ?
SET (
ALLOW_PAGE_LOCKS = ON
)
'

this enables page level locking for all indexes on all tables for one particular database.

How do I do it for all databases in one go.

I tried using sp_msforeachdb but realised I can't do it. I tried a couple of other things. but nothing worked.

Need help here!!!

Thanks in advance

[rmiao]

You try with cursor to get db name and run your code under each db.

[bhosalenarayan]

I already tried that and below is what I did :



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

Use @Database_Name
go;
sp_msforeachtable 'ALTER INDEX all ON ?
SET (
ALLOW_PAGE_LOCKS = ON
)
'

if @@error <> 0
break

fetch next from Database_Cursor
into @Database_Name
end
close Database_Cursor
deallocate Database_Cursor


and this gives an error:

Msg 102, Level 15, State 1, Line 17
Incorrect syntax near '@Database_Name'.



where am I wrong?? I am sorry but I am novice when it comes to all this..

[rmiao]

Can't use parameter in USE, need do it in dymanic sql.

[SQL Swerver]

@rmiao - you seem to have contradicted yourself:


@bhosalenarayan, all you need to do is build a wrapper using sp_msforeachdb around your sp_msforeachtable script.

You didn't mention the original script that fails, only your cursor script which was incorrectly prescribed.

[theo2f]

Just for the records...

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

[Middles]

Just for the records...

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?

Thanks.