find if table locking is disabled

Question

Hi,

I want to find if table locking is disabled across all tables in all databases. so I check this property on the index level from sysindexes table or table level?

How can I check it?

Regards Manjot

Answer 1

As far as I know, table-level locking cannot be disabled. Page and row locking can be disabled on a per-index basis.

To check that, look in sys.indexes (if on SQL 2005 or above) for the allow_row_locks and allow_page_locks columns. If on SQL 2000, use the INDEXPROPERTY function to check the IsPageLockDisallowed and IsRowLockDisallowed properties.

SQL 2005:

SELECT object_name(object_id), name, index_id, allow_row_locks, allow_page_locks FROM sys.indexes

SQL 2000

SELECT object_name(id), name, indid, INDEXPROPERTY(id, indid, 'IsPageLockDisallowed') AS IsPageLockDisallowed, INDEXPROPERTY(id, indid, 'IsRowLockDisallowed ') AS IsRowLockDisallowed 
FROM sysindexes