Clever Geek Handbook

Is a table in use?

How to find out if a table is being used in SQL (in any database)? if someone is already using it, or "open" it, then it is being used.

+1


source to share


3 answers


This will actually give you a better result:



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


source


Generally speaking, the correct way to find out if someone else is using a table and won't let you do whatever you want is to try to do what you want and check if it doesn't work. If the error message says "non-exclusive access" or "table in use" or equivalent, you are wrong.

If your DBMS supports table locks, you can apply the lock to the table and then perform the sequence of operations - but you will interfere with other people who might otherwise use it.



Note that checking on the lock table (eg syslockinfo) is DBMS specific and unreliable - this leads to TOCTOU problems (check time, use time).

+4


source


Check for open locks in the table.

Take a look at the syslockinfo table.

+2


source






More articles:


All Articles