/*---------------------------------------------------------------------
  $Header: /abasql/aba_lockinfo_sql65.sp 7     01-07-16 22:24 Sommar $

  This SP lists locking information for all active processes, that is
  processes that have a lock or are not AWAITING COMMAND. Information
  about all locked objects are included, as well the last command sent
  from the client. Note that this command is tacked out afterwards with
  DBCC INPUTBUFFER, and may be out of sync with the rest of the data.

  This SP should reside in its own database. Under no circumstances must
  it reside in tempdb! As the SP creates and drops permanent tables,
  it is not reentrant, that is it cannot be run by two users simul-
  taneously.

  The original source for the SP was taken from the undocumented
  system procedure sp_lockinfo.

  $History: aba_lockinfo_sql65.sp $
 * 
 * *****************  Version 7  *****************
 * User: Sommar       Date: 01-07-16   Time: 22:24
 * Updated in $/abasql
 * Changed order of columns in final result set.
 *
 * *****************  Version 6  *****************
 * User: Sommar       Date: 01-06-09   Time: 15:20
 * Updated in $/abasql
 * Improved version groups only lists a count for multiple locks by
 * default.
 *
 * *****************  Version 5  *****************
 * User: Sommar       Date: 01-03-17   Time: 22:07
 * Updated in $/abasql
 * Added SET QUOTED_IDENTIFIER OFF for the benefit of people outside
 * Abaris who might have this on.
 *
 * *****************  Version 4  *****************
 * User: Sommar       Date: 00-11-06   Time: 9:44
 * Updated in $/projects/dbverktyg/abasql
 * Fix for last_since causing overflow. More effecient way of getting
 * object names.
 *
 * *****************  Version 3  *****************
 * User: Sommar       Date: 00-02-09   Time: 13:19
 * Updated in $/projects/dbverktyg/abasql
 * Stupid bug: last_since was 10 times too big.
 *
 * *****************  Version 2  *****************
 * User: Sommar       Date: 99-12-21   Time: 19:39
 * Updated in $/projects/dbverktyg/abasql
 * Hide system processes.
 *
 * *****************  Version 1  *****************
 * User: Sommar       Date: 99-12-21   Time: 19:33
 * Created in $/projects/dbverktyg/abasql
  ---------------------------------------------------------------------*/
SET QUOTED_IDENTIFIER OFF
go
CREATE PROCEDURE aba_lockinfo @processes tinyint = 0,
                              @details   bit     = 0 AS

DECLARE @lkdbnm     varchar(32),
        @lkdbid     smallint,
        @stmt       varchar(255),
        @stmt2      varchar(255),
        @spid       smallint,
        @spidstr    varchar(10),
        @str        varchar(255),
        @ix         tinyint,
        @blklvl     tinyint,
        @cntstr     varchar(32),

        @loginlen   varchar(5),
        @cntlen     varchar(5),
        @statuslen  varchar(5),
        @dbnamelen  varchar(5),
        @hostlen    varchar(5),
        @cmdlen     varchar(5),
        @appllen    varchar(5),
        @locktlen   varchar(5),
        @lkobjlen   varchar(5),
        @cpulen     varchar(5),
        @physiolen  varchar(5),
        @memlen     varchar(5),
        @delaylen   varchar(5)


SET NOCOUNT ON
SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED

-- Create working tables, drop if needed.
IF EXISTS (SELECT * FROM sysobjects WHERE name = "lockinfo")
   DROP TABLE lockinfo

IF EXISTS (SELECT * FROM sysobjects WHERE name = "inputbuffer")
   DROP TABLE inputbuffer

CREATE TABLE inputbuffer(inputbuffer varchar(255))

CREATE TABLE lockinfo (
   id          int IDENTITY(1, 1) NOT NULL,
   last        bit                NOT NULL DEFAULT 0,
   cnt         int                NOT NULL,
   active      bit                NOT NULL,
   spid        smallint           NULL,
   login       sysname            NULL,
   status      varchar(10)        NULL,
   dbname      sysname            NULL,
   host        varchar(15)        NULL,
   command     varchar(16)        NULL,
   appl        varchar(30)        NULL,
   blking      smallint           NOT NULL,
   blkby       smallint           NULL,
   blklvl      smallint           NOT NULL,
   locktype    varchar(30)        NOT NULL,
   waittype    binary(2)          NULL,
   lktype      smallint           NULL,
   lkobjdbid   smallint           NULL,
   lkobjid     int                NULL,
   lkobj       varchar(70)        NOT NULL,
   cpu         int                NULL,
   physio      int                NULL,
   memusage    int                NULL,
   now         char(12)           NOT NULL,
   login_time  char(16)           NULL,
   last_batch  char(16)           NULL,
   last_since  numeric(10,3)      NULL,
   delay       int                NOT NULL,
   inputbuffer char(255)          NOT NULL)

IF @details = 0
BEGIN
   -- In this mode, get the count of locks of each type. This makes the output
   -- much smaller, but is possibly more unsafe, as locks and processes are
   -- not capture simultaneously.
   INSERT lockinfo (active, cnt, login, spid,
                    status, dbname, host, command,
                    appl, blking, blkby, blklvl, lktype, lkobjdbid, lkobjid,
                    locktype, lkobj, cpu, physio, memusage, waittype,
                    now,
                    login_time,
                    last_batch,
                    last_since, delay, inputbuffer)
      SELECT CASE WHEN (upper(p.cmd) <> 'AWAITING COMMAND' OR l.spid IS NOT NULL)
                       AND coalesce(p.spid, 0) <> @@spid
                       AND coalesce(p.spid, 10) > 4
                       THEN 1
                  ELSE 0
             END, isnull(l.cnt, 0), suser_name(p.suid), coalesce(p.spid, l.spid),
             p.status, db_name(p.dbid), p.hostname, p.cmd,
             p.program_name, 0, p.blocked, 0, l.type, l.dbid, l.id,
             ' ', ' ', p.cpu, p.physical_io, p.memusage, p.waittype,
             convert(char(12), getdate(), 14),
             convert(char(7), p.login_time, 12) + convert(char(9), p.login_time, 8),
             convert(char(7), p.last_batch, 12) + convert(char(9), p.last_batch, 8),
             CASE WHEN datediff(DAY, p.last_batch, getdate()) > 20
                  THEN NULL
                  ELSE datediff(MS, p.last_batch, getdate()) / 1000.000
             END, 0, ' '
      FROM master..sysprocesses p
      FULL JOIN (SELECT spid, dbid, id, type, COUNT(*) AS cnt
                 FROM   master..syslocks
                 GROUP  BY spid, dbid, id, type) AS l ON p.spid = l.spid
      WHERE (upper(p.cmd) <> 'AWAITING COMMAND' OR
             l.spid IS NOT NULL OR
             @processes > 0)
        AND  (p.spid IS NULL OR p.spid <> @@spid OR @processes > 0)
        AND  (p.spid IS NULL OR p.spid > 4 OR @processes > 0)
END
ELSE
BEGIN
   -- Get all locks.
   INSERT lockinfo (active, cnt, login, spid,
                    status, dbname, host, command,
                    appl, blking, blkby, blklvl, lktype, lkobjdbid, lkobjid,
                    locktype,
                    lkobj, cpu, physio, memusage, waittype,
                    now,
                    login_time,
                    last_batch,
                    last_since, delay, inputbuffer)
      SELECT CASE WHEN (upper(p.cmd) <> 'AWAITING COMMAND' OR l.spid IS NOT NULL)
                       AND coalesce(p.spid, 0) <> @@spid
                       AND coalesce(p.spid, 10) > 4
                       THEN 1
                  ELSE 0
             END, 1, suser_name(p.suid), coalesce(p.spid, l.spid),
             p.status, db_name(p.dbid), p.hostname, p.cmd,
             p.program_name, 0, p.blocked, 0, l.type, l.dbid, l.id,
             ' ', ' ', p.cpu, p.physical_io, p.memusage, p.waittype,
             convert(char(12), getdate(), 14),
             convert(char(7), p.login_time, 12) + convert(char(9), p.login_time, 8),
             convert(char(7), p.last_batch, 12) + convert(char(9), p.last_batch, 8),
             CASE WHEN datediff(DAY, p.last_batch, getdate()) > 20
                  THEN NULL
                  ELSE datediff(MS, p.last_batch, getdate()) / 1000.000
             END, 0, ' '
      FROM master..sysprocesses p
      FULL JOIN master..syslocks l ON p.spid = l.spid
      WHERE (upper(p.cmd) <> 'AWAITING COMMAND' OR
             l.spid IS NOT NULL OR
             @processes > 0)
        AND  (p.spid IS NULL OR p.spid <> @@spid OR @processes > 0)
        AND  (p.spid IS NULL OR p.spid > 4 OR @processes > 0)
END

/* ======================== */
/* Get data from spt_values */
/* ======================== */
UPDATE l
SET    locktype = v1.name,
       lkobj    = CASE l.id WHEN 0 THEN v2.name ELSE ' ' END
FROM   lockinfo l
JOIN   master..spt_values v1 ON v1.number = l.lktype
                            AND v1.type   = 'L'
LEFT   JOIN master..spt_values v2 ON v2.number = l.lktype
                            AND v2.type   = 'SFL'
WHERE  l.lktype IS NOT NULL



/* ======================= */
/* Get input buffers       */
/* ======================= */
IF @processes < 2
BEGIN
   EXEC ("DECLARE C2 CURSOR FOR
             SELECT DISTINCT str(spid), spid
             FROM   lockinfo
             WHERE  active = 1
               AND  login IS NOT NULL")  -- Those with login NULL are orphaned locks.
END
ELSE
BEGIN
   EXEC ("DECLARE C2 CURSOR FOR
             SELECT DISTINCT str(spid), spid
             FROM   lockinfo
             WHERE  login IS NOT NULL")
END
OPEN C2

WHILE 1 = 1
BEGIN
   FETCH C2 INTO @spidstr, @spid
   IF @@fetch_status <> 0
      BREAK

   TRUNCATE TABLE inputbuffer
   INSERT inputbuffer EXEC ("DBCC INPUTBUFFER (" + @spidstr + ") WITH NO_INFOMSGS")

   SELECT @str = ' '
   SELECT @str = rtrim(inputbuffer) FROM inputbuffer

   -- If there is a null char, cut string at this point.
   SELECT @ix = charindex(char(0), @str)
   IF @ix > 0
      SELECT @str = substring(@str, 1, @ix)

   -- Replace line breaks with spaces.
   WHILE  1 = 1
   BEGIN
      SELECT @ix = charindex(char(10), @str)
      IF @ix = 0
         SELECT @ix = charindex(char(13), @str)
      IF @ix = 0
         BREAK
      SELECT @str = substring(@str, 1, @ix - 1) + ' ' +
                    substring(@str, @ix + 1, 255)
   END

   UPDATE lockinfo
   SET    inputbuffer = isnull(substring(@str, 1, datalength(@str) - 1), ' '),
          delay       = datediff(ms, now, convert(char(12), getdate(), 14))
   WHERE  spid = @spid
END

DEALLOCATE C2

/*-------------------------------*/
/* Mark last row                */
/*-------------------------------*/

UPDATE lockinfo
SET    last = 1
FROM   lockinfo l1
WHERE  id = (SELECT MAX(id) FROM lockinfo l2 WHERE l2.spid = l1.spid)

/* ======================= */
/* flag blocking processes */
/* ======================= */

UPDATE lockinfo
SET    blking = 1
WHERE  spid in (SELECT blkby FROM lockinfo WHERE blkby > 0)

UPDATE lockinfo
SET    blklvl = 1
WHERE  blking = 1
  AND  blkby  = 0

SELECT @blklvl = 1

WHILE EXISTS (SELECT * FROM lockinfo WHERE blkby > 0 AND blklvl = 0) AND
      @blklvl < 20
BEGIN
   UPDATE l1
   SET    blklvl = @blklvl + 1
   FROM   lockinfo l1, lockinfo l2
   WHERE  l1.blkby = l2.spid
     AND  l1.blkby > 0
     AND  l1.blklvl = 0
     AND  l2.blklvl = @blklvl

   SELECT @blklvl = @blklvl + 1
END

/* =============================================*/
/* Get object names per database.               */
/* =============================================*/

EXEC ('DECLARE c1 CURSOR FOR
          SELECT DISTINCT d.name, l.lkobjdbid
          FROM   lockinfo l, master..sysdatabases d
          WHERE  l.lkobjdbid = d.dbid
            AND  l.lkobjid > 0
      FOR READ ONLY')

OPEN c1

WHILE 1 = 1
BEGIN
   FETCH c1 INTO @lkdbnm, @lkdbid
   IF @@fetch_status <> 0
      BREAK

   /* set database.owner.name of locked objects in lockinfo */
   SELECT @stmt  = " UPDATE l " +
                   " SET    lkobj = '" + @lkdbnm + ".' + u.name + '.' + o.name " +
                   " FROM   lockinfo l "
   SELECT @stmt2 = " JOIN   " + @lkdbnm + "..sysobjects o ON l.lkobjid = o.id " +
                   " JOIN   " + @lkdbnm + "..sysusers   u ON u.uid = o.uid " +
                   " WHERE  l.lkobjdbid = " + convert(char, @lkdbid)
   EXEC(@stmt + @stmt2)
END
DEALLOCATE c1

UPDATE l
SET    lkobj = d.name
FROM   lockinfo l
JOIN   master..sysdatabases d ON l.lkobjdbid = d.dbid
WHERE  l.lkobjid = 0

----------------------------------------------
-- Get lengths
---------------------------------------------
SELECT @loginlen   = convert(varchar(5), isnull(max(datalength(login)), 1)),
       @cntlen     = convert(varchar(5), isnull(max(datalength(ltrim(str(cnt)))), 1)),
       @statuslen  = convert(varchar(5), isnull(max(datalength(status)), 1)),
       @dbnamelen  = convert(varchar(5), isnull(max(datalength(dbname)), 1)),
       @hostlen    = convert(varchar(5), isnull(max(datalength(host)), 1)),
       @cmdlen     = convert(varchar(5), isnull(max(datalength(command)), 1)),
       @appllen    = convert(varchar(5), isnull(max(datalength(appl)), 1)),
       @locktlen   = convert(varchar(5), isnull(max(datalength(locktype)), 1)),
       @lkobjlen   = convert(varchar(5), isnull(max(datalength(lkobj)), 1)),
       @cpulen     = convert(varchar(5), isnull(max(datalength(ltrim(str(cpu)))), 1)),
       @physiolen  = convert(varchar(5), isnull(max(datalength(ltrim(str(physio)))), 1)),
       @memlen     = convert(varchar(5), isnull(max(datalength(ltrim(str(memusage)))), 1)),
       @delaylen   = convert(varchar(5), isnull(max(datalength(ltrim(str(delay)))), 1))
FROM   lockinfo

-- Set @cntstr, this column is only to be shown when @details is = 0.
SELECT @cntstr = CASE @details
                    WHEN 1 THEN ""
                    ELSE "cnt = convert(varchar( " + @cntlen + "), cnt), "
                 END

/* ====== return the lockinfo table ===== */
EXEC("
   SELECT spid, " + @cntstr + "
          login    = coalesce(convert(varchar( " + @loginlen + "), login), ' '),
          status   = coalesce(convert(varchar( " + @statuslen + "), status), ' '),
          command  = coalesce(convert(varchar( " + @cmdlen + "), command), ' '),
          dbname   = coalesce(convert(varchar( " + @dbnamelen + "), dbname), ' '),
          host     = coalesce(convert(varchar( " + @hostlen + "), host), ' '),
          appl     = coalesce(convert(varchar( " + @appllen + "), appl), ' '),
          lvl      = CASE blklvl
                          WHEN 0 THEN ' '
                          WHEN 1 THEN '!!'
                          ELSE convert(varchar(3), blklvl - 1)
                     END,
          blkby   = CASE coalesce(blkby, 0)
                         WHEN 0 THEN ' '
                        ELSE convert(varchar(5), blkby)
                     END,
          locktype = convert(varchar( " + @locktlen + "), locktype),
          object   = convert(varchar( " + @lkobjlen + "), lkobj),
          waittype,
          cpu      = coalesce(convert(varchar( " + @cpulen + "), cpu),  ' '),
          io       = coalesce(convert(varchar( " + @physiolen + "), physio),  ' '),
          memusg   = coalesce(convert(varchar( " + @memlen + "), memusage), ' '),
          now,
          login_time = coalesce(login_time, ' '),
          last_batch = coalesce(last_batch, ' '),
          last_since = CASE WHEN last_since IS NOT NULL
                            THEN str(last_since, 11, 3)
                            ELSE ' '
                       END,
          delay    = ' ' + CASE WHEN delay > 0
                                THEN convert(varchar( " + @delaylen + "), delay)
                                ELSE ''
                           END,
          inputbuffer,
          CASE last WHEN 1 THEN char(13) + char(10) ELSE ' ' END
   FROM   lockinfo
   ORDER  BY spid, last ASC")

DROP TABLE lockinfo
DROP TABLE inputbuffer

go