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.

REGISTER or login:

Forgot Password?
By submitting you agree to receive email from TechTarget and its partners. If you reside outside of the United States, you consent to having your personal data transferred to and processed in the United States. Privacy

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: