Current table of spid
-
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
-
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
-
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