use sybsystemprocs
go

drop procedure sp_lockconfig
go

-- sp_lockconfig, 'Lists data for lock promotions and index locking schemes'
-- sp_lockconfig, '   if SYS_FLAG is non-null include system tables'


create procedure sp_lockconfig (@SYS_FLAG  char (1) = NULL) as
   set ansinull                      on
   set flushmessage                  on
   set nocount                       on
   set string_rtruncation            on

   print ' '

   if (@@trancount = 0)
      begin
         set chained off

         if (@@isolation > 1)
            begin
               set transaction isolation level 1
            end
      end
   else
      begin
         print '    sp_lockconfig CANNOT BE RUN FROM WITHIN A TRANSACTION.'

         print ' '

         return 1
      end

   declare @allcount  varchar (7),
           @dpcount   varchar (7),
           @drcount   varchar (7),
           @sysval    smallint,
           @tabtext   varchar (12)

   create table #lockcfg
     (sort      tinyint       not null,
      type      char (8)      not null,
      name      varchar (30)  not null,
      levelx    varchar ( 5)  not null,
      txt       varchar (33)  not null)

   insert into #lockcfg
      select 1,
             'Table',
             object_name (object),
             'page',
             substring (char_value, 1, 33)
      from sysattributes
      where class       = 5
        and attribute   = 0
        and object_type = 'T'

   insert into #lockcfg
      select 1,
             'Table',
             object_name (object),
             'row',
             substring (char_value, 1, 33)
      from sysattributes
      where class       = 5
        and attribute   = 1
        and object_type = 'T'

   insert into #lockcfg
      select 2,
             'Database',
             db_name (),
             'page',
             substring (char_value, 1, 33)
      from master.dbo.sysattributes
      where class       = 5
        and attribute   = 0
        and object_type = 'D'
        and object      = db_id ()

   insert into #lockcfg
      select 2,
             'Database',
             db_name (),
             'row',
             substring (char_value, 1, 33)
      from master.dbo.sysattributes
      where class       = 5
        and attribute   = 1
        and object_type = 'D'
        and object      = db_id ()

   insert into #lockcfg
      select 3,
             'Server',
             'default lock scheme',
             '-',
             substring (c.value2, 1, 10)
      from master.dbo.sysconfigures f,
           master.dbo.syscurconfigs c
      where f.name    = 'lock scheme'
        and f.parent <> 19
        and f.config <> 19
        and c.config  = f.config

   insert into #lockcfg
      select 3,
             'Server',
             '-',
             'page',
             'PCT = '
             +  convert (varchar (11), pc.value)
             +  ', LWM = '
             +  convert (varchar (11), lc.value)
             +  ', HWM = '
             +  convert (varchar (11), hc.value)
      from master.dbo.sysconfigures pf,
           master.dbo.sysconfigures lf,
           master.dbo.sysconfigures hf,
           master.dbo.syscurconfigs pc,
           master.dbo.syscurconfigs lc,
           master.dbo.syscurconfigs hc
      where pf.config  = pc.config
        and pf.name    = 'page lock promotion PCT'
        and pf.parent <> 19
        and pf.config <> 19
        and lf.config  = lc.config
        and lf.name    = 'page lock promotion LWM'
        and lf.parent <> 19
        and lf.config <> 19
        and hf.config  = hc.config
        and hf.name    = 'page lock promotion HWM'
        and hf.parent <> 19
        and hf.config <> 19

   insert into #lockcfg
      select 3,
             'Server',
             '-',
             'row',
             'PCT = '
             +  convert (varchar (11), pc.value)
             +  ', LWM = '
             +  convert (varchar (11), lc.value)
             +  ', HWM = '
             +  convert (varchar (11), hc.value)
      from master.dbo.sysconfigures pf,
           master.dbo.sysconfigures lf,
           master.dbo.sysconfigures hf,
           master.dbo.syscurconfigs pc,
           master.dbo.syscurconfigs lc,
           master.dbo.syscurconfigs hc
      where pf.config  = pc.config
        and pf.name    = 'row lock promotion PCT'
        and pf.parent <> 19
        and pf.config <> 19
        and lf.config  = lc.config
        and lf.name    = 'row lock promotion LWM'
        and lf.parent <> 19
        and lf.config <> 19
        and hf.config  = hc.config
        and hf.name    = 'row lock promotion HWM'
        and hf.parent <> 19
        and hf.config <> 19

   select TYPE        = type,
          OBJECT      = substring (name, 1, 28),
          'LEVEL'     = levelx,
          'LOCK DATA' = txt
   from #lockcfg
   order by sort, name, levelx

   print ' '

   if (@SYS_FLAG IS NULL)
      begin
         select @sysval  = 3,
                @tabtext = 'USER'
      end
   else
      begin
         select @sysval  = 1,
                @tabtext = 'USER/SYSTEM'
      end

   select @allcount = ltrim (substring (convert (char (10),
                                                 convert (money,
                                                          count (*)),
                                                 1),
                                        1,
                                        7))
   from sysobjects
   where (sysstat & 15)    in (@sysval, 3)
     and (sysstat2 & 8192)  = 8192

   select @dpcount = ltrim (substring (convert (char (10),
                                                convert (money,
                                                         count (*)),
                                                1),
                                       1,
                                       7))
   from sysobjects
   where (sysstat & 15)     in (@sysval, 3)
     and (sysstat2 & 16384)  = 16384

   select @drcount = ltrim (substring (convert (char (10),
                                                convert (money,
                                                         count (*)),
                                                1),
                                       1,
                                       7))
   from sysobjects
   where (sysstat & 15)     in (@sysval, 3)
     and (sysstat2 & 32768)  = 32768

   if ((@allcount <> '0')  and  (@dpcount = '0')  and  (@drcount = '0'))
      begin
         print '    ALL %1! TABLES USE ALLPAGES LOCKING.', @tabtext
      end
   else if ((@allcount = '0')  and  (@dpcount <> '0')  and  (@drcount = '0'))
      begin
         print '    ALL %1! TABLES USE DATAPAGES LOCKING.', @tabtext
      end
   else if ((@allcount = '0')  and  (@dpcount = '0')  and  (@drcount <> '0'))
      begin
         print '    ALL %1! TABLES USE DATAROWS LOCKING.', @tabtext
      end
   else
      begin
         if (@allcount = '0')
            begin
               print '    THERE ARE NO %1! TABLES WITH ALLPAGES LOCKING.', @tabtext
            end
         else
            begin
               print '    THERE ARE %1! %2! TABLES WITH ALLPAGES LOCKING.',
                     @allcount, @tabtext

               print ' '

               select 'TABLE' = name,
                      OWNER   = user_name (uid)
               from sysobjects
               where (sysstat & 15)    in (@sysval, 3)
                 and (sysstat2 & 8192)  = 8192
               order by 'TABLE', OWNER
            end

         print ' '

         if (@dpcount = '0')
            begin
               print '    THERE ARE NO %1! TABLES WITH DATAPAGES LOCKING.',
                     @tabtext
            end
         else
            begin
               print '    THERE ARE %1! %2! TABLES WITH DATAPAGES LOCKING.',
                     @dpcount, @tabtext

               print ' '

                  select 'TABLE' = space (30),
                         OWNER   = space (30)
                  where 1 = 2
               union
                  select substring (name  +  ' *',
                                    1,
                                    30),
                         user_name (uid)
                  from sysobjects
                  where (sysstat & 15)     in (@sysval, 3)
                    and (sysstat2 & 16384)  = 16384
                    and (sysstat2 & 131072) = 131072
               union
                  select name,
                         user_name (uid)
                  from sysobjects
                  where (sysstat & 15)      in (@sysval, 3)
                    and (sysstat2 & 16384)   = 16384
                    and (sysstat2 & 131072) <> 131072
               order by 'TABLE', OWNER
            end

         print ' '

         if (@drcount = '0')
            begin
               print '    THERE ARE NO %1! TABLES WITH DATAROWS LOCKING.',
                     @tabtext
            end
         else
            begin
               print '    THERE ARE %1! %2! TABLES WITH DATAROWS LOCKING.',
                     @drcount, @tabtext

               print ' '

                  select 'TABLE' = space (30),
                         OWNER   = space (30)
                  where 1 = 2
               union
                  select substring (name  +  ' *',
                                    1,
                                    30),
                         user_name (uid)
                  from sysobjects
                  where (sysstat & 15)      in (@sysval, 3)
                    and (sysstat2 & 32768)   = 32768
                    and (sysstat2 & 131072)  = 131072
               union
                  select name,
                         user_name (uid)
                  from sysobjects
                  where (sysstat & 15)      in (@sysval, 3)
                    and (sysstat2 & 32768)   = 32768
                    and (sysstat2 & 131072) <> 131072
               order by 'TABLE', OWNER
            end
      end

   print ' '
go
sp_procxmode sp_lockconfig, anymode
go
