Modify sp_who in Sybase

After the Sybase upgrade, my boss needed to know how many users we have connecting to a specific database using a specific application. In order to accomplish this I extracted sp_who and modified it to gather the information that I needed. I also did not overwrite the original sp_who but I had it create it as a new proc. Make sure that you do not overwrite the original sp_who as you may need it in the future. I went to the application team and got a list of logins that they use for the application to connect to the database, this way I could filter off of these to get accurate counts. So here is what I did.

USE

sybsystemprocsgo

IF

OBJECT_ID(‘dbo.sp_circ’) IS NOT

NULLBEGIN

DROP PROCEDURE dbo.sp_app 

IF OBJECT_ID(‘dbo.sp_app’) IS NOT

NULL

PRINT

‘<<< FAILED DROPPING PROCEDURE dbo.sp_app >>>’

ELSE

PRINT

‘<<< DROPPED PROCEDURE dbo.sp_app >>>’

END

go

/* Sccsid = “%Z% generic/sproc/%M% %I% %G%” */

/* 4.8 1.1 06/14/90 sproc/src/serveroption */

/*

** Messages for “sp_circ” 17nnn

**

** 17231, “No login with the specified name exists.”

*/

create

procedure sp_app @loginame varchar(255) = NULL

as

declare @low intdeclare

@high intdeclare

@spidlow intdeclare

@spidhigh intdeclare

@len1 int, @len2 int, @len3 int

if

@@trancount = 0

begin

set chained

offend

set

transaction isolation level

1

select

@low = @@minsuid, @high = @@maxsuid,@spidlow = @@minspid, @spidhigh = @@maxspid

if

@loginame is not

NULLbegin

select @low = suser_id(@loginame), @high = suser_id

(@loginame) 

if @low is NULL

begin

if @loginame like “[0-9]%”

begin

select @spidlow = convert(int, @loginame),@spidhigh = convert(int, loginame),@low = @@minsuid, @high = @@maxsuid 

end

else

begin

/*** 17231, “No login with the specified name exists.”

*/

raiserror

17231 

return

(1) 

end

endend

begin

select distinct loginame=suser_name(suid),dbname=db_name

(dbid) 

into #whoappresult 

from master..sysprocesses 

where suid >= @low

and suid <= @high 

and spid >= @spidlow

and spid <= @spidhigh 

and suser_name(suid) like

‘appaccount1%’

or suser_name(suid) like

‘appaccount2%’

exec sp_autoformat @fulltabname = #whocircresult,@selectlist =

“loginame, dbname”,@orderby =

“order by loginame”

drop table #whoappresult  

endreturn

(0)go

EXEC

sp_procxmode ‘dbo.sp_app’,‘anymode’

go

IF

OBJECT_ID(‘dbo.sp_app’) IS NOT

NULL

PRINT

‘<<< CREATED PROCEDURE dbo.sp_app >>>’

ELSE

PRINT

‘<<< FAILED CREATING PROCEDURE dbo.sp_app >>>’

go

GRANT

EXECUTE ON dbo.sp_app TO

public

go