Clever Geek Handbook

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


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


source






More articles:


All Articles