June 15, 2012

sp_who (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_who(nvarchar @loginame)

MetaData:

 create procedure sys.sp_who  -- - 1995/11/28 15:48  
@loginame sysname = NULL -- or 'active'
as

declare @spidlow int,
@spidhigh int,
@spid int,
@sid varbinary(85)

select @spidlow = 0
,@spidhigh = 32767


if ( @loginame is not NULL
AND upper(@loginame collate Latin1_General_CI_AS) = 'ACTIVE'
)
begin

select spid , ecid, status
,loginame=rtrim(loginame)
,hostname ,blk=convert(char(5),blocked)
,dbname = case
when dbid = 0 then null
when dbid <> 0 then db_name(dbid)
end
,cmd
,request_id
from sys.sysprocesses_ex
where spid >= @spidlow and spid <= @spidhigh AND
upper(cmd) <> 'AWAITING COMMAND'

return (0)
end

if (@loginame is not NULL
AND upper(@loginame collate Latin1_General_CI_AS) <> 'ACTIVE'
)
begin
if (@loginame like '[0-9]%') -- is a spid.
begin
select @spid = convert(int, @loginame)
select spid, ecid, status,
loginame=rtrim(loginame),
hostname,blk = convert(char(5),blocked),
dbname = case
when dbid = 0 then null
when dbid <> 0 then db_name(dbid)
end
,cmd
,request_id
from sys.sysprocesses_ex
where spid = @spid
end
else
begin
select @sid = suser_sid(@loginame)
if (@sid is null)
begin
raiserror(15007,-1,-1,@loginame)
return (1)
end
select spid, ecid, status,
loginame=rtrim(loginame),
hostname ,blk=convert(char(5),blocked),
dbname = case
when dbid = 0 then null
when dbid <> 0 then db_name(dbid)
end
,cmd
,request_id
from sys.sysprocesses_ex
where sid = @sid
end
return (0)
end


-- loginame arg is null
select spid,
ecid,
status,
loginame=rtrim(loginame),
hostname,
blk=convert(char(5),blocked),
dbname = case
when dbid = 0 then null
when dbid <> 0 then db_name(dbid)
end
,cmd
,request_id
from sys.sysprocesses_ex
where spid >= @spidlow and spid <= @spidhigh

return (0) -- sp_who

No comments:

Post a Comment

Total Pageviews