Change Index Of All Tables, In At The Databases On A Server.

Hi,
I am working on a script to do following:


get a list of indexes on all tables in all dbs on a SQL server.

If the index property to allow page locks is off, then turn it on, re-index and turn it off again.
My problem is:
i want to use ' Use <db>' statement in the middle of my script but it is not working.I tried using dynamic SQL with


set @cmd='use '+ @dbname
exec (@cmd)
But this is not working.
Can we use 'use' statement in the middle of a script? If not what is the alternative?

My script looks as follows:


DECLARE @Database VARCHAR(255)

DECLARE @Table VARCHAR(255)

declare @Index varchar(255)

DECLARE @cmd NVARCHAR(500)

DECLARE @fillfactor INT

SET @fillfactor = 90

DECLARE DatabaseCursor CURSOR FOR

SELECT name FROM master.dbo.sysdatabases

WHERE name NOT IN ('master','model','msdb','tempdb','distrbution')

ORDER BY 1

OPEN DatabaseCursor

FETCH NEXT FROM DatabaseCursor INTO @Database

WHILE @@FETCH_STATUS = 0

BEGIN

SET @cmd = 'DECLARE TableCursor CURSOR FOR SELECT table_catalog + ''.'' + table_schema + ''.'' + table_name as tableName

FROM ' + @Database + '.INFORMATION_SCHEMA.TABLES WHERE table_type = ''BASE TABLE'''

-- create table cursor

EXEC (@cmd)

OPEN TableCursor

FETCH NEXT FROM TableCursor INTO @Table

WHILE @@FETCH_STATUS = 0

BEGIN

set @cmd='use '+@Database

print (@cmd)

exec (@cmd)



declare IndexCursor CURSOR for select name from sys.indexes where object_id=object_id(@Table)

open IndexCursor

fetch next from IndexCursor into @Index



print (@table)

--select name from sys.indexes where object_id=object_id(@Table)

print (@index)

WHILE @@FETCH_STATUS = 0

begin

if (INDEXPROPERTY(OBJECT_ID(@Table),@Index,'IsPageLockDisallowed')=1)

begin

print (@Index + ' page locking off')

-- SET @cmd='ALTER INDEX '+@Index +' ON '+@Table+' SET (ALLOW_PAGE_LOCKS = ON) reorganize

-- ALTER INDEX '+@Index +' ON '+@Table+' SET (ALLOW_PAGE_LOCKS = OFF)'

end

else

begin

print (@Index + ' page locking on')

-- SET @cmd='ALTER INDEX '+@Index +' ON '+@Table+' reorganize'

end

--PRINT (@cmd)

fetch next from IndexCursor into @Index

end

CLOSE IndexCursor

DEALLOCATE IndexCursor

FETCH NEXT FROM TableCursor INTO @Table

END

CLOSE TableCursor

DEALLOCATE TableCursor

FETCH NEXT FROM DatabaseCursor INTO @Database

END

CLOSE DatabaseCursor

DEALLOCATE DatabaseCursor




Can anyone help me please?