#!/bin/csh -f
echo ""
echo "***************************************"
echo "* $0                                 *"
echo "* Vijay Mitra                          *"
echo "* `date` *"
echo "***************************************"
echo ""
isql -S $DSQUERY -U $DB_USER -P $DB_PASW -w132 << EOF 
DECLARE @dbid int, @tmpdbid int
SET NOCOUNT ON

SELECT @dbid = dbid
FROM master..sysprocesses
WHERE spid = @@spid

SELECT @tmpdbid = dbid
FROM master..sysdatabases
WHERE name = 'tempdb'

SET NOCOUNT OFF

SELECT convert(smallint, L.spid) Spid,
       P.hostname Host,
       convert(char(8), P.program_name) Prog,
       convert(char(5),
               isnull(replicate(P.hostprocess,
                                1 - patindex(P.hostprocess, '')
                                    * patindex ('', P.hostprocess)),
                      '' )) 'Proc',
       convert(char(12), V.name) Lock_Type,

       convert(char(25), object_name(L.id, L.dbid)) Table_Name_or_Id,
       page,
       convert(char(12), D.name) Db,
       convert(char(20), P.tran_name) 'Tran'
FROM   master..syslocks L,
       master..sysprocesses P,
       master..sysdatabases D,
       master..spt_values V
WHERE  L.spid *= P.spid
AND    V.type = 'L'
AND    L.type = V.number
AND    L.dbid *= D.dbid
ORDER BY 'Spid', Db, Table_Name_or_Id, Lock_Type
go

print ' '
go

select 
P.spid Spid, 
/*
P.kpid, 
*/
convert(tinyint,P.enginenum) Eng, 
convert(char(8),P.status) Status, 
/*
P.suid, 
*/
convert(char(10),L.name) who,
P.hostname Host, 
convert(char(16), P.program_name) Prog,
convert(char(5), P.hostprocess) 'Proc', 
P.cmd Cmd, 
/*
P.cpu CPU, 
*/
P.physical_io Phys_IO, 
/*
P.memusage Mem_Usg, 
*/
P.blocked Blk,
convert(char(20),db_name(P.dbid)) Db, 
isnull(convert(char(7),P.time_blocked),' ') Sec
/*
P.uid, 
P.gid, 
P.tran_name, 
P.time_blocked, 
P.network_pktsz
*/
from master..sysprocesses P
, master..syslogins L
where L.suid = P.suid
/*where P.blocked > 0*/
and P.cmd != 'AWAITING COMMAND'
and P.cmd != 'WAITFOR'
order by P.spid

set nocount off
go

EOF