sp_lock with table names?
Steve Wechsler
Anyone have a stored procedure that works like sp_lock, but returns
the table name instead of the table id?
Steve
--
Steve.W...@mail.citicorp.com (do NOT send mail to slsdev address)
Free O.J. Simpson! Make murder a misdemeanor!
"Watch?? I'm gonna pray, Man! Know any good religions?" -- Zaphod Beeblebrox
Only a fool would think that the opinions above are representative of Citicorp
mdchachi
>Anyone have a stored procedure that works like sp_lock, but returns
>the table name instead of the table id?
Yes, I wrote one and posted it here some time ago. Write me and I'll
send it to you (I don't have it with me right now).
Mike
--
C ^ ^ D _ mdch...@vela.acs.oakland.edu mdch...@japan.ml.com
^0 0^ __
( v ) Woof.
U (or is it Moo?) BRAIN MODERNITY
luc van der veurst
: Anyone have a stored procedure that works like sp_lock, but returns
--
------------------------------------------------------
Luc Van der Veurst Mail : lu...@az.vub.ac.be
Academic Hospital, VUB Phone : 32 - 2 477 69 80
Laarbeeklaan 101 Fax : 32 - 2 477 69 75
1090 Brussels, Belgium
------------------------------------------------------
luc van der veurst
: Anyone have a stored procedure that works like sp_lock, but returns
: the table name instead of the table id?
Steve,
Since the relation between object_id and object_name are stored in
different databases, I don't think it's possible to write a stored
procedure that shows tablenames instead of ids just using system
tables. It will become possible with 10.x.x cursors and when it will
be possible to use a variable in a from clause (ex. from @dbname..sys-
objects) (this feature is an enhancement request).
I have done the following :
1. created a table objects (in wast_db : 'Working And System Tables'-db) :
create table objects (dbid smallint, id int, name varchar(32), type char(2))
create unique clustered index objectsI1 on objects (dbid, id)
2. created a unix script to fill this table with the id-name relationship
for all databases in the server (1 parameter: server-name )
(.syblogin is a file where each row contains a <username>\t<password>) :
---- Begin Script ------------------------------------
#!/bin/ksh
Server=$1
pw=`cat $HOME/.syblogin | grep sa | awk '{print $2}'`
dblist=`(print "$pw";
print "select 'DB', name from sysdatabases"
print "go") | isql -Usa -S$Server | awk '/DB/ {print $2}'`
(print "$pw";
print "truncate table wast_db..objects";
print "go";
for db in $dblist
do
print "insert wast_db..objects";
print "select db_id('$db'), id, name, type";
print "from $db..sysobjects";
print "go";
done
) | isql -Usa -S$Server -en
---- End Script ------------------------------------
3. created a system procedure sp_l which is a modification of sp_lock
(join between syslocks and objects) :
---- Begin Procedure -------------------------------
create procedure sp_l
@spid1 int = NULL, /* server process id to check for locks */
@spid2 int = NULL /* other process id to check for locks */
as
declare @length int
/*
** Show the locks for both parameters.
*/
if @spid1 is not NULL
begin
select l.spid, locktype = convert(char(18),v.name),
table_name = convert(varchar(20),o.name), l.page,
dbname = convert(varchar(15), db_name(l.dbid))
from syslocks l, spt_values v, wast_db..objects o
where l.type *= v.number
and v.type = "L"
and l.spid in (@spid1, @spid2)
and l.dbid *= o.dbid
and l.id *= o.id
end
/*
** No parameters, so show all the locks.
*/
else
begin
select l.spid, locktype = convert(char(18), v.name),
table_name = convert(varchar(20),o.name), l.page,
dbname = convert(varchar(15), db_name(l.dbid))
from syslocks l, spt_values v, wast_db..objects o
where l.type *= v.number
and v.type = "L"
and spid != @@spid
and l.dbid *= o.dbid
and l.id *= o.id
order by spid, dbname, table_name, locktype, page
end
return (0)
---- End Procedure ---------------------------------
The problem with sp_l is that when objects are created or modified
(drop/create) the script must be run, otherwise sp_l shows NULL in
the table_name column. But I can live with this.
Luc
mdchachi
>Steve Wechsler (st...@slsdev.wwss.us-ny.citicorp.com) wrote:
>: Anyone have a stored procedure that works like sp_lock, but returns
>: the table name instead of the table id?
>Steve,
>Since the relation between object_id and object_name are stored in
>different databases, I don't think it's possible to write a stored
>procedure that shows tablenames instead of ids just using system
>tables. It will become possible with 10.x.x cursors and when it will
>be possible to use a variable in a from clause (ex. from @dbname..sys-
>objects) (this feature is an enhancement request).
This is true but it's been my experience that typically, most projects
only use one database or, if not, when locking occurs it's usally one in
particular that I'm concerned about. A procedure I wrote looks up the
table name for the current DB and just leaves the object id if locks
occur in other DB's. If I ever remember to bring it in, I'll post it.
Mischa Sandberg
- create an sp_ that updates a table in some common database (in master
if you're feeling lucky), containing (dbid,id,name) combinations.
The procedure hard-codes a list of databases to scan in this way.
- execute that procedure, if your databases are pretty volatile, or if
you're
concerned with tempdb locks ...; then execute something like
select name = isnull(OBJ.name, convert(varchar,LOX.id)), LOX.*
from master..syslocks LOX, master..AllObjects OBJ
where LOX.dbid *= OBJ.dbid and LOX.id *= OBJ.id
Not a perfect solution, but at least you can *usually* get all the object
names, and will at least get some output (albeit numeric id's) where there
is something quite volatile going on. And your "hardcoding" amounts to
the list of databases in one procedure.
Just my .02
--
Mischa Sandberg ... Mischa_...@mindlink.bc.ca
or uunet!van-bc!rsoft!mindlink!Mischa_Sandberg
*-*-*-*-*-*-*-*-*-*-*
Engineers think equations are an approximation of reality.
Physicists think reality is an approximation of the equations.
Mathematicians never make the connection.
Kiran G. Jain
: >Anyone have a stored procedure that works like sp_lock, but returns
: >the table name instead of the table id?
use master
go
if exists (
select * from sysobjects
where name = 'sp_locks'
and sysstat & 7 = 4
)
drop procedure sp_locks
go
create procedure sp_locks
@spid1 int = NULL, /* server process id to check for locks */
@spid2 int = NULL /* other process id to check for locks */
as
/*
* sp_locks
* Kiran Jain @ The Franklin Mortgage Capital Corp.
* ja...@iia.org or ja...@cs.msstate.edu
* Inspiration : Dan Graifer
*
* This code may be used freely without charge.
* This notice must remain attached to the code below.
*
* The Franklin Group, and its affiliates, make no claims as to the efficacy of
* this code, and provide no warranty. Use of this code is at your own
* risk, as neither me ( Kiran Jain ) nor The Franklin Group accepts no
* liability for its use, or responsibility for its maintenance.
*
* sp_locks contain much more information than the standard sp_lock
* provided by Sybase. The only thing which you can do to make it better
* is to write a shell script on top of it taking the "host process id" from
* this output and grep the user name from your system using ps. So that you
* can effectively see that which UNIX/system user is holding the lock
* instead of the regular sybase user.
*
* Usage : sp_locks
*
*/
/*
** Show the locks for both parameters.
*/
if @spid1 is not NULL
begin
select
SPID=syslocks.spid, Process_ID=sysprocesses.hostprocess,
Lock_Type=spt_values.name, Program=program_name,
DBName=substring(db_name(syslocks.dbid), 1, 15),
Table_Name=sysobjects.name, Sybase_User=sysusers.name,
Cmd=cmd, Page=syslocks.page, Status=status
from
syslocks, spt_values, sysprocesses, sysusers, sysobjects
where
syslocks.type = spt_values.number
and spt_values.type = 'L'
and syslocks.spid = sysprocesses.spid
and sysusers.suid = sysprocesses.suid
and syslocks.id = sysobjects.id
and syslocks.spid in (@spid1, @spid2)
end
/*
** No parameters, so show all the locks.
*/
else
begin
select
SPID=syslocks.spid, Process_ID=sysprocesses.hostprocess,
Lock_Type=spt_values.name, Program=program_name,
DBName=substring(db_name(syslocks.dbid), 1, 15),
Table_Name=sysobjects.name, Sybase_User=sysusers.name,
Cmd=cmd, Page=syslocks.page, Status=status
from
syslocks, spt_values, sysprocesses, sysusers, sysobjects
where
syslocks.type = spt_values.number
and spt_values.type = 'L'
and syslocks.spid = sysprocesses.spid
and sysusers.suid = sysprocesses.suid
and syslocks.id = sysobjects.id
order by
SPID, DBName, Table_Name, Lock_Type, Page
end
return (0)
go
-----------------------------------------------------------------------------
Kiran Jain Fannie Mae
Software Consultant (W) : (202) 752-1439
e-mail : ja...@iia.org OR g9u...@fnma.com (R) : (703) 556-9298
Mischa Sandberg
(Kiran G. Jain) writes:
>
> ...
> select
> SPID=syslocks.spid, Process_ID=sysprocesses.hostprocess,
> Lock_Type=spt_values.name, Program=program_name,
> DBName=substring(db_name(syslocks.dbid), 1, 15),
> Table_Name=sysobjects.name, Sybase_User=sysusers.name,
> Cmd=cmd, Page=syslocks.page, Status=status
>
> from
> syslocks, spt_values, sysprocesses, sysusers, sysobjects
> where
> syslocks.type = spt_values.number
> and spt_values.type = 'L'
> and syslocks.spid = sysprocesses.spid
> and sysusers.suid = sysprocesses.suid
> and syslocks.id = sysobjects.id
Um. Well. This kind of restricts the display of locks to objects in
the master DB! The only exception to that would be, by some fluke, the ID
of some locked object matched some ID in master --- and so, you'd get a
nice piece
of misinformation about the locked object's name.
Or do you create all your application tables in master? Novel idea --- how
well does this work for you?
Perhaps you'd like to consider a follow-up posting of some kind.
Frankly, something that works *worse* than sp_locks doesn't, ah, make
your consulting outfit look too lustrous. Mind you, I've had one memorable
conversation with Sybase Tech Support where it had to be explained (to
them) that the sysobjects table is in every database, not just master :-)
I wish to emphasize that was an extremely isolated (hilarious) exception.
Kumar C K
This probably the closest you can get to a solution using stored procedures.
Because it will not work when there are locks for more than one database, and this
stored procedure will be context sensitive to the database the user is currently
using, in reporting data from sysobjects & sysusers.
The best way to write a sp_lock will be a shell script, which executes the sql statements in the different databases which are reported by the outout of the initial
sp_lock statement. But they tend to be very slow.
CK
Bruce Gary
>From: s5u...@fnma.COM (Kumar C K)
>Subject: Re: sp_lock with table names?
>Date: Mon, 1 Aug 1994 18:25:00 GMT
>This probably the closest you can get to a solution using stored procedures.
>Because it will not work when there are locks for more than one database, and this
>stored procedure will be context sensitive to the database the user is currently
>using, in reporting data from sysobjects & sysusers.
I have a simple query which reports all the locks currently held,
as well as the object and page being locked. If I understand the
(original) question, this may help:
create procedure lockcheck as
select d.spid,
"username"=a.name,
"lock type"=f.name,
"page"=b.page,
"table name"=c.name,
"database"=e.name
from master..syslogins a,
master..syslocks b,
sysobjects c,
master..sysprocesses d,
master..sysdatabases e,
master..spt_values f
where a.suid = d.suid
and d.spid = b.spid
and b.id = c.id
and b.dbid = e.dbid
and f.number = b.type
and f.type = "L"
order by d.spid
go
Bruce Gary
bg...@fhcrc.org
mdchachi
------------------ cut here and run ----------------------------------
use master
go
if exists (
select * from sysobjects
where name = 'sp_block'
and sysstat & 7 = 4
)
drop procedure sp_block
go
create procedure sp_block @dbname varchar(30) = "%"
as
/*
* Name: sp_block
* Date: Jan 10 1994
* Author: Mike Chachich (mdch...@vela.acs.oakland.edu)
*
* There is no warranty expressed or implied and the use of this code
* is at your own risk. Specifically, claims that this code causes SQL
* Server to generate obscene messages will be vehemently denied.
*
* This procedure is very similar to sp_lock except that it provides more
* information and prints out the name of locked tables that are within the
* current database. It also lists any blocked processes and who they are
* blocked by. The use of multiple 'select' and 'print' statements was
* avoided in order to use this in the current version of PowerBuilder's DBA
* Painter (in PB, print is treated like raiserror and each select is treated
* as a separate result set)
*
* Note: this procedure, as is, is not compatible with
* versions of SQL Server older than 4.2
*
* Usage : sp_block [database-name]
*
*/
SELECT
/* display all locks */
syslocks.spid,
user=substring(sysusers.name,1,7),
"table / blcked_by"=
convert(varchar(17),isnull(object_name(id),convert(varchar(30),id))),
lock_type=convert(varchar(14),spt_values.name),
page=convert(varchar(10),syslocks.page),
dbname=substring(db_name(syslocks.dbid), 1, 10),
status=substring(sysprocesses.status, 1, 8),
program=substring(program_name,1,12),
command=substring(cmd,1,10)
FROM master.dbo.syslocks syslocks,
master.dbo.spt_values spt_values,
master.dbo.sysprocesses sysprocesses,
master.dbo.syslogins sysusers
WHERE syslocks.type = spt_values.number
and spt_values.type = 'L'
and syslocks.spid = sysprocesses.spid
and sysusers.suid = sysprocesses.suid
and db_name(syslocks.dbid) like @dbname
UNION
SELECT
/* display all blocked processes */
sp1.spid,
user=substring(suser_name(sp1.suid),1,7),
blk_user=
convert(char(3),sp1.blocked) +
substring(suser_name(sp2.suid),1,14),
locktype=null,
page=null,
dbname=substring(db_name(sp1.dbid),1,10),
status=substring(sp1.status, 1, 8),
program=substring(sp1.program_name,1,12),
command=substring(sp1.cmd,1,10)
FROM master.dbo.sysprocesses sp1, master.dbo.sysprocesses sp2
WHERE sp1.blocked = sp2.spid
go
grant execute on sp_block to public
go
Larry J Lennhoff
I use the following select statement to combine much of the information in
sp_lock and sp_who. It returns the spid holding the lock, the name of the user
owning the spid, the name of the database the lock is held in, the name of the
table the lock is held in, the type of the lock, the spid (if any) blocking
the lock, the name of the user owning the spid blocking the lock (that lived
in the house that jack built).
It only works in system 10 (it uses the additional parameter to
object_name that was added then). Finally, I'd like to
thank the poster who started off with something like this but
simpler and inspired me to extend their work
Larry
select l.spid, user_name = n.name,
dbname = convert(char(15),
db_name(l.dbid)),
tablename = object_name(id,l.dbid),
locktype = v.name,
blocked_by_spid = convert(char(5),blocked),
blocked_by_name = ISNULL((SELECT name from master..syslogins n2
WHERE n2.suid =
(SELECT suid from master..sysprocesses p2
where p2.spid = blocked)),"Nobody")
from master..syslocks l, master..spt_values v , master..syslogins n,
master..sysprocesses p
where l.type = v.number and v.type = 'L'
and l.spid = p.spid
and p.suid = n.suid
order by spid, dbname, tablename
--
Something it was gave Him the victory that day
To us remained the glory of a dauntless daring
And even if my troop fell thence vanquished
Yet to have attempted a valiant enterprise is still a trophy
Rajan Bhardvaj
> >This probably the closest you can get to a solution using stored procedures.
> >Because it will not work when there are locks for more than one
database, and this
> >stored procedure will be context sensitive to the database the user is
currently
> >using, in reporting data from sysobjects & sysusers.
You can modify the Sybase supplied stored proc by chnaging the table id
column to be
object_name(id,dbid)
That will list table names across all databases.
--
Rajan Bhardvaj (rbha...@worldbank.org, (202)458-4748)
DISCLAIMER :
MY VIEWS DO NOT REPRESENT THE VIEWS OF ANY ORGANIZATION,IMPLIED OR OTHERWISE.
mdchachi
>In article <BGARY.165...@fhcrc.org>, BG...@fhcrc.org (Bruce Gary) wrote:
>> >This probably the closest you can get to a solution using stored procedures.
>> >Because it will not work when there are locks for more than one
>database, and this
>> >stored procedure will be context sensitive to the database the user is
>currently
>> >using, in reporting data from sysobjects & sysusers.
>You can modify the Sybase supplied stored proc by chnaging the table id
>column to be
> object_name(id,dbid)
>That will list table names across all databases.
This is not true, not in the version I'm running anyway (4.9.2).
object_name() only takes one parameter.
mc
Rajan Bhardvaj
mdch...@pluto.acs.oakland.edu (mdchachi) wrote:
> rbha...@worldbank.org (Rajan Bhardvaj) writes:
>
> >You can modify the Sybase supplied stored proc by chnaging the table id
> >column to be
> > object_name(id,dbid)
>
> >That will list table names across all databases.
>
> This is not true, not in the version I'm running anyway (4.9.2).
> object_name() only takes one parameter.
>
The System 10 version does take parameters as per my earlier post.