Find if table locking is disabled
I want to find if a table is locked on all tables in all databases. so I check this property at the index level from the sysindexes table or table level?
How can I check it?
Manjot relationship
+2
Manjot
source
to share
1 answer
As far as I know, table level locking cannot be disabled. Locking pages and rows can be disabled based on the index.
To check this, look in sys.indexes (if on SQL 2005 or higher) for the allow_row_locks and allow_page_lock 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
+1
GilaMonster
source
to share