The Multifunctioning DBA

May 12 2010   5:00PM GMT

Modify sp_who in Sybase

Colin Smith Colin Smith Profile: Colin Smith

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

 Comment on this Post

 
There was an error processing your information. Please try again later.
Thanks. We'll let you know when a new response is added.
Send me notifications when other members comment.

Forgot Password

No problem! Submit your e-mail address below. We'll send you an e-mail containing your password.

Your password has been sent to:

Share this item with your network: