请教查看cursor name的办法

Tiger.wang

其他应用进程使用cusor，我可以查到它的cursor Id，怎么通过Cursor Id查到Cursor Name呢？

chuxu

dbcc sdes(pid)
可以看到cursor name

Tiger.wang

原意是想用dbcc cursorinfo(0,cursorname,spid)来查看cursor的查询计划，已经解决，cursorname参数是名字和ID均可
谢谢版主的回复：）

Tiger.wang

顺便把我已经写好的监控sybase应用间检查堵塞的脚本发上来，看对sybase维护人员是否有帮助：）
use appdb
go


if exists(select 1 from appdb..sysobjects where name='up_sys_check' and type=""
  drop proc up_sys_check
go
if exists(select 1 from tempdb..sysobjects where name='blk1' and type="U"
  drop table tempdb..blk1
go
if exists(select 1 from tempdb..sysobjects where name='blk2' and type="U"
  drop table tempdb..blk2
go

/***************************************************/
/* 检索出引起blk的表名                             */
/***************************************************/
create  procedure up_sys_check
  @dbname char(30)=null
as
  declare  @n_block int
  declare  @dbid smallint
  declare  @spid int
  declare  @curid int

  set nocount on
  dbcc traceon(3604)

  if @dbname is not null
            select @dbid=db_id(@dbname)
  if exists(select 1 from tempdb..sysobjects where name='blk1' and type="U"
        drop table tempdb..blk1
  if exists(select 1 from tempdb..sysobjects where name='blk2' and type="U"
        drop table tempdb..blk2
       
  select "*******************start check block spid********************"
  select "Time:"+convert(varchar(20),getdate(),109)
  select l.spid as spid,substring(v.name,1,12) as locktype,
            substring(db_name(l.dbid)+".."+convert(char(20),object_name(l.id,l.dbid)),1,26) as table_name,  
        ipaddr,hostprocess,class,convert(char(12),l.page) as page,substring(p.cmd,1,16) as cmd
    into tempdb..blk1
    from master..syslocks l, master..sysprocesses p, master..spt_values v
    where p.spid=l.spid and l.type = v.number and v.type = "L" and p.dbid=isnull(@dbid,p.dbid)  
          and p.spid=isnull(@spid,p.spid) and l.dbid=isnull(@dbid,l.dbid) and l.spid=isnull(@spid,l.spid)
                and ((l.type>=257 and l.type<=267) or (l.type>=769 and l.type<=779)) and p.blocked=0
                order by spid,table_name
                
                
  select distinct spid into tempdb..blk2 from tempdb..blk1
  select @n_block = count(*) from tempdb..blk2
  
  while  @n_block >0
  begin  
    set rowcount 1
    select @spid=spid from tempdb..blk2
    delete tempdb..blk2
    select @n_block = @n_block-1
    set rowcount 0
    select "******check-table-lock-blocked:"+ str(@spid)+"count:"+str(count(1)) from master..sysprocesses where blocked=@spid
    select * from tempdb..blk1 where spid=@spid
   
    if exists(select 1 from tempdb..blk1 where spid=@spid and class like "%Cursor Id%"
    begin
            select        @curid=convert(int,substring(class,11,) from tempdb..blk1 where spid=@spid and class like "%Cursor Id%"
        dbcc cursorinfo(0,@curid,@spid)
    end
    else
    begin
            select "******check-spid-blocked execute plan :"+ str(@spid)
            exec sp_showplan @spid,null,null,null
   
    end
    select "******check-spid-blocked sqltext :"+ str(@spid)
    dbcc sqltext(@spid)
  end

  set rowcount 0
  drop table tempdb..blk1
  drop table tempdb..blk2
go


select 'end of the script.'
go