Check table in use

Question

Post reply

Check table in use

shankartup

SSC Journeyman

Points: 82
More actions
July 22, 2009 at 11:48 pm

#132118

Hi All,
How do i check whether the table is in use. i need to check if the table is in use, i need to restrict the other user not allowing to update/delete.
is there a way to which returns a value so that based upon the value i can disable update in the front end
Thanks
Shankar.N

Viewing 5 posts - 1 through 5 (of 5 total)

You must be logged in to reply to this topic. Login to reply

Jeff Moden SSC Guru Points: 1004638 More actions · Answer 1

SQL Server exercises it's own locking. Why do you want to override it?

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

Victor Shahar Mr or Mrs. 500 Points: 533 More actions · Answer 2

Use this:

SELECT top 1 *

FROM dbo.sysobjects o, master.dbo.syslockinfo l

WHERE l.rsc_objid = o.id

AND name = ''

an locked object will be in syslockinfo table, you can select data to check this before you going to have some heavy operations.

Enjoy !!

Jeff Moden SSC Guru Points: 1004638 More actions · Answer 3

If you just want to guarantee exclusive use for a heavy operation, you don't need to do that. Just use the WITH(TABLOCKX) hint and when it's able, it'll lock the table for exclusive use.

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

Steve Jones - SSC Editor SSC Guru Points: 738632 More actions · Answer 4

You don't need to check if a table is in use. As Jeff mentioned, SQL Server handles this.

If you do want to manually lock a table/row for some reason, you would use a separate column or table and mark a value that your application will check.

If you think you do need to check, I'd suggest you give us reasons why so we can help you determine the best way to do this.