January 19, 2005 at 10:26 am
Hi,
On SQL7 I use a query to know the table is been using for any spid:
select convert( varchar(60) , rtrim( obj.name ) ) as 'name' , rtrim( prc.cmd ) as cmd
from master..sysprocesses prc (nolock)
, master..syslocks lo (nolock)
, sysobjects obj (nolock)
where prc.spid = lo.spid
and lo.id = obj.id
and lo.spid = @pSPID
Now, on SQL2000 this query doesn't work
Anybody can help me
Sorry for my poor english
January 19, 2005 at 3:19 pm
Probably needs some cleaning (built for Query analyzer) but here goes....
--create temptable
create table #spid(
spidid int,
dbid int,
objid int,
indid int,
type varchar(3),
resource varchar(1),
mode varchar(2),
status varchar(25))
insert into #spid
exec sp_lock
go
--alter table for more info
alter table #spid Add objname varchar(200) null
go
alter table #spid add dbname varchar(100) null
go
--update with info
update #spid set objname=name from #spid,sysobjects where objid=id
go
update #spid set dbname=name from #spid,sysdatabases where #spid.dbid=sysdatabases.dbid
go
--cleanup
drop table #spid
January 20, 2005 at 2:04 am
Thanks., but I changed the table syslocks to syslockinfo and it works...
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply
This website stores cookies on your computer.
These cookies are used to improve your website experience and provide more personalized services to you, both on this website and through other media.
To find out more about the cookies we use, see our Privacy Policy