:SetVar Procedure "Download_process_list"
:r $(DropProcedureScript_AUX)
create procedure [$(Procedure)] @EmitCVSMetadata bit = 1,
                                @ForPlainText    bit = 0
AS
/*==================================================================================================
#
# DOWNLOADING OF SQL PROCESS LIST:
#
==================================================================================================*/
$(BEGIN_AUX)
----------------------------------------------------------------------------------------------------
if @EmitCVSMetadata is null
   exec [AUX:RaiseNullParameter] N'$(Procedure)', '@EmitCVSMetadata'
if @ForPlainText is null
   exec [AUX:RaiseNullParameter] N'$(Procedure)', '@ForPlainText'
----------------------------------------------------------------------------------------------------
declare @DateTime datetime2; set @DateTime=GETDATE();
----------------------------------------------------------------------------------------------------
-- RESULT-SET #1:
if @EmitCVSMetadata = 1
select
   N'SQL-processes '+
   dbo.[AUX:ToStringFS.Int]( YEAR  ( @DateTime ), 4 )+'-'+
   dbo.[AUX:ToStringFS.Int]( MONTH ( @DateTime ), 2 )+'-'+
   dbo.[AUX:ToStringFS.Int]( DAY   ( @DateTime ), 2 )+' '+
   dbo.[AUX:ToStringFS.Int]( DATEPART( hour  , @DateTime ), 2 )+
   dbo.[AUX:ToStringFS.Int]( DATEPART( minute, @DateTime ), 2 )
-- dbo.[AUX:ToStringFS.Int]( DATEPART( second, @DateTime ), 2 )
   'CSVBaseName'
----------------------------------------------------------------------------------------------------
-- RESULT-SET #2:
if @ForPlainText = 1
   set NOCOUNT off

select
   CONVERT( bit,
      case when p.[spid] = @@SPID
      then 1 else 0 end )     'Is current',
   IIF( @ForPlainText=1,
      ISNULL( db.[name], '' ),
      db.[name] )             'Database name',
   p.[spid]                   'SPID',
   case p.[status]
      when 'runnable' then UPPER(RTRIM( p.[status] ))
      else LOWER(RTRIM(  p.[status] ))
   end                        'Status',
   RTRIM( p.[cmd] )           'Command',
   p.[cpu]                    'CPU',
   RTRIM( p.[loginame] )      'Login name',
   RTRIM( p.[program_name] )  'Application name',
   RTRIM( p.[hostname] )      'Host name',
   p.[login_time]             'Login time',
   p.[last_batch]             'Last batch time'
from
   [sysprocesses] p (nolock)
left join
   [sysdatabases] db (nolock)
   on p.[dbid] = db.[dbid]
order by
   case
      when p.[status] = 'runnable'
      then 0 else 1
   end,
   case
      when p.[spid] = @@SPID
      then 0 else 1
   end,
   case
      when db.[dbid] is null
      then 1 else 0
   end,
   ISNULL( db.[name], '' ),
   p.[spid]

----------------------------------------------------------------------------------------------------
$(END_AUX)
/*================================================================================================*/
GO
/*
exec [Download_process_list]
exec [Download_process_list] @EmitCVSMetadata=0, @ForPlainText=1
*/
