use sybsystemprocs
go

sp_dropprocedure sp_arguments
go
sp_dropprocedure sp_checks
go
sp_dropprocedure sp_defaults
go
sp_dropprocedure sp_parameters          -- renamed as sp_arguments
go
sp_dropprocedure sp_procs_fillprintname
go
sp_dropprocedure sp_procs
go
sp_dropprocedure sp_rules_all
go
sp_dropprocedure sp_rules
go
sp_dropprocedure sp_showtable_detail
go
sp_dropprocedure sp_showtable
go
sp_dropprocedure sp_showtables
go
sp_dropprocedure sp_tablesx
go
sp_dropprocedure sp_triggers
go
sp_dropprocedure sp_usertypes
go
sp_dropprocedure sp_views
go

dump transaction sybsystemprocs with truncate_only
go

set quoted_identifier on
go

create procedure sp_arguments (@PROCEDURE  varchar (63) = 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_arguments CANNOT BE RUN FROM WITHIN A TRANSACTION.'

         print ' '

         return 1
      end

   declare @cmdstring  varchar (255),
           @dotpstn    tinyint,
           @id         int,
           @owner      varchar ( 30),
           @parmlen    tinyint,
           @spcount    smallint,
           @splen      tinyint,
           @spcstring  varchar (  7),
           @uidcount   tinyint

   create table #arguments
      (spname     varchar (40)  not null,
       pname      varchar (30)  not null,
       colid      tinyint       not null,
       typname    char (16)     not null,
       utype      smallint      not null,
       stype      tinyint       not null,
       len        tinyint       not null,
       prec       tinyint       null,
       scale      tinyint       null,
       sortfield  varchar (50)  not null)

   if (@PROCEDURE IS NULL)
      begin
         if (exists (select *
                     from sysobjects
                     where (sysstat & 15) = 4))
            begin
               select @spcount = count (o.uid)
               from sysobjects o
               where (o.sysstat & 15) = 4
                 and not exists (select c.*
                                 from syscolumns c
                                 where c.id = o.id)

               if (@spcount > 0)
                  begin
                     select @spcstring = ltrim (substring (convert (char (10),
                                                                    convert (money,
                                                                             @spcount),
                                                                    1),
                                                           1,
                                                           7))

                     print '    THERE ARE %1! STORED PROCEDURES WITHOUT ARGUMENTS.',
                           @spcstring

                     print ' '

                     create table #noparms
                        (spname  varchar (63)  not null)

                     insert into #noparms
                        select o.name
                        from sysobjects o
                        where o.uid            = 1
                          and (o.sysstat & 15) = 4
                          and 1                = (select count (o2.uid)
                                                  from sysobjects o2
                                                  where o2.name           = o.name
                                                    and (o2.sysstat & 15) = 4)
                          and not exists (select c.*
                                          from syscolumns c
                                          where c.id = o.id)

                     insert into #noparms
                        select o.name + ' (' + user_name (o.uid) + ')'
                        from sysobjects o
                        where o.uid            = 1
                          and (o.sysstat & 15) = 4
                          and 1                < (select count (o2.uid)
                                                  from sysobjects o2
                                                  where o2.name           = o.name
                                                    and (o2.sysstat & 15) = 4)
                          and not exists (select c.*
                                          from syscolumns c
                                          where c.id = o.id)

                     select @splen = max (char_length (spname))
                     from #noparms

                     select @cmdstring = 'select ''STORED PROCEDURE'' = substring (spname, 1,'
                                         +  convert (varchar (2), @splen)
                                         +  ')'
                                         +  ' from #noparms'
                                         +  ' order by ''STORED PROCEDURE'''

                     execute  (@cmdstring)
                  end
               else
                  begin
                     print '    THERE ARE NO PROCEDURES WITHOUT ARGUMENTS.'
                  end

               print ' '

               select @spcount = count (o.uid)
               from sysobjects o
               where (o.sysstat & 15) = 4
                 and exists (select c.*
                             from syscolumns c
                             where c.id = o.id)

               if (@spcount > 0)
                  begin
                     select @spcstring = ltrim (substring (convert (char (10),
                                                                   convert (money,
                                                                            @spcount),
                                                                   1),
                                                          1,
                                                          7))

                     print '    THERE ARE %1! STORED PROCEDURES WITH ARGUMENTS.',
                           @spcstring

                     print ' '

                     insert into #arguments
                        select o.name,
                               substring (c.name, 2, 29),
                               c.colid,
                               ' ',
                               c.usertype,
                               c.type,
                               c.length,
                               c.prec,
                               c.scale,
                               substring (o.name + ' ' + user_name (o.uid), 1, 50)
                        from syscolumns c,
                             sysobjects o
                        where o.uid            = 1
                          and (o.sysstat & 15) = 4
                          and o.id             = c.id
                          and 1                = (select count (o2.uid)
                                                  from sysobjects o2
                                                  where o2.name           = o.name
                                                    and (o2.sysstat & 15) = 4)

                     insert into #arguments
                        select substring (o.name + ' (' + user_name (o.uid) + ')', 1, 40),
                               substring (c.name, 2, 29),
                               c.colid,
                               ' ',
                               c.usertype,
                               c.type,
                               c.length,
                               c.prec,
                               c.scale,
                               substring (o.name + ' ' + user_name (o.uid), 1, 50)
                        from syscolumns c,
                             sysobjects o
                        where o.uid            = 1
                          and (o.sysstat & 15) = 4
                          and o.id             = c.id
                          and 1                < (select count (o2.uid)
                                                  from sysobjects o2
                                                  where o2.name           = o.name
                                                    and (o2.sysstat & 15) = 4)

                     insert into #arguments
                        select substring (o.name + ' (' + user_name (o.uid) + ')', 1, 40),
                               substring (c.name, 2, 29),
                               c.colid,
                               ' ',
                               c.usertype,
                               c.type,
                               c.length,
                               c.prec,
                               c.scale,
                               substring (o.name + ' ' + user_name (o.uid), 1, 50)
                        from syscolumns c,
                             sysobjects o
                        where o.uid            <> 1
                          and (o.sysstat & 15)  = 4
                          and o.id              = c.id

                     update #arguments
                     set typname = t.name
                     from systypes t
                     where #arguments.utype > 99
                       and #arguments.utype = t.usertype

                     update #arguments
                     set typname = t.name
                     from systypes t
                     where #arguments.utype in (5, 6, 7, 11, 12, 16, 18, 21, 22)
                       and #arguments.utype  = t.usertype

                     update #arguments
                     set len = len / @@ncharsize
                     where ((utype = 24)  and  (stype = 47))
                        or ((utype = 25)  and  (stype = 39))

                     update #arguments
                     set typname = t.name + ' (' + convert (varchar (3), #arguments.len) + ')'
                     from systypes t
                     where #arguments.utype in (1, 2, 3, 4, 24, 25)
                       and #arguments.utype  = t.usertype

                     update #arguments
                     set typname = t.name + ' (' + convert (varchar (3), #arguments.prec)
                                   + ', ' +  convert (varchar (3), #arguments.scale) + ')'
                     from systypes t
                     where #arguments.utype in (10, 26)
                       and #arguments.utype  = t.usertype

                     update #arguments
                     set typname = 'float (' +  convert (varchar (2), prec) + ')'
                     where utype in (8, 23)
                       and prec  IS NOT NULL

                     update #arguments
                     set typname = 'real'
                     where ((utype = 23)  or  ((utype = 8) and (len = 4)))
                       and prec IS NULL

                     update #arguments
                     set typname = 'double precision'
                     where utype  = 8
                       and len    = 8
                       and prec  IS NULL

                     update #arguments
                     set spname = ' '
                     where colid > 1

                     select @splen   = max (char_length (spname)),
                            @parmlen = max (char_length (pname))
                     from #arguments

                     if (@splen < 16)
                        begin
                           select @splen = 16
                        end

                     if (@parmlen < 9)
                        begin
                           select @parmlen = 9
                        end

                     if ((@splen + @parmlen) > 55)
                        begin
                           while ((@splen + @parmlen) > 55)
                              begin
                                 if (@splen > 16)
                                    begin
                                       select @splen = @splen - 1
                                    end

                                 if (@parmlen > 9)
                                    begin
                                       select @parmlen = @parmlen - 1
                                    end
                              end

                           select @splen = 55 - @parmlen
                        end

                     select @cmdstring = 'select ''STORED PROCEDURE'' = substring (spname, 1,'
                                         +  convert (varchar (2), @splen)
                                         +  '),'
                                         +  '''ARGUMENT'' = substring (pname, 1,'
                                         +  convert (varchar (2), @parmlen)
                                         +  '),'
                                         +  '''DATATYPE'' = typname'
                                         +  ' from #arguments'
                                         +  ' order by sortfield, colid'

                     execute  (@cmdstring)
                  end
               else
                  begin
                     print '    THERE ARE NO STORED PROCEDURES WITH ARGUMENTS.'

                     print ' '
                  end
            end
         else
            begin
               print '    THERE ARE NO STORED PROCEDURES.'

               print ' '
            end
      end
   else
      begin
         select @dotpstn = charindex ('.', @PROCEDURE)

         select @id  = id
         from sysobjects
         where id             = object_id (@PROCEDURE)
           and (sysstat & 15) = 4

         if ((@dotpstn > 0)  and
             (charindex ('.', substring (@PROCEDURE, 1 + @dotpstn, 63)) > 0))
            begin
               print '    A DATABASE CANNOT BE SPECIFIED FOR THE PROCEDURE ARGUMENT.'

               print ' '
            end
         else if (@id IS NOT NULL)
            begin
               if (exists (select *
                           from syscolumns
                           where id = @id))
                  begin
                     insert into #arguments
                        select @PROCEDURE, substring (c.name, 2, 29), c.colid, t.name,
                               c.usertype, c.type, c.length, c.prec, c.scale, ' '
                        from syscolumns c,
                             systypes t
                        where c.id       = @id
                          and t.usertype = c.usertype
                  end
               else
                  begin
                     print '    PROCEDURE %1! HAS NO ARGUMENTS.', @PROCEDURE

                     print ' '
                  end
            end
         else if ((@dotpstn = 0)  and
                  (exists (select *
                           from sysobjects
                           where name           = @PROCEDURE
                             and (sysstat & 15) = 4)))
            begin
               select @uidcount = count (id)
               from sysobjects
               where name           = @PROCEDURE
                 and (sysstat & 15) = 4

               if (@uidcount = 1)
                  begin
                     select @id    = id,
                            @owner = user_name (uid)
                     from sysobjects
                     where name           = @PROCEDURE
                       and (sysstat & 15) = 4

                     print '    PROCEDURE %1! IS OWNED BY %2!.',
                           @PROCEDURE, @owner

                     print ' '

                     if (exists (select *
                                 from syscolumns
                                 where id = @id))
                        begin
                           insert into #arguments
                              select ' ', substring (c.name, 2, 29), c.colid,
                                     t.name, c.usertype, c.type, c.length,
                                     c.prec, c.scale, ' '
                              from syscolumns c,
                                   systypes t
                              where c.id       = @id
                                and t.usertype = c.usertype
                        end
                     else
                        begin
                           print '    PROCEDURE %1! HAS NO ARGUMENTS.', @PROCEDURE

                           print ' '
                        end
                  end
               else
                  begin
                     print '    STORED PROCEDURE %1! IS NOT UNIQUE.  QUALIFY WITH THE OWNER NAME.',
                           @PROCEDURE

                     print ' '

                     select OWNERS = user_name (uid)
                     from sysobjects
                     where name           = @PROCEDURE
                       and (sysstat & 15) = 4
                  end
            end
         else if ((@PROCEDURE like 'sp[_]%')  and  (db_name () <> 'sybsystemprocs'))
            begin
               print '    STORED PROCEDURE %1! IS BEING CHECKED IN sybsystemprocs.',
                     @PROCEDURE

               print ' '

               select @id = id
               from sybsystemprocs.dbo.sysobjects
               where name           = @PROCEDURE
                 and uid            = 1
                 and (sysstat & 15) = 4

               if (@id IS NULL)
                  begin
                     print '    STORED PROCEDURE %1! DOES NOT EXIST IN EITHER DATABASE.',
                           @PROCEDURE

                     print ' '
                  end
               else
                  begin
                     if (exists (select *
                                 from sybsystemprocs.dbo.syscolumns c
                                 where id = @id))
                        begin
                           insert into #arguments
                              select ' ', substring (c.name, 2, 29), c.colid,
                                     t.name, c.usertype, c.type, c.length,
                                     c.prec, c.scale, ' '
                              from sybsystemprocs.dbo.syscolumns c,
                                   sybsystemprocs.dbo.systypes t
                              where c.id       = @id
                                and t.usertype = c.usertype
                        end
                     else
                        begin
                           print '    STORED PROCEDURE %1! HAS NO ARGUMENTS.', @PROCEDURE

                           print ' '
                        end
                  end
            end
         else
            begin
               print '    STORED PROCEDURE %1! DOES NOT EXIST.', @PROCEDURE

               print ' '
            end

         if (exists (select *
                     from #arguments))
            begin
               print '    STORED PROCEDURE %1! HAS ARGUMENTS.', @PROCEDURE

               print ' '

               update #arguments
               set typname = t.name
               from systypes t
               where #arguments.utype in (5, 6, 7, 11, 12, 21, 22)
                 and #arguments.utype  = t.usertype

               update #arguments
               set len = len / @@ncharsize
               where ((utype = 24)  and  (stype = 47))
                  or ((utype = 25)  and  (stype = 39))

               update #arguments
               set typname = t.name + ' (' + convert (varchar (3), #arguments.len) + ')'
               from systypes t
               where #arguments.utype in (1, 2, 3, 4, 24, 25)
                 and #arguments.utype  = t.usertype

               update #arguments
               set typname = t.name + ' (' + convert (varchar (3), #arguments.prec)
                             + ', ' +  convert (varchar (3), #arguments.scale) + ')'
               from systypes t
               where #arguments.utype in (10, 26)
                 and #arguments.utype  = t.usertype

               update #arguments
               set typname = 'float (' +  convert (varchar (2), prec) + ')'
               where utype in (8, 23)
                 and prec  IS NOT NULL

               update #arguments
               set typname = 'real'
               where ((utype = 23)  or  ((utype = 8) and (len = 4)))
                 and prec IS NULL

               update #arguments
               set typname = 'double precision'
               where utype  = 8
                 and len    = 8
                 and prec  IS NULL

               select @parmlen = max (char_length (pname))
               from #arguments

               select @cmdstring = 'select ''ARGUMENT'' = substring (pname, 1,'
                                   +  convert (varchar (2), @parmlen)
                                   +  '),'
                                   +  '''DATATYPE'' = typname'
                                   +  ' from #arguments'
                                   +  ' order by sortfield, colid'

               execute  (@cmdstring)
            end
      end
go
sp_addcomment sp_arguments, 'Lists arguments for PROCEDURE or (default) all procedures'
go
sp_procxmode sp_arguments, anymode
go

create procedure sp_checks (@TABLE varchar (63) = 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_checks CANNOT BE RUN FROM WITHIN A TRANSACTION.'

         print ' '

         return 1
      end

   declare @CR          char (  1),
           @HT          char (  1),
           @LF          char (  1),
           @chkcount    varchar (  7),
           @cmdstring   varchar (255),
           @namelen     tinyint,
           @sequence    tinyint,
           @space1      char (  1),
           @space3      char (  3),
           @tabcount    varchar (  7),
           @tableid     int,
           @textlen     tinyint

   select @CR     = char (13),
          @HT     = char (9),
          @LF     = char (10),
          @space1 = space (1),
          @space3 = space (3)

   create table #checks
      (id         int            not null,
       uid        smallint       not null,
       colid      smallint       not null,
       colname    varchar ( 30)  not null,
       prntname   varchar ( 63)  not null,
       seq        tinyint        not null,
       chktext    varchar (255)  null,
       sorttext   varchar (255)  not null)

   create table #checks2
      (id         int            not null,
       colid      smallint       not null,
       prntname   varchar ( 63)  not null,
       seq        tinyint        not null,
       seq2       tinyint        not null,
       chktext    char ( 78)     not null,
       sorttext   varchar (255)  not null)

   select @tableid = id
   from sysobjects
   where id              = object_id (@TABLE)
     and (sysstat & 15) in (1, 3)

   if ((@TABLE IS NULL)  or (@tableid IS NOT NULL))
      begin
         insert into #checks            -- "Column level" Column checks
            select o.id,
                   o.uid,
                   c.colid,
                   col_name (c.tableid, c.colid),
                   object_name (c.tableid),
                   1,
                   substring (cm.text, 1, char_length (cm.text) - 1),
                   @space1
            from sysconstraints c,
                 syscomments cm,
                 sysobjects o
            where c.tableid         = isnull (@tableid, c.tableid)
              and (c.status & 128)  = 128
              and c.colid          <> 0
              and o.id              = c.tableid
              and cm.id             = c.constrid
              and ((cm.text like 'CHECK %')  or  (cm.text like 'CONSTRAINT %'))

         insert into #checks            -- "Table level" Column checks
            select o.id,
                   o.uid,
                   256,
                   '-',
                   object_name (c.tableid),
                   1,
                   substring (cm.text, 1, char_length (cm.text) - 1),
                   @space1
            from sysconstraints c,
                 syscomments cm,
                 sysobjects o
            where c.tableid        = isnull (@tableid, c.tableid)
              and (c.status & 128) = 128
              and c.colid          = 0
              and o.id             = c.tableid
              and c.constrid       = cm.id
              and ((cm.text like 'CHECK %')  or  (cm.text like 'CONSTRAINT %'))
      end

   if (exists (select *
               from #checks))
      begin
         if (@TABLE IS NULL)
            begin
               update #checks
               set prntname = substring (c.prntname + ' (' + user_name (c.uid) + ')', 1, 30)
               from #checks c
               where c.uid <> 1
                  or 1      < (select count (o.uid)
                               from sysobjects o
                               where o.name            = c.prntname
                                 and (o.sysstat & 15) in (1, 3))
            end

         update #checks
         set chktext = substring (chktext, charindex ('(', chktext) + 1, 255)

         while (exists (select *
                        from #checks
                        where charindex (@CR, chktext) > 0))
            begin
               update #checks
               set chktext = substring (chktext, 1, charindex (@CR, chktext) - 1)
                             +  @space1
                             +  ltrim (substring (chktext, charindex (@CR, chktext) + 1, 255))
               where charindex (@CR, chktext) > 0
            end

         while (exists (select *
                        from #checks
                        where charindex (@HT, chktext) > 0))
            begin
               update #checks
               set chktext = substring (chktext, 1, charindex (@HT, chktext) - 1)
                             +  @space1
                             +  ltrim (substring (chktext, charindex (@HT, chktext) + 1, 255))
               where charindex (@HT, chktext) > 0
            end

         while (exists (select *
                        from #checks
                        where charindex (@LF, chktext) > 0))
            begin
               update #checks
               set chktext = substring (chktext, 1, charindex (@LF, chktext) - 1)
                             +  @space1
                             +  ltrim (substring (chktext, charindex (@LF, chktext) + 1, 255))
               where charindex (@LF, chktext) > 0
            end

         if (@TABLE IS NOT NULL)
            begin
               update #checks
               set prntname = colname
            end

         select @namelen = max (char_length (prntname))
         from #checks

         select @sequence = 2,
                @textlen  = 76 - @namelen

         insert into #checks2
            select id,
                   colid,
                   prntname,
                   seq,
                   1,
                   substring (chktext, 1, @textlen),
                   prntname
            from #checks
            where chktext IS NOT NULL

         update #checks
         set chktext = ltrim (substring (chktext, @textlen + 1, 255))

         while (exists (select *
                        from #checks
                        where char_length (chktext) > 3))
            begin
               insert into #checks2
                  select id,
                         colid,
                         prntname,
                         seq,
                         @sequence,
                         @space3  +  substring (chktext, 1, @textlen - 3),
                         prntname
                  from #checks
                  where substring (chktext, 1, @textlen - 3) IS NOT NULL

               update #checks
               set chktext = ltrim (substring (chktext, @textlen - 2, 255))

               select @sequence = @sequence + 1
            end

         if (@TABLE IS NULL)
            begin
               if (exists (select *
                           from #checks
                           where seq > 1))
                  begin
                     select @chkcount = ltrim (substring (convert (char (10), convert (money, count (*)), 1), 1, 7))
                     from #checks
                     where seq = 1

                     select @tabcount = ltrim (substring (convert (char (10), convert (money, count (distinct (id))), 1), 1, 7))
                     from #checks
                     where seq = 1

                     print '    THERE ARE %1! CHECK CONSTRAINTS ON %2! TABLES.',
                           @chkcount, @tabcount

                     print @space1
                  end

               update #checks2
               set sorttext = substring (prntname
                                         +  ' '
                                         +  right ('00'  +  (convert (varchar (3), colid)), 3)
                                         +  ' '
                                         +  right ('00'  +  (convert (varchar (3), seq2)), 3)
                                         +  ' '
                                         +  substring (chktext, 1, 50),
                                         1,
                                         255)

               update #checks2
               set prntname = @space1
               from #checks2 c2a
               where exists (select *
                             from #checks2 c2b
                             where c2b.id       = c2a.id
                               and c2b.colid    = c2a.colid
                               and c2b.seq      = c2a.seq
                               and c2b.seq2     = c2a.seq2
                               and c2b.sorttext = c2a.sorttext
                               and c2b.sorttext > (select min (sorttext)
                                                   from #checks2 c2c
                                                   where c2c.id = c2b.id))

               select @cmdstring = 'select ''TABLE'' = substring (prntname, 1,'
                                   +  convert (varchar (2), @namelen)
                                   +  '),'
                                   +  '''CHECK'' = substring (chktext, 1,'
                                   +  convert (varchar (2), @textlen)
                                   +  ')'
                                   +  ' from #checks2'
                                   +  ' order by sorttext, seq2'

               execute  (@cmdstring)
            end
         else
            begin
               update #checks2
               set prntname = ' '
               where seq2 <> 1

               select @cmdstring = 'select ''COLUMN'' = substring (prntname, 1,'
                                   +  convert (varchar (2), @namelen)
                                   +  '),'
                                   +  '''CHECK'' = substring (chktext, 1,'
                                   +  convert (varchar (2), @textlen)
                                   +  ')'
                                   +  ' from #checks2'
                                   +  ' order by colid, seq2'

               execute  (@cmdstring)
            end
      end
   else
      begin
         if (@TABLE IS NULL)
            begin
               print '    THERE ARE NO CHECK CONSTRAINTS.'
            end
         else if (@tableid IS NULL)
            begin
               print '    TABLE %1! DOES NOT EXIST.', @TABLE
            end
         else
            begin
               print '    THERE ARE NO CHECK CONSTRAINTS ON %1!.', @TABLE
            end
      end

   print @space1
go
sp_addcomment sp_checks, 'Lists check constraints for TABLE or (default) all tables'
go
sp_procxmode sp_checks, anymode
go

create procedure sp_defaults as
   set ansinull                      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_defaults CANNOT BE RUN FROM WITHIN A TRANSACTION.'

         print ' '

         return 1
      end

   declare @CR         char (1),
           @LF         char (1),
           @LFas       char (4),
           @LFasLF     char (4),
           @as         char (4),
           @asLF       char (4),
           @cmdstring  varchar (255),
           @dfltlen    tinyint,
           @dscrplen   tinyint,
           @ownerlen   tinyint

   select @CR = char (13),                            -- Carriage Return
          @LF = char (10)                             -- Line Feed

   select @as     = ' AS ',
          @asLF   = ' AS' + @LF,
          @LFas   = @LF + 'AS ',
          @LFasLF = @LF + 'AS' + @LF

   create table #defaults
      (dflt      varchar (30)  not null,
       dscrp     varchar (60)  null,
       owner     varchar (30)  not null,
       created   char ( 8)     not null)

   if (exists (select o.*
               from sysobjects o,
                    sysprocedures p
               where (o.sysstat & 15)   = 6
                 and o.id               = p.id
                 and p.sequence         = 1
                 and (p.status & 4096) <> 4096))
      begin
         insert into #defaults
            select o.name,
                   substring (c.text, 1, 60),
                   isnull (user_name (o.uid), '?'),
                   convert (char (8), o.crdate, 11)
            from syscomments c,
                 sysobjects o,
                 sysprocedures p
            where (o.sysstat & 15)   = 6
              and o.id               = p.id
              and p.sequence         = 1
              and (p.status & 4096) <> 4096
              and o.id               = c.id
              and c.colid            = 1
              and c.texttype         = 0

         update #defaults
         set dscrp = substring (dscrp,
                                4 + charindex (@as, upper (dscrp)),
                                datalength (dscrp) - 3 - charindex (@as, upper (dscrp)))
         where charindex (@as, upper (dscrp)) > 0

         update #defaults
         set dscrp = substring (dscrp,
                                4 + charindex (@asLF, upper (dscrp)),
                                datalength (dscrp) - 3 - charindex (@asLF, upper (dscrp)))
         where charindex (@asLF, upper (dscrp)) > 0

         update #defaults
         set dscrp = substring (dscrp,
                                4 + charindex (@LFas, upper (dscrp)),
                                datalength (dscrp) - 3 - charindex (@LFas, upper (dscrp)))
         where charindex (@LFas, upper (dscrp)) > 0

         update #defaults
         set dscrp = substring (dscrp,
                                4 + charindex (@LFasLF, upper (dscrp)),
                                datalength (dscrp) - 3 - charindex (@LFasLF, upper (dscrp)))
         where charindex (@LFasLF, upper (dscrp)) > 0

         update #defaults
         set dscrp = ltrim (dscrp)

         update #defaults
         set dscrp = substring (dscrp, 1, charindex (@CR, dscrp) - 1)
                     + ' '
                     + substring (dscrp, charindex (@CR, dscrp) + 1, 40)
         where charindex (@CR, dscrp) > 1

         update #defaults
         set dscrp = substring (dscrp, 1, charindex (@LF, dscrp) - 1)
         where charindex (@LF, dscrp) > 1

         select @dfltlen  = max (char_length (dflt)),
                @dscrplen = max (char_length (dscrp)),
                @ownerlen = max (char_length (owner))
         from #defaults

         if (@dfltlen < 7)
            begin
               select @dfltlen = 7
            end

         if (@dscrplen < 11)
            begin
               select @dscrplen = 11
            end

         if (@ownerlen < 5)
            begin
               select @ownerlen = 5
            end

         if ((@dfltlen + @dscrplen + @ownerlen) > 66)
            begin
               while ((@dfltlen + @dscrplen + @ownerlen) > 66)
                  begin
                     if (@dscrplen > 11)
                        begin
                           select @dscrplen = @dscrplen - 1
                        end

                     if (@ownerlen > 5)
                        begin
                           select @ownerlen = @ownerlen - 1
                        end
                  end

               select @dscrplen = 66 - @dfltlen - @ownerlen
            end

         select @cmdstring = 'select ''DEFAULT'' = substring (dflt, 1,'
                             +  convert (varchar (2), @dfltlen)
                             +  '),'
                             +  'DESCRIPTION = substring (dscrp, 1,'
                             +  convert (varchar (2), @dscrplen)
                             +  '),'
                             +  'OWNER = substring (owner, 1,'
                             +  convert (varchar (2), @ownerlen)
                             +  '),'
                             +  'CREATED = created'
                             +  ' from #defaults'
                             +  ' order by dflt, owner'

         execute  (@cmdstring)
      end
   else
      begin
         print '    THERE ARE NO USER-NAMED DEFAULTS.'

         print ' '
      end
go
sp_addcomment sp_defaults, 'Lists all user-named defaults with their description'
go
sp_procxmode sp_defaults, anymode
go


------------ Temporary table needed for creation of sp_procs_fillprintname ---

create table #procs
  (id          int           not null,
   uid         int           not null,
   createdt    datetime      not null,
   mode        tinyint       not null,
   recompile   tinyint       not null,
   xp          tinyint       not null,
   procname    varchar (32)  not null,
   printname   varchar (82)  not null,
   parameters  varchar (51)  not null)
go

create procedure sp_procs_fillprintname as
   set ansinull                      on
   set flushmessage                  on
   set string_rtruncation            on

   --  'any', 'chained', AND 'unchained' ARE MUTUALLY EXCLUSIVE.
   --  'recompile' AND 'xp' ARE MUTUALLY EXCLUSIVE.
   --  'unchained' IS THE DEFAULT INTERPRETATION HENCE IS NOT FILLED IN.

   update #procs
   set printname = substring (printname + ' [any]', 1, 82)
   where mode      = 2
     and recompile = 0
     and xp        = 0

   update #procs
   set printname = substring (printname + ' [any, recompile]', 1, 82)
   where mode      = 2
     and recompile = 1

   update #procs
   set printname = substring (printname + ' [any, xp]', 1, 82)
   where mode = 2
     and xp   = 1

   update #procs
   set printname = substring (printname + ' [chained]', 1, 82)
   where mode      = 1
     and recompile = 0
     and xp        = 0

   update #procs
   set printname = substring (printname + ' [chained, recompile]', 1, 82)
   where mode      = 1
     and recompile = 1

   update #procs
   set printname = substring (printname + ' [chained, xp]', 1, 82)
   where mode = 1
     and xp   = 1

   update #procs
   set printname = substring (printname + ' [recompile]', 1, 82)
   where mode      = 0
     and recompile = 1

   update #procs
   set printname = substring (printname + ' [xp]', 1, 82)
   where mode = 0
     and xp   = 1
go
sp_existsprocedure sp_procs_fillprintname
go

drop table #procs
go

create procedure sp_procs (@PREFIX varchar (30) = NULL) as
   set ansinull                      on
   set flushmessage                  on
   set string_rtruncation            on

   --   NOTE THAT IF "any", "chained", ETC. ARE SPECIFIED THEN THAT TEXT
   --   SHOULD NOT BE IN THE "[...]" SUFFIX OF THE PROCEDURE NAME.

   print ' '

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

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

         print ' '

         return 1
      end

   declare @cmdstring   varchar (255),
           @continue    smallint,
           @format_lks  varchar ( 11),
           @format_pgs  varchar ( 11),
           @i           tinyint,
           @likestring  varchar ( 30),
           @parmlen     tinyint,
           @proclen     tinyint,
           @procpgs     int,
           @syslocks    int

   create table #procs
     (id          int           not null,
      uid         int           not null,
      createdt    datetime      not null,
      mode        tinyint       not null,
      recompile   tinyint       not null,
      xp          tinyint       not null,
      procname    varchar (32)  not null,
      printname   varchar (82)  not null,
      parameters  varchar (51)  not null)

   select @continue = 0,
          @procpgs  = used_pgs (id, doampg, ioampg)
   from sysindexes
   where id = object_id ('sysprocedures')

   select @syslocks = value
   from master.dbo.syscurconfigs
   where config = 106

   if ((@@isolation > 0)  and  ((10 * @syslocks) < (12 * @procpgs)))
      begin
         select @format_lks = ltrim (substring (convert (char (14), convert (money, @syslocks), 1), 1, 11)),
                @format_pgs = ltrim (substring (convert (char (14), convert (money, @procpgs), 1), 1, 11))

         print '    THERE ARE %1! SYSTEM LOCKS AND %2! SYSPROCEDURES PAGES.',
               @format_lks, @format_pgs

         print ' '

         print '    TRANSACTION ISOLATION LEVEL 0 WILL BE USED.'

         print ' '

         set transaction isolation level 0
      end


   if (@PREFIX IS NULL)
      begin
         select @likestring = '%'
      end
   else if (upper (@PREFIX) in ('ANY', 'CHAINED', 'DATE', 'RECOMPILE',
                                'UNCHAINED', 'XP'))
      begin
         select @likestring = '%'
      end
   else
      begin
         execute  sp_like_string  @INPUT_STRING=@PREFIX,
                                  @LIKE_STRING=@likestring  output
      end

   if (exists (select *
               from sysobjects
               where (sysstat & 15) = 4))
      begin
         insert into #procs
            select o.id, o.uid, o.crdate, 0, 0, 0, o.name, o.name,
                   substring (substring (cl1.name, 2, 29)
                              + ', ' + substring (cl2.name, 2, 29)
                              + ', ' + substring (cl3.name, 2, 29)
                              + ', ' + substring (cl4.name, 2, 29)
                              + ', ' + substring (cl5.name, 2, 29)
                              + ', ' + substring (cl6.name, 2, 29),
                              1,
                              51)
            from sysobjects o  left join  syscolumns cl1
              on o.id      = cl1.id
             and cl1.colid = 1
            left join  syscolumns cl2
              on o.id      = cl2.id
             and cl2.colid = 2
            left join  syscolumns cl3
              on o.id      = cl3.id
             and cl3.colid = 3
            left join  syscolumns cl4
              on o.id      = cl4.id
             and cl4.colid = 4
            left join  syscolumns cl5
              on o.id      = cl5.id
             and cl5.colid = 5
            left join  syscolumns cl6
              on o.id      = cl6.id
             and cl6.colid = 6
            where (o.sysstat & 15)    = 4
              and o.name           like @likestring

         if (@PREFIX IS NULL)
            begin
               execute  sp_exclude_object  @TYPE='EXP',
                                           @TABLENAME='#procs',
                                           @COLUMNNAME='procname'
            end

         update #procs
         set parameters = ' '
         where parameters like ',%'

         update #procs
         set parameters = substring (parameters + ', ...', 1, 51)
         from syscolumns c,
              #procs p
         where c.id    = p.id
           and c.colid = 6

         select @i = 1

         while (@i < 6)
            begin
               update #procs
               set parameters = rtrim (substring (parameters, 1, char_length (parameters) - 1))
               where right (parameters, 1) = ','

               select @i = @i + 1
            end

         update #procs
         set printname = p.printname + ' (' + user_name (p.uid) + ')'
         from #procs p
         where p.uid <> 1
            or 1      < (select count (p2.uid)
                         from #procs p2
                         where p2.procname = p.procname)

         update #procs
         set mode = 1
         from sysobjects o,
              #procs p
         where p.id              = o.id
           and (o.sysstat2 & 16) = 16

         update #procs
         set mode = 2
         from sysobjects o,
              #procs p
         where p.id              = o.id
           and (o.sysstat2 & 32) = 32

         update #procs
         set recompile = 1
         from #procs p,
              sysprocedures s
         where s.id            = p.id
           and s.sequence      = 1
           and (s.status & 64) = 64

         update #procs
         set xp = 1
         from #procs p,
              sysobjects o
         where o.id   = p.id
           and o.type = 'XP'

         delete from #procs
         where upper (@PREFIX)  = 'ANY'
           and mode            <> 2

         delete from #procs
         where upper (@PREFIX)  = 'CHAINED'
           and mode            <> 1

         delete from #procs
         where upper (@PREFIX)  = 'UNCHAINED'
           and mode            <> 0

         delete from #procs
         where upper (@PREFIX)  = 'RECOMPILE'
           and recompile       <> 1

         delete from #procs
         where upper (@PREFIX)  = 'XP'
           and xp              <> 1

         select @continue = count (*)
         from #procs

         if (upper (@PREFIX) in ('ANY', 'CHAINED', 'UNCHAINED'))
            begin
               if (@continue > 0)
                  begin
                     update #procs
                     set printname = substring (printname + ' [recompile]', 1, 82)
                     where recompile = 1
                       and xp        = 0

                     update #procs
                     set printname = substring (printname + ' [xp]', 1, 82)
                     where recompile = 0
                       and xp        = 1
                  end
               else if (upper (@PREFIX) = 'ANY')
                  begin
                     print '    THERE ARE NO STORED PROCEDURES THAT USE ANYMODE.'

                     print ' '
                  end
               else if (upper (@PREFIX) = 'CHAINED')
                  begin
                     print '    THERE ARE NO STORED PROCEDURES THAT USE CHAINED MODE.'

                     print ' '
                  end
               else
                  begin
                     print '    THERE ARE NO STORED PROCEDURES THAT USE UNCHAINED MODE.'

                     print ' '
                  end
            end
         else if (upper (@PREFIX) = 'DATE')
            begin
               if (@continue > 0)
                  begin
                     execute  sp_procs_fillprintname

                     select @parmlen = max (char_length (parameters)),
                            @proclen = max (char_length (printname))
                     from #procs

                     if (@proclen > 51)
                        begin
                           select @proclen = 51
                        end
                     else if (@proclen < 9)
                        begin
                           select @proclen = 9
                        end

                     if ((@parmlen + @proclen) > 61)
                        begin
                           select @parmlen = 61 - @proclen
                        end

                     select @cmdstring = 'select ''DATE/TIME'' = convert (char (8), createdt, 11)'
                                         +  '+ '' '''
                                         +  '+ substring (convert (char (8), createdt, 8), 1, 5),'
                                         +  '''PROCEDURE'' = substring (printname, 1,'
                                         +  convert (varchar (2), @proclen)
                                         +  '),'
                                         +  '''PARAMETERS'' = substring (parameters, 1,'
                                         +  convert (varchar (2), @parmlen)
                                         +  ')'
                                         +  ' from #procs'
                                         +  ' order by createdt, printname'

                     execute  (@cmdstring)

                     select @continue = 0
                  end
               else
                  begin
                     print '    THERE ARE NO STORED PROCEDURES.'

                     print ' '
                  end
            end
         else if (upper (@PREFIX) in ('RECOMPILE', 'XP'))
            begin
               if (@continue > 0)
                  begin
                     update #procs
                     set printname = substring (printname + ' [any]', 1, 82)
                     where mode = 2

                     update #procs
                     set printname = substring (printname + ' [chained]', 1, 82)
                     where mode = 1
                  end
               else if (upper (@PREFIX) = 'RECOMPILE')
                  begin
                     print '    THERE ARE NO STORED PROCEDURES WITH THE RECOMPILE OPTION.'

                     print ' '
                  end
               else
                  begin
                     print '    THERE ARE NO EXTENDED STORED PROCEDURES.'

                     print ' '
                  end
            end
         else
            begin
               if (@continue > 0)
                  begin
                     if (@PREFIX IS NULL)
                        begin
                           print '    ALL STORED PROCEDURES WILL BE LISTED.'
                        end
                     else
                        begin
                           print '    THERE ARE STORED PROCEDURES LIKE %1!.',
                                 @likestring
                        end

                     print ' '

                     execute  sp_procs_fillprintname
                  end
               else
                  begin
                     print '    THERE ARE NO STORED PROCEDURES LIKE %1!.', @likestring

                     print ' '
                  end
            end

         if (@continue > 0)
            begin
               select @parmlen = max (char_length (parameters)),
                      @proclen = max (char_length (printname))
               from #procs

               if (@proclen > 57)
                  begin
                     select @proclen = 57
                  end
               else if (@proclen < 9)
                  begin
                     select @proclen = 9
                  end

               if ((@parmlen + @proclen) > 67)
                  begin
                     select @parmlen = 67 - @proclen
                  end

               select @cmdstring = 'select ''DATE'' = convert (char (8), createdt, 11),'
                                   +  '''PROCEDURE'' = substring (printname, 1,'
                                   +  convert (varchar (2), @proclen)
                                   +  '),'
                                   +  '''PARAMETERS'' = substring (parameters, 1,'
                                   +  convert (varchar (2), @parmlen)
                                   +  ')'
                                   +  ' from #procs'
                                   +  ' order by printname'

               execute  (@cmdstring)
            end
      end
   else
      begin
         print '    THERE ARE NO STORED PROCEDURES.'

         print ' '
      end
go
sp_addcomment sp_procs, 'Lists stored procedures with various scope/sort options:'
go
sp_addcomment sp_procs, '   (NULL):      all stored procedures (default)'
go
sp_addcomment sp_procs, '   "ANY":       only anymode stored procedures'
go
sp_addcomment sp_procs, '   "CHAINED":   only chained mode stored procedures'
go
sp_addcomment sp_procs, '   "DATE":      all stored procedures in date order'
go
sp_addcomment sp_procs, '   PREFIX:      only stored procedures like PREFIX%'
go
sp_addcomment sp_procs, '   "RECOMPILE": only stored procedures with recompile option'
go
sp_addcomment sp_procs, '   "UNCHAINED": only unchained mode stored procedures'
go
sp_addcomment sp_procs, '   "XP":        only extended stored procedures'
go
sp_procxmode sp_procs, anymode
go

create procedure sp_rules_all (@DATE_FLAG  char (1)) as
   set ansinull                      on
   set string_rtruncation            on

   declare @CR         char (1),
           @HT         char (1),
           @LF         char (1),
           @NUL        char (1),
           @atsymbol   char (1),
           @cmdstring  varchar (255),
           @datelen    tinyint,
           @rulelen    tinyint,
           @seq        tinyint,
           @textlen    smallint

   select @CR       = char (13),                      -- Carriage Return
          @HT       = char (9),                       -- Horizontal Tab
          @LF       = char (10),                      -- Line Feed
          @NUL      = char (0),                       -- NUL (!)
          @atsymbol = char (64),                      -- '@'
          @datelen  = 15,
          @seq      = 2

   create table #rules
      (rname     varchar ( 62)  not null,
       uid       int            not null,
       crdate    datetime       not null,
       dateprnt  char (14)      not null,
       colid     tinyint        not null,
       ruletext  varchar (255)  null)

   create table #rules2
      (rname     varchar ( 62)  not null,
       crdate    datetime       not null,
       dateprnt  char (14)      not null,
       colid     tinyint        not null,
       seq       tinyint        not null,
       ruletext  varchar (255)  null,
       sort      varchar (255)  not null)

   insert into #rules
      select o.name, o.uid, o.crdate, ' ', c.colid, c.text
      from syscomments c,
           sysobjects o,
           sysprocedures p
      where (o.sysstat & 15)   = 7
        and o.id               = p.id
        and p.sequence         = 1
        and (p.status & 4096) <> 4096
        and o.id               = c.id
        and c.texttype         = 0

   update #rules
   set rname = substring (r.rname + ' (' + user_name (r.uid) + ')', 1, 62)
   from #rules r
   where r.uid <> 1
      or 1      < (select count (o.uid)
                   from sysobjects o,
                        sysprocedures p
                   where o.name             = r.rname
                     and (o.sysstat & 15)   = 7
                     and o.id               = p.id
                     and p.sequence         = 1
                     and (p.status & 4096) <> 4096)

   update #rules
   set ruletext = substring (ruletext, charindex ('(@', ruletext), 255)
   where colid                     = 1
     and charindex ('(@', ruletext) > 0

   update #rules
   set ruletext = substring (ruletext, charindex (@atsymbol, ruletext), 255)
   where charindex ('(@', ruletext) = 0
     and charindex (@atsymbol, ruletext)  > 0

   while (exists (select *
                  from #rules
                  where charindex (@CR, ruletext) > 0))
      begin
         update #rules
         set ruletext = stuff (ruletext, charindex (@CR, ruletext), 1, ' ')
         where charindex (@CR, ruletext) > 0
      end

   while (exists (select *
                  from #rules
                  where charindex (@HT, ruletext) > 0))
      begin
         update #rules
         set ruletext = stuff (ruletext, charindex (@HT, ruletext), 1, ' ')
         where charindex (@HT, ruletext) > 0
      end

   while (exists (select *
                  from #rules
                  where charindex (@LF, ruletext) > 0))
      begin
         update #rules
         set ruletext = stuff (ruletext, charindex (@LF, ruletext), 1, ' ')
         where charindex (@LF, ruletext) > 0
      end

   update #rules
   set ruletext = ltrim (ruletext)

   delete from #rules
   where ruletext IS NULL
      or ruletext  = ' '

   if (@DATE_FLAG IS NULL)
      begin
         select @datelen = 0
      end

   select @rulelen = max (char_length (rname)),
          @textlen = max (char_length (ruletext))
   from #rules

   if ((@datelen + @rulelen + @textlen) > 76)
      begin
         if (@datelen + @rulelen > 50)
            begin
               select @rulelen = 50 - @datelen
            end

         select @textlen = 76 - @datelen - @rulelen
      end

   insert into #rules2
      select rname, crdate, ' ', colid, 1,
             substring (ruletext, 1, @textlen),
             ' '
      from #rules
      where ruletext IS NOT NULL

   update #rules
   set ruletext = ltrim (substring (ruletext, @textlen + 1, 255))

   while (exists (select *
                  from #rules
                  where ruletext IS NOT NULL))
      begin
         insert into #rules2
            select rname, crdate, ' ', colid, @seq,
                   substring (ruletext, 1, @textlen),
                   ' '
            from #rules
            where ruletext IS NOT NULL
              and ruletext     <> @NUL

         update #rules
         set ruletext = ltrim (substring (ruletext, @textlen + 1, 255))

         select @seq = @seq + 1
      end

   if (@DATE_FLAG IS NULL)
      begin
         update #rules2
         set sort = rname
                    +  ' '
                    +  right ('00'  +  (convert (varchar (3), colid)), 3)
                    +  ' '
                    +  right ('00'  +  (convert (varchar (3), seq)), 3)

         select @cmdstring = 'select ''RULE'' = substring (rname, 1,'
      end
   else
      begin
         update #rules2
         set dateprnt = convert (char (8), crdate, 11)
                        +  ' '
                        +  convert (char (5), crdate, 8),
             sort     = convert (char (10), crdate, 102)
                        +  ' '
                        +  convert (char (8), crdate, 108)
                        +  ' '
                        +  rname
                        +  ' '
                        +  right ('00'  +  (convert (varchar (3), colid)), 3)
                        +  ' '
                        +  right ('00'  +  (convert (varchar (3), seq)), 3)

         select @cmdstring = 'select CREATED = dateprnt,'
                             +  '''RULE'' = substring (rname, 1,'
      end

   update #rules2
   set rname    = ' ',
       dateprnt = ' '
   where colid <> 1
      or seq   <> 1

  select @cmdstring = @cmdstring
                      +  convert (varchar (2), @rulelen)
                      +  '),'
                      +  'DESCRIPTION = substring (ruletext, 1,'
                      +  convert (varchar (3), @textlen)
                      +  ')'
                      +  ' from #rules2'
                      +  ' order by sort'

  execute (@cmdstring)
go
sp_existsprocedure sp_rules_all
go

create procedure sp_rules (@TABLE varchar (63) = NULL) as
   set ansinull                      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_rules CANNOT BE RUN FROM WITHIN A TRANSACTION.'

         print ' '

         return 1
      end

   declare @dbname     varchar (30),
           @tableid    int

   if ((@TABLE IS NULL)  or  (upper (@TABLE) = 'DATE'))
      begin
         if (exists (select o.*
                     from sysobjects o,
                          sysprocedures p
                     where (o.sysstat & 15)   = 7
                       and o.id               = p.id
                       and p.sequence         = 1
                       and (p.status & 4096) <> 4096))
            begin
               if (@TABLE IS NULL)
                  begin
                     execute  sp_rules_all  @DATE_FLAG=NULL
                  end
               else
                  begin
                     execute  sp_rules_all  @DATE_FLAG='X'
                  end
            end
         else
            begin
               select @dbname = ltrim (db_name ())

               print '    THERE ARE NO RULES IN DATABASE %1!.', @dbname

               print ' '
            end
      end
   else
      begin
         select @tableid = id
         from sysobjects
         where id              = object_id (@TABLE)
           and (sysstat & 15) in (1, 3)

         if (@tableid IS NULL)
            begin
               print '    TABLE %1! DOES NOT EXIST.', @TABLE

               print ' '
            end
         else if (exists (select c.*
                          from syscolumns c,
                               sysprocedures p
                          where c.id               = @tableid
                            and c.domain           = p.id
                            and p.sequence         = 1
                            and (p.status & 4096) <> 4096))
            begin
               select 'COLUMN' = col_name (@tableid, c.colid),
                      'RULE'   = object_name (c.domain)
               from syscolumns c,
                    sysprocedures p
               where c.id               = @tableid
                 and c.domain           = p.id
                 and p.sequence         = 1
                 and (p.status & 4096) <> 4096
               order by c.colid
            end
         else
            begin
               print '    THERE ARE NO RULES ON TABLE %1!.', @TABLE

               print ' '
            end
      end
go
sp_addcomment sp_rules, 'Lists rules with various scope/sort options:'
go
sp_addcomment sp_rules, '   (NULL): all rules in name order (default)'
go
sp_addcomment sp_rules, '   "DATE": all rules in date order'
go
sp_addcomment sp_rules, '   TABLE:  only rules for columns in TABLE'
go
sp_procxmode sp_rules, anymode
go

create procedure sp_showtable_detail (@OBJECTID int) as
   set ansinull                      on
   set flushmessage                  on
   set string_rtruncation            on

   declare @CR          char (  1),
           @HT          char (  1),
           @LF          char (  1),
           @chkrullen   tinyint,
           @cmdstring   varchar (255),
           @collen      tinyint,
           @dfltlen     tinyint,
           @dfltmssg    char (  6),
           @nullflag    tinyint,
           @objectname  varchar ( 30),
           @typelen     tinyint

   create table #showtbldetail
     (column    varchar (30)  not null,
      ident     tinyint       not null,
      usertype  smallint      not null,
      stype     tinyint       not null,
      len       tinyint       not null,
      prec      tinyint       null,
      scale     tinyint       null,
      typetext  varchar (18)  not null,
      cdflt     int           not null,
      dflt      varchar (60)  null,
      nulltype  int           not null,
      cr        int           not null,
      crtype    char (1)      not null,
      crtext    varchar (255) null,
      colid     smallint      not null,
      seq       tinyint       not null)

   select @CR          = char ( 13),                  -- Carriage Return
          @HT          = char (  9),                  -- Horizontal Tab
          @LF          = char ( 10),                  -- Line Feed
          @chkrullen   = 0,
          @collen      = 0,
          @dfltlen     = 0,
          @nullflag    = 0,
          @objectname  = object_name (@OBJECTID),
          @typelen     = 0

   if (exists (select *
               from master.dbo.sysdatabases
               where dbid            = db_id ()
                 and (status & 8192) = 8192))
      begin
         select @dfltmssg = '(Null)'                  -- ANSI db assumed
      end
   else
      begin
         select @dfltmssg = '-'
      end

   insert into #showtbldetail
      select c.name,
             convert (tinyint, convert (bit, (c.status & 128))),
             c.usertype,
             c.type,
             c.length,
             c.prec,
             c.scale,
             t.name,
             c.cdefault,
             @dfltmssg,
             convert (int, convert (bit, (c.status & 8))),
             c.domain,
             'C',
             NULL,
             c.colid,
             0
      from syscolumns c,
           systypes t
      where c.id       = @OBJECTID
        and c.usertype = t.usertype

   update #showtbldetail
   set len = len / @@ncharsize
   where ((usertype = 24)  and  (stype = 47))
      or ((usertype = 25)  and  (stype = 39))

   update #showtbldetail
   set typetext = typetext
                  + ' ('
                  + convert (varchar (3), len)
                  + ')'
   where usertype in (1, 2, 3, 4, 24, 25)

   update #showtbldetail
   set typetext = typetext
                  + ' ('
                  + convert (varchar (3), prec)
                  + ', '
                  + convert (varchar (3), scale)
                  + ')'
   where usertype in (10, 26)

   update #showtbldetail
   set typetext = 'float (' + convert (varchar (3), prec) + ')'
   where usertype in (8, 23)
     and prec     IS NOT NULL

   update #showtbldetail
   set typetext = 'real'
   where ((usertype = 23)  or  ((usertype = 8) and (len = 4)))
     and prec IS NULL

   update #showtbldetail
   set typetext = 'double precision'
   where usertype  = 8
     and len       = 8
     and prec     IS NULL

   update #showtbldetail
   set typetext = 'tinyint'
   where usertype in (5, 13)
     and len       = 1

   update #showtbldetail
   set typetext = 'smallint'
   where usertype in (6, 13)
     and len       = 2

   update #showtbldetail
   set typetext = 'int'
   where usertype in (7, 13)
     and len       = 4

   if (exists (select *
               from #showtbldetail
               where nulltype = 1))
      begin
         update #showtbldetail
         set typetext = typetext + ' *'
         where nulltype = 1

         select @nullflag = 1
      end

   update #showtbldetail
   set dflt = object_name (s.cdflt)
   from syscomments c,
        #showtbldetail s
   where s.cdflt    = c.id
     and c.colid    = 1
     and c.colid2   = 0
     and c.texttype = 0

   update #showtbldetail
   set dflt = ltrim (substring (c.text, 8, 60))
   from syscomments c,
        #showtbldetail s
   where s.cdflt    = c.id
     and c.colid    = 1
     and c.colid2   = 0
     and c.texttype = 0
     and object_name (s.cdflt) like
                      substring (@objectname, 1, 10)  +  '[_]'
                      +  substring (s.column, 1, 6)  +  '[_]%'

   while (exists (select *
                  from #showtbldetail
                  where charindex (@CR, dflt) > 0))
      begin
         update #showtbldetail
         set dflt = stuff (dflt, charindex (@CR, dflt), 1, ' ')
         where charindex (@CR, dflt) > 0
      end

   while (exists (select *
                  from #showtbldetail
                  where charindex (@HT, dflt) > 0))
      begin
         update #showtbldetail
         set dflt = stuff (dflt, charindex (@HT, dflt), 1, ' ')
         where charindex (@HT, dflt) > 0
      end

   while (exists (select *
                  from #showtbldetail
                  where charindex (@LF, dflt) > 0))
      begin
         update #showtbldetail
         set dflt = stuff (dflt, charindex (@LF, dflt), 1, ' ')
         where charindex (@LF, dflt) > 0
      end

   update #showtbldetail
   set dflt = '-'
   where dflt      = @dfltmssg
     and nulltype  = 0

   update #showtbldetail
   set dflt = ltrim (dflt)

   update #showtbldetail
   set dflt = '! ' + s.dflt
   from systypes t,
        #showtbldetail s
   where s.usertype  > 99
     and s.usertype  = t.usertype
     and s.cdflt    <> t.tdefault

   update #showtbldetail
   set dflt = '! ' + dflt + ' (Identity)'
   where ident  = 1
     and cdflt <> 0
     and dflt not like '!%'

   update #showtbldetail
   set dflt = '!' + dflt + ' (Identity)'
   where ident   = 1
     and cdflt  <> 0
     and dflt like '!%'

   update #showtbldetail
   set dflt = '(Identity)'
   where ident = 1
     and cdflt = 0

   if (exists (select *
               from #showtbldetail
               where ident  = 1
                 and cdflt <> 0))
      begin
         print '    WARNING: IDENTITY COLUMN HAS AN UNUSEABLE DEFAULT.'

         print ' '
      end

   update #showtbldetail
   set crtype = 'R'
   from sysprocedures p,
        #showtbldetail t
   where p.id               = t.cr
     and p.sequence         = 1
     and (p.status & 4096) <> 4096

   -- UPDATE RULE DATA DEFINED BY sp_bindrule FOR A TABLE.COLUMN.

   update #showtbldetail
   set cr     = t.domain,
       crtype = 'R',
       crtext = cm.text
   from syscomments cm,
        systypes t,
        #showtbldetail std
   where std.cr      = 0
     and t.usertype  = std.usertype
     and t.domain   <> 0
     and cm.id       = t.domain

   -- INSERT DEFINITION TEXT FOR RULES DEFINED VIA USERTYPES.

   insert into #showtbldetail
      select ' ', 0, 0, 0, 0, 0, 0, ' ', 0, ' ', 0, t.domain, 'R', cm.text, std.colid, 1
      from syscomments cm,
           systypes t,
           #showtbldetail std
      where std.cr     <> 0
        and std.crtype  = 'C'
        and t.usertype  = std.usertype
        and t.domain   <> 0
        and cm.id       = t.domain

   -- INSERT ROWS FOR COLUMNS WITH MULTIPLE CHECKS.

   insert into #showtbldetail
      select ' ', 0, 0, 0, 0, 0, 0, ' ', 0, ' ', 0, c.constrid, 'C', cm.text, c.colid, 1
      from sysconstraints c,
           syscomments cm
      where c.tableid         = @OBJECTID
        and (c.status & 128)  = 128
        and c.colid          <> 0
        and cm.id             = c.constrid
        and ((cm.text like 'CHECK %')  or  (cm.text like 'CONSTRAINT %'))
        and not exists (select *
                        from #showtbldetail st
                        where st.colid = c.colid
                          and st.cr    = c.constrid)

   -- INSERT TABLE LEVEL CHECKS.

   insert into #showtbldetail
      select '-', 0, 0, 0, 0, 0, 0, '-', 0, '-', 0, c.constrid, 'C', cm.text, 256, 0
      from sysconstraints c,
           syscomments cm
      where c.tableid        = @OBJECTID
        and (c.status & 128) = 128
        and c.colid          = 0
        and cm.id            = c.constrid
        and ((cm.text like 'CHECK %')  or  (cm.text like 'CONSTRAINT %'))

   if (exists (select *
               from #showtbldetail
               where ident = 0
                 and dflt  like '!%'))
      begin
         print '    WARNING: COLUMN AND USERTYPE DEFAULTS DIFFER.'

         print ' '
      end

   update #showtbldetail
   set crtext = c.text
   from syscomments c,
        #showtbldetail t
   where t.crtype   = 'C'
     and c.id       = t.cr
     and c.colid    = 1
     and c.colid2   = 0
     and c.texttype = 0

   update #showtbldetail
   set crtext = substring (crtext, 1, char_length (crtext) - 1)
   where crtype    = 'C'
     and crtext like '%CHECK%)'

   update #showtbldetail
   set crtext = substring (c.text, charindex ('@', c.text) - 1, 255)
   from syscomments c,
        #showtbldetail t
   where t.crtype                = 'R'
     and c.id                    = t.cr
     and c.texttype              = 0
     and charindex ('@', c.text) > 0

   update #showtbldetail
   set crtext = substring (crtext, 2, 255)
   where crtype                    = 'R'
     and substring (crtext, 1, 1) <> '('

   -- BEGINING TEXT VARIES DEPENDING UPON create VERSUS alter.  (Bug # 123888.)

   update #showtbldetail
   set crtext = substring (crtext, charindex ('(', upper (crtext)) + 1, 255)
   where upper (crtext) like '%CHECK%'

   -- ELIMINATE THE COLUMN NAME ONLY IF THE COLUMN IS PROPERLY REFERENCED.

-- update #showtbldetail
-- set crtext = ltrim (substring (crtext, char_length (column) + 1, 255))
-- where upper (crtext) like (upper (column)  +  ' %')

   while (exists (select *
                  from #showtbldetail
                  where charindex (@CR, crtext) > 0))
      begin
         update #showtbldetail
         set crtext = substring (crtext, 1, charindex (@CR, crtext) - 1)
                    + ' '
                    + substring (crtext, charindex (@CR, crtext) + 1, 255)
         where charindex (@CR, crtext) > 0
      end

   while (exists (select *
                  from #showtbldetail
                  where charindex (@HT, crtext) > 0))
      begin
         update #showtbldetail
         set crtext = substring (crtext, 1, charindex (@HT, crtext) - 1)
                    + ' '
                    + substring (crtext, charindex (@HT, crtext) + 1, 255)
         where charindex (@HT, crtext) > 0
      end

   while (exists (select *
                  from #showtbldetail
                  where charindex (@LF, crtext) > 0))
      begin
         update #showtbldetail
         set crtext = substring (crtext, 1, charindex (@LF, crtext) - 1)
                    + ' '
                    + substring (crtext, charindex (@LF, crtext) + 1, 255)
         where charindex (@LF, crtext) > 0
      end

   update #showtbldetail
   set crtext = '-'
   where crtext IS NULL

   select @chkrullen = max (char_length (crtext)),
          @collen    = max (char_length (column)),
          @dfltlen   = max (char_length (dflt)),
          @typelen   = max (char_length (typetext))
   from #showtbldetail

   if (@collen < 6)
      begin
         select @collen = 6
      end

   if (@typelen < 4)
      begin
         select @typelen = 4
      end

   if (@dfltlen < 7)
      begin
         select @dfltlen = 7
      end

   if (@chkrullen < 11)
      begin
         select @chkrullen = 11
      end

   while ((@collen + @typelen + @dfltlen + @chkrullen) > 74)
      begin
         if (@dfltlen > 7)
            begin
               select @dfltlen = @dfltlen - 1
            end

         if (@chkrullen > 11)
            begin
               select @chkrullen = @chkrullen - 1
            end
      end

   select @cmdstring = 'select COLUMN = substring (column, 1,'
                       +  convert (varchar (2), @collen)
                       +  '),'
                       +  'TYPE = substring (typetext, 1,'
                       +  convert (varchar (2), @typelen)
                       +  '),'
                       +  '''DEFAULT'' = substring (dflt, 1,'
                       +  convert (varchar (2), @dfltlen)
                       +  '),'
                       +  '''CHECK/RULE'' = substring (crtext, 1,'
                       +  convert (varchar (2), @chkrullen)
                       +  ')'
                       +  ' from #showtbldetail'
                       +  ' order by colid, seq'

   execute (@cmdstring)
go
sp_existsprocedure sp_showtable_detail
go

create procedure sp_showtable (@TABLE varchar (63)) as
   set ansinull                      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_showtable CANNOT BE RUN FROM WITHIN A TRANSACTION.'

         print ' '

         return 1
      end

   declare @dottable     varchar (30),
           @dotowner     varchar (30),
           @dotposition  tinyint,
           @namecnt      tinyint,
           @tableid      int

   select @tableid = id,
          @namecnt = 0
   from sysobjects
   where id              = object_id (@TABLE)
     and (sysstat & 15) in (1, 2, 3)

   if (@tableid IS NULL)
      begin
         select @namecnt = count (*)
         from sysobjects
         where name            = @TABLE
           and (sysstat & 15) in (1, 2, 3)

         if (@namecnt = 0)
            begin
               select @dotposition = charindex ('.', @TABLE)

               if (@dotposition > 0)
                  begin
                     select @dottable = substring (@TABLE, @dotposition + 1, 30),
                            @dotowner = substring (@TABLE, 1, @dotposition - 1)

                     select @tableid = id
                     from sysobjects
                     where name            = @dottable
                       and uid             = user_id (@dotowner)
                       and (sysstat & 15) in (1, 2, 3)
                  end
            end
         else if (@namecnt = 1)
            begin
               select @tableid = id
               from sysobjects
               where name            = @TABLE
                 and (sysstat & 15) in (1, 2, 3)
            end
         else
            begin
               print '    TABLE/VIEW %1! IS NOT UNIQUE.  QUALIFY WITH THE OWNER NAME.',
                     @TABLE

               print ' '
            end
      end

   if ((@tableid IS NULL)  and  (@namecnt < 2))
      begin
         print '    TABLE/VIEW %1! DOES NOT EXIST.', @TABLE

         print ' '
      end
   else
      begin
         if (exists (select *
                     from syscolumns
                     where id = @tableid))
            begin
               execute  sp_showtable_detail  @OBJECTID=@tableid
            end
         else
            begin
               print '    TABLE/VIEW %1! DOES NOT HAVE ANY COLUMNS!', @TABLE

               print ' '
            end
      end
go
sp_addcomment sp_showtable, 'Lists column names and types for TABLE/VIEW'
go
sp_procxmode sp_showtable, anymode
go

create procedure sp_showtables (@PREFIX varchar (30) = 'U') as
   set ansinull                      on
   set close on endtran              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_showtables CANNOT BE RUN FROM WITHIN A TRANSACTION.'

         print ' '

         return 1
      end

   declare @createdate   char ( 8),
           @errorchar    varchar ( 4),
           @likestring   varchar ( 31),
           @maxobjlen    tinyint,
           @objectname   varchar (60),
           @owner        varchar (30),
           @printmssg    varchar (70),
           @printname    varchar (63),
           @status       tinyint,
           @typetext     varchar (17),
           @uidcount     tinyint

   declare object_type_cursor cursor for
      select name, user_name (uid), convert (char (8), crdate, 11)
      from sysobjects
      where (sysstat & 15) = @status
      order by name, user_name (uid)
   for read only

   declare object_prefix_cursor cursor for
      select name, user_name (uid), convert (char (8), crdate, 11)
      from sysobjects
      where name           like @likestring
        and (sysstat & 15)   in (1, 2, 3)
      order by name, user_name (uid)
   for read only

   if (upper (@PREFIX) in ('S', 'U', 'V'))
      begin
         if (upper (@PREFIX) = 'S')
            begin
               select @typetext = 'S (System Tables)',
                      @status   = 1
            end
         else if (upper (@PREFIX) = 'U')
            begin
               select @typetext = 'U (User Tables)',
                      @status   = 3
            end
         else
            begin
               select @typetext = 'V (Views)',
                      @status   = 2
            end

         open object_type_cursor

         fetch object_type_cursor into @objectname, @owner, @createdate

         if (@@sqlstatus = 2)
            begin
               print '    THERE ARE NO OBJECTS OF TYPE %1!.', @typetext

               print ' '
            end
         else
            begin
               select @maxobjlen = 4 + max (datalength (name + user_name (uid)))
               from sysobjects
               where (sysstat & 15) = @status

               while (@@sqlstatus = 0)
                  begin
                     if (@owner = 'dbo')
                        begin
                           select @uidcount = count (uid)
                           from sysobjects
                           where name            = @objectname
                             and (sysstat & 15) in (1, 2, 3)

                           if (@uidcount = 1)
                              begin
                                 select @printname = @objectname
                              end
                           else
                              begin
                                 select @objectname = @owner + '.' + @objectname,
                                        @printname  = @objectname + ' (' + @owner + ')'
                              end
                        end
                     else
                        begin
                           select @objectname = @owner + '.' + @objectname,
                                  @printname  = @objectname + ' (' + @owner + ')'
                        end

                     select @printmssg = @printname
                                         + space (@maxobjlen - datalength (@printname))
                                         + '(' + @createdate + ')'

                     print ' '

                     print ' '

                     print '    %1!', @printmssg

                     execute  sp_showtable  @TABLE=@objectname

                     fetch object_type_cursor into @objectname, @owner, @createdate
                  end

               if (@@sqlstatus <> 2)
                  begin
                     select @errorchar = convert (varchar (4), @@sqlstatus)

                     print '  CURSOR ERROR.  STATUS = %1!.', @errorchar
                  end
            end

         close object_type_cursor

         deallocate cursor object_type_cursor
      end
   else
      begin
         execute  sp_like_string  @INPUT_STRING=@PREFIX,
                                  @LIKE_STRING=@likestring  output

         open object_prefix_cursor

         fetch object_prefix_cursor into @objectname, @owner, @createdate

         if (@@sqlstatus = 2)
            begin
               print '    THERE ARE NO TABLES/VIEWS LIKE %1!.', @likestring

               print ' '
            end
         else
            begin
               select @maxobjlen = 4 + max (datalength (name + user_name (uid)))
               from sysobjects
               where name           like @PREFIX + '%'
                 and (sysstat & 15)   in (1, 2, 3)

               while (@@sqlstatus = 0)
                  begin
                     if (@owner = 'dbo')
                        begin
                           select @uidcount = count (uid)
                           from sysobjects
                           where name            = @objectname
                             and (sysstat & 15) in (1, 2, 3)

                           if (@uidcount = 1)
                              begin
                                 select @printname = @objectname
                              end
                           else
                              begin
                                 select @objectname = @owner + '.' + @objectname,
                                        @printname  = @objectname + ' (' + @owner + ')'
                              end
                        end
                     else
                        begin
                           select @objectname = @owner + '.' + @objectname,
                                  @printname  = @objectname + ' (' + @owner + ')'
                        end

                     select @printmssg = @printname
                                         + space (@maxobjlen - datalength (@printname))
                                         + '(' + @createdate + ')'

                     print ' '

                     print ' '

                     print '    %1!', @printmssg

                     execute  sp_showtable  @TABLE=@objectname

                     fetch object_prefix_cursor into @objectname, @owner, @createdate
                  end

               if (@@sqlstatus <> 2)
                  begin
                     select @errorchar = convert (varchar (4), @@sqlstatus)

                     print '  CURSOR ERROR.  STATUS = %1!.', @errorchar
                  end
            end

         close object_prefix_cursor

         deallocate cursor object_prefix_cursor
      end
go
sp_addcomment sp_showtables, 'sp_showtable for all tables/views with following qualifiers:'
go
sp_addcomment sp_showtables, '   (NULL): all user tables and views'
go
sp_addcomment sp_showtables, '   PREFIX: only tables/views like PREFIX%'
go
sp_addcomment sp_showtables, '   "S":    only System tables'
go
sp_addcomment sp_showtables, '   "U":    only User tables (default)'
go
sp_addcomment sp_showtables, '   "V":    only Views'
go
sp_procxmode sp_showtables, anymode
go

create procedure sp_tablesx (@PREFIX     varchar (30) = NULL,
                             @SORT_FLAG  varchar (1)  = NULL)  as
   set ansinull                      on
   set flushmessage                  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_tablesx CANNOT BE RUN FROM WITHIN A TRANSACTION.'

         print ' '

         return 1
      end

   declare @cmdstring   varchar (255),
           @likestring  varchar ( 30),
           @maxnamelen  tinyint

   create table #tablesx
      (id         int            not null,
       uid        smallint       not null,
       crdate     datetime       not null,
       tablename  varchar ( 33)  not null,
       totp       int            not null,
       datap      char ( 11)     not null,
       indexp     char ( 11)     not null,
       tuples     char ( 13)     not null,
       maxlen     char (  7)     not null,
       ownername  varchar ( 30)  not null,
       indexes    smallint       not null,
       printname  varchar (31)   not null)

   execute  sp_like_string  @INPUT_STRING=@PREFIX,
                            @LIKE_STRING=@likestring  output

   if exists (select *
              from sysobjects
              where ((@PREFIX IS NULL)  or
                     (upper (@PREFIX) in ('DATE', 'OWNER', 'PAGES', 'ROWS', 'WIDTH')))
                and (sysstat & 15) = 3)
      begin
         insert into #tablesx
            select distinct o.id,
                            o.uid,
                            o.crdate,
                            o.name,
                            0,
                            substring (convert (char (14),
                                                convert (money,
                                                         sum (data_pgs (i.id, i.doampg))),
                                                1),
                                       1,
                                       11),
                            substring (convert (char (14),
                                                convert (money,
                                                         sum (data_pgs (i.id, i.ioampg))),
                                                1),
                                       1,
                                       11),
                            substring (convert (char (16),
                                                convert (money,
                                                         rowcnt (i0.doampg)),
                                                1),
                                       1,
                                       13),
                            substring (convert (char (10),
                                                convert (money,
                                                         i0.maxlen),
                                                1),
                                       1,
                                       7),
                            substring (user_name (o.uid), 1, 25),
                            0,
                            ' '
            from sysindexes i,
                 sysindexes i0,
                 sysobjects o
            where (o.sysstat & 15) = 3
              and i0.id            = o.id
              and i0.indid         < 2
              and i.id             = o.id
              and i.indid          < 255
            group by o.id
            having (o.sysstat & 15) = 3
               and i0.id            = o.id
               and i0.indid         < 2
               and i.id             = o.id
               and i.indid          < 255
         execute  sp_exclude_object  @TYPE='EXT',
                                     @TABLENAME='#tablesx',
                                     @COLUMNNAME='tablename'
      end
   else if (exists (select *
                    from sysobjects
                    where @PREFIX          IS NOT NULL
                      and name           like @likestring
                      and (sysstat & 15)   in (1, 3)))
      begin
         insert into #tablesx
            select distinct o.id,
                            o.uid,
                            o.crdate,
                            o.name,
                            0,
                            substring (convert (char (14),
                                                convert (money,
                                                         sum (data_pgs (i.id, i.doampg))),
                                                1),
                                       1,
                                       11),
                            substring (convert (char (14),
                                                convert (money,
                                                         sum (data_pgs (i.id, i.ioampg))),
                                                1),
                                       1,
                                       11),
                            substring (convert (char (16),
                                                convert (money,
                                                         rowcnt (i0.doampg)),
                                                1),
                                       1,
                                       13),
                            substring (convert (char (10),
                                                convert (money,
                                                         i0.maxlen),
                                                1),
                                       1,
                                       7),
                            substring (user_name (o.uid), 1, 25),
                            0,
                            ' '
            from sysindexes i,
                 sysindexes i0,
                 sysobjects o
            where (o.sysstat & 15)   in (1, 3)
              and o.name           like @likestring
              and i0.id               = o.id
              and i0.indid            < 2
              and i.id                = o.id
              and i.indid             < 255
            group by o.id
            having (o.sysstat & 15)   in (1, 3)
               and o.name           like @likestring
               and i0.id               = o.id
               and i0.indid            < 2
               and i.id                = o.id
               and i.indid             < 255
      end
   else if (@PREFIX IS NULL)
      begin
         print '    THERE ARE NO USER TABLES.'

         print ' '
      end
   else
      begin
         print '    THERE ARE NO USER TABLES LIKE %1!.', @likestring

         print ' '
      end

   if (exists (select *
               from #tablesx))
      begin
         update #tablesx
         set tablename = substring (t.tablename + ' (' + ownername + ')', 1, 33)
         from #tablesx t
         where uid <> 1
            or 1    < (select count (t2.uid)
                       from #tablesx t2
                       where t.tablename = t2.tablename)

         update #tablesx
         set tablename = substring (t.tablename + ' [' + a.char_value + ']', 1, 33)
         from #tablesx t,
              sysattributes a
         where a.class        = 3             -- buffer manager
           and a.attribute    = 0             -- cache bindings
           and a.object_type  = 'T'           -- table binding
           and t.id           = a.object

         update #tablesx
         set indexes = (select count (i0.id) - 1
                        from sysindexes i0
                        where i0.id    = t.id
                          and i0.indid < 254
                          and (exists (select ii0.*
                                       from sysindexes ii0
                                       where ii0.id    = t.id
                                         and ii0.indid = 0)))
         from #tablesx t

         update #tablesx
         set indexes = (select count (i1.id)
                        from sysindexes i1
                        where i1.id    = t.id
                          and i1.indid < 254
                          and (exists (select ii1.*
                                       from sysindexes ii1
                                       where ii1.id    = t.id
                                         and ii1.indid = 1)))
         from #tablesx t
         where (exists (select ii11.*
                        from sysindexes ii11
                        where ii11.id    = t.id
                          and ii11.indid = 1))

         update #tablesx
         set indexp = '          -',
             tuples = '            -'
         where id = 8

         select @maxnamelen = max (char_length (tablename))
         from #tablesx

         if (@maxnamelen > 23)
            begin
               select @maxnamelen = 23
            end

         select @cmdstring = 'select ''TABLE'' = substring (tablename, 1,'
                             +  convert (varchar (2), @maxnamelen)
                             +  '),'
                             +  '''DATA PAGES'' = datap,'
                             +  '''INDEX PAGES'' = indexp,'
                             +  '''APP ROWS'' = tuples,'
                             +  '''MAX LEN'' = maxlen,'
                             +  'INDEXES = indexes'
                             +  ' from #tablesx'

         if (@PREFIX IS NULL)
            begin
               if (@SORT_FLAG IS NULL)
                  begin
                     select @cmdstring = @cmdstring
                                         +  ' order by tablename'
                  end
               else
                  begin
                     select @cmdstring = @cmdstring
                                         +  ' order by tablename desc'
                  end
            end
         else if (upper (@PREFIX) = 'DATE')
            begin
               if (@maxnamelen = 23)
                  begin
                     select @maxnamelen = 22
                  end

               select @cmdstring = 'select ''DATE'' = convert (char (8), crdate, 11),'
                                   +  '''TABLE'' = substring (tablename, 1,'
                                   +  convert (varchar (2), @maxnamelen)
                                   +  '),'
                                   +  '''DATA PAGES'' = datap,'
                                   +  '''INDEX PAGES'' = indexp,'
                                   +  '''APP ROWS'' = tuples,'
                                   +  '''MAX LEN'' = maxlen'
                                   +  ' from #tablesx'

               if (@SORT_FLAG IS NULL)
                  begin
                     select @cmdstring = @cmdstring
                                         +  ' order by crdate, tablename'
                  end
               else
                  begin
                     select @cmdstring = @cmdstring
                                         +  ' order by crdate desc, tablename'
                  end
            end
         else if (upper (@PREFIX) = 'OWNER')
            begin
               update #tablesx
               set tablename = substring (tablename, 1, charindex ('(', tablename) - 1)
               where charindex ('(', tablename) > 0
                 and charindex ('[', tablename) = 0

               update #tablesx
               set tablename = substring (tablename, 1, charindex ('(', tablename) - 1)
                               + substring (tablename, charindex ('[', tablename) - 1, 23)
               where charindex ('(', tablename) > 0
                 and charindex ('[', tablename) > 0

               update #tablesx
               set printname = substring (ownername + '.' + tablename, 1, 31)

               insert into #tablesx
                  select 0, 0, getdate (), ' ', 0, ' ', ' ', ' ', ' ',
                         ownername, 0, ' '
                  from #tablesx
                  group by ownername

               delete from #tablesx
               where printname = ' '
                 and ownername = (select min (ownername)
                                  from #tablesx)

               select @maxnamelen = max (char_length (printname))
               from #tablesx

               select @cmdstring = 'select ''TABLE'' = substring (printname, 1,'
                                   +  convert (varchar (2), @maxnamelen)
                                   +  '),'
                                   +  '''DATA PAGES'' = datap,'
                                   +  '''INDEX PAGES'' = indexp,'
                                   +  '''APP ROWS'' = tuples,'
                                   +  '''MAX LEN'' = maxlen'
                                   +  ' from #tablesx'

               if (@SORT_FLAG IS NULL)
                  begin
                     select @cmdstring = @cmdstring
                                         +  ' order by ownername, tablename'
                  end
               else
                  begin
                     select @cmdstring = @cmdstring
                                         +  ' order by ownername desc, tablename'
                  end
            end
         else if (upper (@PREFIX) = 'PAGES')
            begin
               update #tablesx
               set totp = (select sum (data_pgs (i.id, i.doampg)) + sum (data_pgs (i.id, i.ioampg))
                           from sysindexes i
                           where i.id = t.id)
               from #tablesx t

               if (@SORT_FLAG IS NULL)
                  begin
                     select @cmdstring = @cmdstring
                                         +  ' order by totp, datap, tuples, tablename'
                  end
               else
                  begin
                     select @cmdstring = @cmdstring
                                         +  ' order by totp desc, datap desc, tuples desc, tablename'
                  end
            end
         else if (upper (@PREFIX) = 'ROWS')
            begin
               if (@SORT_FLAG IS NULL)
                  begin
                     select @cmdstring = @cmdstring
                                         +  ' order by tuples, tablename'
                  end
               else
                  begin
                     select @cmdstring = @cmdstring
                                         +  ' order by tuples desc, tablename'
                  end
            end
         else if (upper (@PREFIX) = 'WIDTH')
            begin
               if (@SORT_FLAG IS NULL)
                  begin
                     select @cmdstring = @cmdstring
                                         +  ' order by maxlen, tablename'
                  end
               else
                  begin
                     select @cmdstring = @cmdstring
                                         +  ' order by maxlen desc, tablename'
                  end
            end
         else
            begin
               if (@SORT_FLAG IS NULL)
                  begin
                     select @cmdstring = @cmdstring
                                         +  ' order by tablename'
                  end
               else
                  begin
                     select @cmdstring = @cmdstring
                                         +  ' order by tablename desc'
                  end
            end

         execute (@cmdstring)
      end
go
sp_addcomment sp_tablesx, 'Lists user tables with space data with several options:'
go
sp_addcomment sp_tablesx, '   (NULL):    all user tables in table name order (default)'
go
sp_addcomment sp_tablesx, '   "DATE":    all user tables in creation date order'
go
sp_addcomment sp_tablesx, '   "OWNER":   all user tables in user / table name order'
go
sp_addcomment sp_tablesx, '   "PAGES":   all user tables in number of pages order'
go
sp_addcomment sp_tablesx, '   PREFIX:    only user tables like PREFIX% in table name order'
go
sp_addcomment sp_tablesx, '   "ROWS":    all user tables in number of rows order'
go
sp_addcomment sp_tablesx, '   "WIDTH":   all user tables in table width order'
go
sp_addcomment sp_tablesx, '   SORT FLAG: secondary option for descending sorts'
go
sp_procxmode sp_tablesx, anymode
go

create procedure sp_triggers (@TABLE varchar (63) = NULL) as
   set ansinull                      on
   set flushmessage                  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_triggers CANNOT BE RUN FROM WITHIN A TRANSACTION.'

         print ' '

         return 1
      end

   declare @cmdstring   varchar (255),
           @dellen      tinyint,
           @inslen      tinyint,
           @tablen      tinyint,
           @tableid     int,
           @triglen     tinyint,
           @updlen      tinyint

   -- DETERMINE WHICH TEMPORARY TABLE STRUCTURE TO USE AND POSSIBLY LOAD
   -- PRELIMINARY DATA.

   if (@TABLE IS NULL)
      begin
         create table #triggers
            (tabid     int            not null,
             tabuid    smallint       not null,
             tabname   varchar (63)   not null,
             deltrig   int            not null,
             delname   varchar (65)   not null,
             deldt     datetime       null,
             deldate   char (14)      not null,
             instrig   int            not null,
             insname   varchar (65)   not null,
             insdt     datetime       null,
             insdate   char (14)      not null,
             updtrig   int            not null,
             updname   varchar (65)   not null,
             upddt     datetime       null,
             upddate   char (14)      not null)
      end
   else
      begin
         create table #triggersdt
            (tdate     datetime       null,
             fmt_date  char (14)      null,
             tid       integer        not null,
             tname     varchar (32)   null,
             tabuid    smallint       not null,
             tabname   varchar (63)   not null,
             ttype     char (6)       not null)

         if (upper (@TABLE) <> 'DATE')
            begin
               select @tableid = object_id (@TABLE)
               from sysobjects
               where (sysstat & 15) = 3
            end

         insert into #triggersdt
            select trig.crdate,
                   convert (char (8), trig.crdate, 11)
                   +  ' '
                   +  substring (convert (char (8), trig.crdate, 8), 1, 5),
                   tab.deltrig,
                   trig.name,
                   tab.uid,
                   tab.name,
                   'DELETE'
            from sysobjects tab,
                 sysobjects trig
            where tab.id                    = coalesce (@tableid, tab.id)
              and (tab.sysstat & 15)        = 3
              and (tab.sysstat2 & 2097152) <> 2097152
              and tab.deltrig               > 0
              and trig.id                   = tab.deltrig

         insert into #triggersdt
            select trig.crdate,
                   convert (char (8), trig.crdate, 11)
                   +  ' '
                   +  substring (convert (char (8), trig.crdate, 8), 1, 5),
                   tab.deltrig,
                   trig.name  +  ' #',
                   tab.uid,
                   tab.name,
                   'DELETE'
            from sysobjects tab,
                 sysobjects trig
            where tab.id                   = coalesce (@tableid, tab.id)
              and (tab.sysstat & 15)       = 3
              and (tab.sysstat2 & 2097152) = 2097152
              and tab.deltrig              > 0
              and trig.id                  = tab.deltrig

         insert into #triggersdt
            select trig.crdate,
                   convert (char (8), trig.crdate, 11)
                   +  ' '
                   +  substring (convert (char (8), trig.crdate, 8), 1, 5),
                   tab.instrig,
                   trig.name,
                   tab.uid,
                   tab.name,
                   'INSERT'
            from sysobjects tab,
                 sysobjects trig
            where tab.id                    = coalesce (@tableid, tab.id)
              and (tab.sysstat & 15)        = 3
              and (tab.sysstat2 & 1048576) <> 1048576
              and tab.instrig               > 0
              and trig.id                   = tab.instrig

         insert into #triggersdt
            select trig.crdate,
                   convert (char (8), trig.crdate, 11)
                   +  ' '
                   +  substring (convert (char (8), trig.crdate, 8), 1, 5),
                   tab.instrig,
                   trig.name  +  ' #',
                   tab.uid,
                   tab.name,
                   'INSERT'
            from sysobjects tab,
                 sysobjects trig
            where tab.id                   = coalesce (@tableid, tab.id)
              and (tab.sysstat & 15)       = 3
              and (tab.sysstat2 & 1048576) = 1048576
              and tab.instrig              > 0
              and trig.id                  = tab.instrig

         insert into #triggersdt
            select trig.crdate,
                   convert (char (8), trig.crdate, 11)
                   +  ' '
                   +  substring (convert (char (8), trig.crdate, 8), 1, 5),
                   tab.updtrig,
                   trig.name,
                   tab.uid,
                   tab.name,
                   'UPDATE'
            from sysobjects tab,
                 sysobjects trig
            where tab.id                    = coalesce (@tableid, tab.id)
              and (tab.sysstat & 15)        = 3
              and (tab.sysstat2 & 4194304) <> 4194304
              and tab.updtrig               > 0
              and trig.id                   = tab.updtrig

         insert into #triggersdt
            select trig.crdate,
                   convert (char (8), trig.crdate, 11)
                   +  ' '
                   +  substring (convert (char (8), trig.crdate, 8), 1, 5),
                   tab.updtrig,
                   trig.name  +  ' #',
                   tab.uid,
                   tab.name,
                   'UPDATE'
            from sysobjects tab,
                 sysobjects trig
            where tab.id                   = coalesce (@tableid, tab.id)
              and (tab.sysstat & 15)       = 3
              and (tab.sysstat2 & 4194304) = 4194304
              and tab.updtrig              > 0
              and trig.id                  = tab.updtrig

         if ((upper (@TABLE) = 'DATE')  and
             (exists (select *
                      from #triggersdt
                      where tname like '%#')))
            begin
               print '    THERE ARE DISABLED TRIGGERS (#).'

               print ' '
            end
      end

   if (upper (@TABLE) = 'DATE')
      begin
         if (exists (select *
                     from #triggersdt))
            begin
               update #triggersdt
               set tabname = t.tabname + ' (' + user_name (t.tabuid) + ')'
               from #triggersdt t
               where t.tabuid <> 1
                  or 1         < (select count (o.uid)
                                  from sysobjects o
                                  where o.name           = t.tabname
                                    and (o.sysstat & 15) = 3)

               select @tablen  = max (char_length (tabname)),
                      @triglen = max (char_length (tname))
               from #triggersdt

               if (@triglen < 12)
                  begin
                     select @triglen = 12
                  end

               if (@tablen < 5)
                  begin
                     select @tablen = 5
                  end

               if ((@triglen + @tablen) > 54)
                  begin
                     while ((@triglen + @tablen) > 54)
                        begin
                           if (@triglen > 12)
                              begin
                                 select @triglen = @triglen - 1
                              end

                           if (@tablen > 5)
                              begin
                                 select @tablen = @tablen - 1
                              end
                        end

                     select @tablen = 54 - @triglen
                  end

               select @cmdstring = 'select ''TRIGGER DATE'' = fmt_date,'
                                   +  '''TRIGGER NAME'' = substring (tname, 1,'
                                   +  convert (varchar (2), @triglen)
                                   +  '),'
                                   +  '''TABLE'' = substring (tabname, 1,'
                                   +  convert (varchar (2), @tablen)
                                   +  '),'
                                   +  'TYPE = ttype'
                                   +  ' from #triggersdt'
                                   +  ' order by tdate, tname, tabname, ttype'

               execute (@cmdstring)
            end
         else
            begin
               print '    THERE ARE NO TRIGGERS ON USER TABLES.'

               print ' '
            end
      end
   else if (@TABLE IS NULL)
      begin
         insert into #triggers
            select id,
                   uid,
                   name,
                   deltrig, isnull (object_name (deltrig), '-'), NULL, ' ',
                   instrig, isnull (object_name (instrig), '-'), NULL, ' ',
                   updtrig, isnull (object_name (updtrig), '-'), NULL, ' '
            from sysobjects o
            where (sysstat & 15) = 3
              and ((deltrig > 0)  or  (instrig > 0)  or  (updtrig > 0))

         if (exists (select *
                     from #triggers))
            begin
               update #triggers
               set delname = '# '  +  t.delname
               from sysobjects o,
                    #triggers t
               where o.id                   = t.tabid
                 and (o.sysstat2 & 2097152) = 2097152

               update #triggers
               set insname = '# '  +  t.insname
               from sysobjects o,
                    #triggers t
               where o.id                   = t.tabid
                 and (o.sysstat2 & 1048576) = 1048576

               update #triggers
               set updname = '# '  +  t.updname
               from sysobjects o,
                    #triggers t
               where o.id                   = t.tabid
                 and (o.sysstat2 & 4194304) = 4194304

               if (exists (select *
                           from #triggers
                           where delname like '#%'
                              or insname like '#%'
                              or updname like '#%'))
                  begin
                     print '    THERE ARE DISABLED TRIGGERS (#).'

                     print ' '
                  end
               else
                  begin
                     print '    ALL TRIGGERS ARE ENABLED.'

                     print ' '
                  end

               update #triggers
               set tabname = t.tabname + ' (' + user_name (t.tabuid) + ')'
               from #triggers t
               where t.tabuid <> 1
                  or 1         < (select count (o.uid)
                                  from sysobjects o
                                  where o.name           = t.tabname
                                    and (o.sysstat & 15) = 3)

               update #triggers
               set delname = t.delname + ' (' + user_name (o.uid) + ')'
               from sysobjects o,
                    #triggers t
               where o.id   = t.deltrig
                 and o.uid <> t.tabuid

               update #triggers
               set insname = t.insname + ' (' + user_name (o.uid) + ')'
               from sysobjects o,
                    #triggers t
               where o.id   = t.instrig
                 and o.uid <> t.tabuid

               update #triggers
               set updname = t.updname + ' (' + user_name (o.uid) + ')'
               from sysobjects o,
                    #triggers t
               where o.id   = t.updtrig
                 and o.uid <> t.tabuid

               update #triggers
               set deldt   = o.crdate,
                   deldate = convert (char (8), o.crdate, 11)
                             + ' '
                             + substring (convert (char (8), o.crdate, 8), 1, 5)
               from sysobjects o
               where #triggers.deltrig > 0
                 and #triggers.deltrig = o.id

               update #triggers
               set insdt   = o.crdate,
                   insdate = convert (char (8), o.crdate, 11)
                             + ' '
                             + substring (convert (char (8), o.crdate, 8), 1, 5)
               from sysobjects o
               where #triggers.instrig > 0
                 and #triggers.instrig = o.id

               update #triggers
               set upddt   = o.crdate,
                   upddate = convert (char (8), o.crdate, 11)
                             + ' '
                             + substring (convert (char (8), o.crdate, 8), 1, 5)
               from sysobjects o
               where #triggers.updtrig > 0
                 and #triggers.updtrig = o.id

               select @dellen = max (char_length (delname)),
                      @inslen = max (char_length (insname)),
                      @tablen = max (char_length (tabname)),
                      @updlen = max (char_length (updname))
               from #triggers

               if (@tablen < 5)
                  begin
                     select @tablen = 5
                  end
               else if (@tablen > 56)
                  begin
                     select @tablen = 56
                  end

               if (@dellen < 6)
                  begin
                     select @dellen = 6
                  end

               if (@inslen < 6)
                  begin
                     select @inslen = 6
                  end

               if (@updlen < 6)
                  begin
                     select @updlen = 6
                  end

               if ((@tablen + @dellen + @inslen + @updlen) > 74)
                  begin
                     while ((@tablen + @dellen + @inslen + @updlen) > 74)
                        begin
                           if (@dellen > 6)
                              begin
                                 select @dellen = @dellen - 1
                              end

                           if (@inslen > 6)
                              begin
                                 select @inslen = @inslen - 1
                              end

                           if (@updlen > 6)
                              begin
                                 select @updlen = @updlen - 1
                              end
                        end

                     select @updlen = 74 - @tablen - @dellen - @inslen
                  end

               select @cmdstring = 'select ''TABLE'' = substring (tabname, 1,'
                                   +  convert (varchar (2), @tablen)
                                   +  '),'
                                   +  '''DELETE'' = substring (delname, 1,'
                                   +  convert (varchar (2), @dellen)
                                   +  '),'
                                   +  '''INSERT'' = substring (insname, 1,'
                                   +  convert (varchar (2), @inslen)
                                   +  '),'
                                   +  '''UPDATE'' = substring (updname, 1,'
                                   +  convert (varchar (2), @updlen)
                                   +  ')'
                                   +  ' from #triggers'
                                   +  ' order by tabname'

               execute  (@cmdstring)
            end
         else
            begin
               print '    THERE ARE NO TRIGGERS ON USER TABLES.'

               print ' '
            end
      end
   else
      begin
         if (@tableid IS NULL)
            begin
               print '    TABLE %1! DOES NOT EXIST.', @TABLE

               print ' '

               return 1
            end
         else if (exists (select *
                          from #triggersdt))
            begin
               if (exists (select *
                           from #triggersdt
                           where tname like '%#'))
                  begin
                     print '    THERE ARE DISABLED TRIGGERS (#) FOR %1!.',
                           @TABLE

                     print ' '
                  end

               select @triglen = max (char_length (tname))
               from #triggersdt

               if (@triglen < 12)
                  begin
                     select @triglen = 12
                  end

               select @cmdstring = 'select TYPE = ttype,'
                                   +  '''TRIGGER NAME'' = substring (tname, 1,'
                                   +  convert (varchar (2), @triglen)
                                   +  '),'
                                   +  '''TRIGGER DATE'' = fmt_date'
                                   +  ' from #triggersdt'
                                   +  ' order by TYPE'

               execute  (@cmdstring)
            end
         else
            begin
               print '    THERE ARE NO TRIGGERS ON TABLE %1!.', @TABLE

               print ' '
            end
      end
go
sp_addcomment sp_triggers, 'Lists triggers with various scope/sort options:'
go
sp_addcomment sp_triggers, '   (NULL): all triggers in table name order (default)'
go
sp_addcomment sp_triggers, '   "DATE": all triggers in date order'
go
sp_addcomment sp_triggers, '   TABLE:  only triggers for TABLE'
go
sp_procxmode sp_triggers, anymode
go

create procedure sp_usertypes 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_usertypes CANNOT BE RUN FROM WITHIN A TRANSACTION.'

         print ' '

         return 1
      end

   declare @btlen       tinyint,
           @cmdstring   varchar (255),
           @dfltlen     tinyint,
           @rulelen     tinyint,
           @utcount     varchar (  7),
           @utlen       tinyint

   create table #usertypes
     (uttext    varchar (30)  not null,
      uid       smallint      not null,
      len       tinyint       not null,
      prec      tinyint       null,
      scale     tinyint       null,
      bttype    tinyint       not null,
      bttext    varchar (30)  not null,
      dflt      varchar (30)  not null,
      rul       varchar (30)  not null,
      variable  tinyint       not null,
      nulltype  tinyint       not null)

   insert into #usertypes
      select ut.name,
             ut.uid,
             ut.length,
             ut.prec,
             ut.scale,
             bt.type,
             bt.name,
             isnull (object_name (ut.tdefault), '-'),
             isnull (object_name (ut.domain), '-'),
             ut.variable,
             ut.allownulls
      from systypes ut,
           systypes bt
      where ut.usertype > 99
        and bt.usertype < 100
        and ut.type     = bt.type

   insert into #usertypes
      select ut.name,
             ut.uid,
             ut.length,
             ut.prec,
             ut.scale,
             bt.type,
             bt.name,
             isnull (object_name (ut.tdefault), '-'),
             isnull (object_name (ut.domain), '-'),
             ut.variable,
             ut.allownulls
      from systypes ut,
           systypes bt
      where ut.usertype = 80              -- timestamp
        and bt.usertype = 4               -- varbinary

   delete from #usertypes
   where bttext in ('nchar', 'nvarchar', 'timestamp')

   delete from #usertypes
   where bttext = 'sysname'
     and ((len <> 30)  or  (nulltype = 0))

   select @utcount = ltrim (substring (convert (char (10),
                                                convert (money,
                                                         count (*)),
                                                1),
                                       1,
                                       7))
   from #usertypes

   print '    THERE ARE %1! USERTYPES.', @utcount

   print ' '

   update #usertypes
   set uttext = substring (uttext + ' (' + user_name (uid) + ')', 1, 30)
   where uid <> 1

   update #usertypes
   set bttext = bttext + ' (' +  convert (varchar (3), len) + ')'
   where bttype in (37, 38, 39, 45, 47)

   update #usertypes
   set bttext = bttext + ' (' +  convert (varchar (3), prec)
                + ', ' +  convert (varchar (3), scale) + ')'
   where bttype in (55, 63, 106, 108)

   update #usertypes
   set bttext = 'float' + ' (' +  convert (varchar (3), prec) + ')'
   where bttype in (59, 62)
     and prec   IS NOT NULL

   update #usertypes
   set bttext = 'real'
   where bttype  = 59
     and prec   IS NULL

   update #usertypes
   set bttext = 'double precision'
   where bttype  = 62
     and prec   IS NULL

   update #usertypes
   set bttext = 'sysname'
   where bttype   = 39
     and len      = 30
     and variable = 0

   update #usertypes
   set bttext = bttext  +  ' *'
   where nulltype = 1

   select @btlen   = max (char_length (bttext)),
          @dfltlen = max (char_length (dflt)),
          @rulelen = max (char_length (rul)),
          @utlen   = max (char_length (uttext))
   from #usertypes

   if (@utlen < 8)
      begin
         select @utlen = 8
      end

   if (@btlen < 9)
      begin
         select @btlen = 9
      end

   if (@dfltlen < 7)
      begin
         select @dfltlen = 7
      end

   if (@rulelen < 4)
      begin
         select @rulelen = 4
      end

   if ((@utlen + @btlen + @dfltlen + @rulelen) > 72)
      begin
         while ((@utlen + @btlen + @dfltlen + @rulelen) > 72)
            begin
               if (@dfltlen > 7)
                  begin
                     select @dfltlen = @dfltlen - 1
                  end

               if (@rulelen > 7)
                  begin
                     select @rulelen = @rulelen - 1
                  end
            end

         select @dfltlen = 72 - @utlen - @btlen - @rulelen
      end

   select @cmdstring = 'select ''USERTYPE'' = substring (uttext, 1,'
                       +  convert (varchar (2), @utlen)
                       +  '),'
                       +  '''BASE TYPE'' = substring (bttext, 1,'
                       +  convert (varchar (2), @btlen)
                       +  '),'
                       +  '''DEFAULT'' = substring (dflt, 1,'
                       +  convert (varchar (2), @dfltlen)
                       +  '),'
                       +  '''RULE'' = substring (rul, 1,'
                       +  convert (varchar (2), @rulelen)
                       +  ')'
                       +  ' from #usertypes'
                       +  ' order by uttext'

   execute  (@cmdstring)

   print ' '

   delete #usertypes
   from #usertypes t
   where 1 = (select count (t2.uttext)
              from #usertypes t2
              where t.bttype   = t2.bttype
                and t.variable = t2.variable
                and t.nulltype = t2.nulltype
                and t.len      = t2.len
                and t.dflt     = t2.dflt
                and t.rul      = t2.rul)

   if (exists (select *
                from #usertypes))
      begin
         print '    THERE ARE MULTIPLE USERTYPES WITH THE SAME ATTRIBUTES.'

         print ' '

         select @btlen   = max (char_length (bttext)),
                @dfltlen = max (char_length (dflt)),
                @rulelen = max (char_length (rul)),
                @utlen   = max (char_length (uttext))
         from #usertypes

         if (@utlen < 8)
            begin
               select @utlen = 8
            end

         if (@btlen < 9)
            begin
               select @btlen = 9
            end

         if (@dfltlen < 7)
            begin
               select @dfltlen = 7
            end

         if (@rulelen < 4)
            begin
               select @rulelen = 4
            end

         if ((@utlen + @btlen + @dfltlen + @rulelen) > 72)
            begin
               while ((@utlen + @btlen + @dfltlen + @rulelen) > 72)
                  begin
                     if (@dfltlen > 7)
                        begin
                           select @dfltlen = @dfltlen - 1
                        end

                     if (@rulelen > 7)
                        begin
                           select @rulelen = @rulelen - 1
                        end
                  end

               select @dfltlen = 72 - @utlen - @btlen - @rulelen
            end

         select @cmdstring = 'select ''USERTYPE'' = substring (uttext, 1,'
                             +  convert (varchar (2), @utlen)
                             +  '),'
                             +  '''BASE TYPE'' = substring (bttext, 1,'
                             +  convert (varchar (2), @btlen)
                             +  '),'
                             +  '''DEFAULT'' = substring (dflt, 1,'
                             +  convert (varchar (2), @dfltlen)
                             +  '),'
                             +  '''RULE'' = substring (rul, 1,'
                             +  convert (varchar (2), @rulelen)
                             +  ')'
                             +  ' from #usertypes '
                             +  ' order by bttext, uttext'

         execute  (@cmdstring)
      end
   else
      begin
         print '    ALL USERTYPES ARE OF UNIQUE BASE TYPE.'
      end

   print ' '
go
sp_addcomment sp_usertypes, 'Lists all user defined types with their attributes'
go
sp_procxmode sp_usertypes, anymode
go

create procedure sp_views (@PARM varchar (30) = NULL) as
   set ansinull                      on
   set flushmessage                  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_views CANNOT BE RUN FROM WITHIN A TRANSACTION.'

         print ' '

         return 1
      end

   declare @basestring  varchar ( 78),
           @cmdstring   varchar (255),
           @likestring  varchar ( 30),
           @retval      int,
           @viewlen     tinyint

   create table #views
      (id        int           not null,
       uid       smallint      not null,
       name      varchar (62)  not null,
       crdate    datetime      not null,
       fmt_date  char (14)     null,
       vpages    int           not null,
       btables   tinyint       not null,
       bviews    tinyint       not null)

   select @retval   = 0

   if exists (select *
              from sysobjects
              where (sysstat & 15) = 2)
      begin
         if (upper (@PARM) in ('DATE', 'SIZE'))
            begin
               insert into #views
                  select id, uid, name, crdate, NULL, 0, 0, 0
                  from sysobjects
                  where (sysstat & 15) = 2
            end
         else
            begin
               execute  sp_like_string  @INPUT_STRING=@PARM,
                                        @LIKE_STRING=@likestring  output

               insert into #views
                  select id, uid, name, crdate, NULL, 0, 0, 0
                  from sysobjects
                  where name           like @likestring
                    and (sysstat & 15)    = 2

               if (@PARM IS NULL)
                  begin
                     execute  sp_exclude_object  @TYPE='EXV',
                                                 @TABLENAME='#views',
                                                 @COLUMNNAME='name'
                  end
            end

         if (exists (select d.id
                     from sysdepends d,
                          #views v
                     where d.id                   = v.id
                       and object_name (d.depid) IS NULL))
            begin
               print '    VIEW DEPENDENCY COUNTS ARE NOT GUARANTEED.  EXECUTE sp_usesright.'

               print ' '

               select @retval = 1
            end

         update #views
         set name = substring (v.name  +  ' ('  +  user_name (v.uid)  +  ')', 1, 47)
         from #views v
         where v.uid <> 1
            or 1      < (select count (v2.uid)
                         from sysobjects v2
                         where v2.name = v.name)

         update #views
         set vpages = (select (1 + (count (p.sequence) / 8))
                       from sysprocedures p
                       where p.id = v.id)
         from #views v

         update #views
         set fmt_date = convert (char (8), crdate, 11)
                        +  ' '
                        +  substring (convert (char (8), crdate, 8), 1, 5),
             btables  = (select count (d.depid)
                         from sysdepends d,
                              sysobjects o
                         where d.id              = v.id
                           and o.id              = d.depid
                           and (o.sysstat & 15) in (1, 3)),
             bviews   = (select count (d.depid)
                         from sysdepends d,
                              sysobjects o
                         where d.id             = v.id
                           and o.id             = d.depid
                           and (o.sysstat & 15) = 2)
         from #views v

         select @viewlen = max (char_length (name))
         from #views

         if (@viewlen > 39)
            begin
               select @viewlen = 39
            end

         select @basestring = space (27 + @viewlen)  +  '<-- BASE -->'

         select @cmdstring = 'select CREATED = fmt_date,'
                             +  '''VIEW'' = substring (name, 1,'
                             +  convert (varchar (2), @viewlen)
                             +  '),'
                             +  'PAGES = substring (convert (char (12), convert (money, vpages), 1), 1, 9),'
                             +  '''TABLES'' = btables,'
                             +  '''VIEWS'' = bviews'
                             +  ' from #views'

         if (upper (@PARM) = 'DATE')
            begin
               print '%1!', @basestring

               select @cmdstring = @cmdstring
                                   +  ' order by crdate, name, user_name (uid)'

               execute  (@cmdstring)
            end
         else if (upper (@PARM) = 'SIZE')
            begin
               print '%1!', @basestring

               select @cmdstring = @cmdstring
                                   +  ' order by vpages desc, name, user_name (uid)'

               execute  (@cmdstring)
            end
         else if (exists (select *
                          from #views))
            begin
               print '%1!', @basestring

               select @cmdstring = @cmdstring
                                   +  ' order by name, user_name (uid)'

               execute  (@cmdstring)
            end
         else
            begin
               if (@PARM IS NULL)
                  begin
                     print '    THERE ARE NO VIEWS OTHER THAN POSSIBLY EXCLUDED VIEWS.'

                     print ' '
                  end
               else
                  begin
                     print '    THERE ARE NO VIEWS LIKE %1!.', @likestring

                     print ' '
                  end
            end
      end
   else
      begin
         print '    THERE ARE NO VIEWS.'

         print ' '
      end

   return @retval
go
sp_addcomment sp_views, 'Lists all views with several options:'
go
sp_addcomment sp_views, '   (NULL): all views in name order'
go
sp_addcomment sp_views, '   "DATE": all views in date order'
go
sp_addcomment sp_views, '   "SIZE": all views in size order'
go
sp_addcomment sp_views, '   PREFIX: only views like PREFIX%'
go
sp_procxmode sp_views, anymode
go

dump transaction sybsystemprocs with truncate_only
go
