Index Locking Strategy

/*

This script returns the index locking strategy information
for all user tables in a database. It also generates the required TSQL statement to alter locking strategies if required. These statements require CTRL-C and CTRL-V individually into query analyser for execution. Results are best viewed in text only. This TSQL can be wrapped in a stored procedure or ran as stand alone TSQL in query analyser.

David Wootton 04/07/2002.

*/
declare @msg   varchar(1000)
declare @tname varchar(150)
declare @idxname varchar(150)


declare indexoption cursor for

select t.name,i.name
from sysobjects t, sysindexes i
where t.id = i.id
and t.type = 'U'
and t.name not like 'dtproper%' 
and i.name <> t.name
group by t.name,i.name
order by t.name

open indexoption

fetch next from  indexoption into @tname,@idxname



WHILE (@@FETCH_STATUS <> -1)

BEGIN

    IF (@@FETCH_STATUS <> -2)

    BEGIN  

print 'Reporting Index Options On The Table '''+@tname+ ''' For The Index ''' +@idxname+ ''''
select @msg = ('sp_indexoption @IndexNamePattern = '''+@tname+'.'+@idxname+'''')  
       print ' '
exec (@msg) 
print 'allowPageLocks  = 0 (or false) then page locks are NOT used. Access to the specified index is obtained using row and table level locks.'
print 'allowRowLocks   = 0 (or false) then row locks are NOT used. Access to the specified index is obtained using page and table level locks.'
print ' '
print 'Required Syntax ... '
print ' '
print 'Exec sp_indexoption '''+@tname+'.'+@idxname+''',''allowPageLocks'',TRUE'
print ' '
print 'Exec sp_indexoption '''+@tname+'.'+@idxname+''',''allowRowLocks'',TRUE'
print ' '
    END

    FETCH NEXT FROM  indexoption INTO @tname,@idxname

END

CLOSE  indexoption
DEALLOCATE  indexoption
GO