Is the Table in Use?

Question

How to figure out if a table is in use in SQL (on any type database)? if somebody is already using it, or have it "open" then its in use.

Answer 1

Check for open locks on the table.

Have a look on the syslockinfo table.

Answer 2

Generally speaking, the correct way to find out whether someone else is using the table and will prevent you from doing whatever you want is to try doing what you want and to check whether it fails. If the failure message indicates 'non-exclusive access' or 'table in use' or equivalent, then you guessed wrong.

If your DBMS supports table locking, you can apply a lock to the table and then do a sequence of operations - but you'll impede other people who might otherwise be trying to use it.

Note that checking in a lock table (such as syslockinfo) is both DBMS-specific and unreliable - it leads to TOCTOU (time of check, time of use) problems.

Answer 3

Actually this will give you a better result:

select spid
    from master..sysprocesses
    where dbid = db_id('Works') and spid <> @@spid