June 15, 2012

sp_who2 (Transact-SQL MetaData) Definition

Please note: that the following source code is provided and copyrighted by Microsoft and is for educational purpose only.
The meta data is from an SQL 2012 Server.

I have posted alot more, find the whole list here.

Goto Definition or MetaData

Definition:

sys.sp_who2(nvarchar @loginame)

MetaData:

 create procedure sys.sp_who2  -- - 1995/11/03 10:16  
@loginame sysname = NULL
as

set nocount on

declare
@retcode int

declare
@sidlow varbinary(85)
,@sidhigh varbinary(85)
,@sid1 varbinary(85)
,@spidlow int
,@spidhigh int

declare
@charMaxLenLoginName varchar(6)
,@charMaxLenDBName varchar(6)
,@charMaxLenCPUTime varchar(10)
,@charMaxLenDiskIO varchar(10)
,@charMaxLenHostName varchar(10)
,@charMaxLenProgramName varchar(10)
,@charMaxLenLastBatch varchar(10)
,@charMaxLenCommand varchar(10)

declare
@charsidlow varchar(85)
,@charsidhigh varchar(85)
,@charspidlow varchar(11)
,@charspidhigh varchar(11)

-- defaults

select @retcode = 0 -- 0=good ,1=bad.
select @sidlow = convert(varbinary(85), (replicate(char(0), 85)))
select @sidhigh = convert(varbinary(85), (replicate(char(1), 85)))

select
@spidlow = 0
,@spidhigh = 32767

-- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- --
IF (@loginame IS NULL) -- Simple default to all LoginNames.
GOTO LABEL_17PARM1EDITED

-- select @sid1 = suser_sid(@loginame)
select @sid1 = null
if exists(select * from sys.syslogins where loginname = @loginame)
select @sid1 = sid from sys.syslogins where loginname = @loginame

IF (@sid1 IS NOT NULL) -- Parm is a recognized login name.
begin
select @sidlow = suser_sid(@loginame)
,@sidhigh = suser_sid(@loginame)
GOTO LABEL_17PARM1EDITED
end

-- -- -- --

IF (lower(@loginame collate Latin1_General_CI_AS) IN ('active')) -- Special action, not sleeping.
begin
select @loginame = lower(@loginame collate Latin1_General_CI_AS)
GOTO LABEL_17PARM1EDITED
end

-- -- -- --

IF (patindex ('%[^0-9]%' , isnull(@loginame,'z')) = 0) -- Is a number.
begin
select
@spidlow = convert(int, @loginame)
,@spidhigh = convert(int, @loginame)
GOTO LABEL_17PARM1EDITED
end

-- -- -- --

raiserror(15007,-1,-1,@loginame)
select @retcode = 1
GOTO LABEL_86RETURN


LABEL_17PARM1EDITED:


-- -- -- -- -- -- -- -- -- -- Capture consistent sysprocesses. -- -- -- -- -- -- -- -- -- -

select

spid
,status
,sid
,hostname
,program_name
,cmd
,cpu
,physical_io
,blocked
,dbid
,convert(sysname, rtrim(loginame))
as loginname
,spid as 'spid_sort'

, substring( convert(varchar,last_batch,111) ,6 ,5 ) + ' '
+ substring( convert(varchar,last_batch,113) ,13 ,8 )
as 'last_batch_char'
,request_id

into #tb1_sysprocesses
from sys.sysprocesses_ex with (nolock)

if @@error <> 0
begin
select @retcode = @@error
GOTO LABEL_86RETURN
end

-- -- -- -- Screen out any rows?

if (@loginame in ('active'))
delete #tb1_sysprocesses
where lower(status) = 'sleeping'
and upper(cmd) in (
'AWAITING COMMAND'
,'LAZY WRITER'
,'CHECKPOINT SLEEP'
)

and blocked = 0



-- -- -- -- Prepare to dynamically optimize column widths.


select
@charsidlow = convert(varchar(85),@sidlow)
,@charsidhigh = convert(varchar(85),@sidhigh)
,@charspidlow = convert(varchar,@spidlow)
,@charspidhigh = convert(varchar,@spidhigh)



select
@charMaxLenLoginName =
convert( varchar
,isnull( max( datalength(loginname)) ,5)
)

,@charMaxLenDBName =
convert( varchar
,isnull( max( datalength( rtrim(convert(varchar(128),db_name(dbid))))) ,6)
)

,@charMaxLenCPUTime =
convert( varchar
,isnull( max( datalength( rtrim(convert(varchar(128),cpu)))) ,7)
)

,@charMaxLenDiskIO =
convert( varchar
,isnull( max( datalength( rtrim(convert(varchar(128),physical_io)))) ,6)
)

,@charMaxLenCommand =
convert( varchar
,isnull( max( datalength( rtrim(convert(varchar(128),cmd)))) ,7)
)

,@charMaxLenHostName =
convert( varchar
,isnull( max( datalength( rtrim(convert(varchar(128),hostname)))) ,8)
)

,@charMaxLenProgramName =
convert( varchar
,isnull( max( datalength( rtrim(convert(varchar(128),program_name)))) ,11)
)

,@charMaxLenLastBatch =
convert( varchar
,isnull( max( datalength( rtrim(convert(varchar(128),last_batch_char)))) ,9)
)
from
#tb1_sysprocesses
where
spid >= @spidlow
and spid <= @spidhigh



-- -- -- -- Output the report.


EXEC(
'
SET nocount off

SELECT
SPID = convert(char(5),spid)

,Status =
CASE lower(status)
When '
'sleeping'' Then lower(status)
Else upper(status)
END

,Login = substring(loginname,1,'
+ @charMaxLenLoginName + ')

,HostName =
CASE hostname
When Null Then '
' .''
When '
' '' Then '' .''
Else substring(hostname,1,'
+ @charMaxLenHostName + ')
END

,BlkBy =
CASE isnull(convert(char(5),blocked),'
'0'')
When '
'0'' Then '' .''
Else isnull(convert(char(5),blocked),'
'0'')
END

,DBName = substring(case when dbid = 0 then null when dbid <> 0 then db_name(dbid) end,1,'
+ @charMaxLenDBName + ')
,Command = substring(cmd,1,'
+ @charMaxLenCommand + ')

,CPUTime = substring(convert(varchar,cpu),1,'
+ @charMaxLenCPUTime + ')
,DiskIO = substring(convert(varchar,physical_io),1,'
+ @charMaxLenDiskIO + ')

,LastBatch = substring(last_batch_char,1,'
+ @charMaxLenLastBatch + ')

,ProgramName = substring(program_name,1,'
+ @charMaxLenProgramName + ')
,SPID = convert(char(5),spid) -- Handy extra for right-scrolling users.
,REQUESTID = convert(char(5),request_id)
from
#tb1_sysprocesses -- Usually DB qualification is needed in exec().
where
spid >= '
+ @charspidlow + '
and spid <= '
+ @charspidhigh + '

order by spid_sort

SET nocount on
'

)


LABEL_86RETURN:


if (object_id('tempdb..#tb1_sysprocesses') is not null)
drop table #tb1_sysprocesses

return @retcode -- sp_who2

No comments:

Post a Comment

Total Pageviews