use sybsystemprocs
go
sp_dropprocedure sp_datatype_decnum
go
sp_dropprocedure sp_datatype_dp
go
sp_dropprocedure sp_datatype_int
go
sp_dropprocedure sp_datatype_float
go
sp_dropprocedure sp_datatype_real
go
sp_dropprocedure sp_datatype_smallint
go
sp_dropprocedure sp_datatype_tinyint
go
sp_dropprocedure sp_datatype_usertype
go
sp_dropprocedure sp_datatype
go

set quoted_identifier on
go

------------ Temporary tables needed for creation of sp_datatype subroutines --

create table #datatype
   (id        int           not null,
    sysstat   smallint      not null,
    objname   varchar (30)  not null,
    colid     tinyint       not null,
    colname   varchar (30)  not null,
    uid       int           not null,
    owner     varchar (30)  not null,
    prntname  varchar (36)  not null,
    len       tinyint       not null,
    nullflag  int           not null,
    nulltext  char (4)      not null,
    dflt      int           null,
    dflttext  varchar (30)  null,
    domain    int           null,
    crtext    varchar (70)  null)
go


create procedure sp_datatype_decnum (@USERTYPE  smallint,
                                     @PREC      tinyint) as
   set ansinull                      on
   set string_rtruncation            on

   insert into #datatype
      select o.id,
             (o.sysstat & 15),
             o.name,
             c.colid,
             c.name,
             o.uid,
             user_name (o.uid),
             substring (o.name + '.' + c.name, 1, 36),
             c.length,
             convert (int, convert (bit, (c.status & 8))),
             ' ',
             c.cdefault,
             NULL,
             c.domain,
             NULL
      from syscolumns c,
           sysobjects o
      where c.usertype        = @USERTYPE
        and ((c.prec = @PREC)  or  ((c.prec IS NOT NULL)  and  (@PREC IS NULL)))
        and c.id              = o.id
        and (o.sysstat & 15) in (1, 2, 3)
go
sp_existsprocedure sp_datatype_decnum
go

create procedure sp_datatype_dp as
   set ansinull                      on
   set string_rtruncation            on

   insert into #datatype
      select o.id,
             (o.sysstat & 15),
             o.name,
             c.colid,
             c.name,
             o.uid,
             user_name (o.uid),
             substring (o.name + '.' + c.name, 1, 36),
             c.length,
             convert (int, convert (bit, (c.status & 8))),
             ' ',
             c.cdefault,
             NULL,
             c.domain,
             NULL
      from syscolumns c,
           sysobjects o
      where c.usertype        = 8
        and c.length          = 8
        and c.prec           IS NULL
        and c.id              = o.id
        and (o.sysstat & 15) in (1, 2, 3)
go
sp_existsprocedure sp_datatype_dp
go

create procedure sp_datatype_int as
   set ansinull                      on
   set string_rtruncation            on

   insert into #datatype
      select o.id,
             (o.sysstat & 15),
             o.name,
             c.colid,
             c.name,
             o.uid,
             user_name (o.uid),
             substring (o.name + '.' + c.name, 1, 36),
             4,
             convert (int, convert (bit, (c.status & 8))),
             ' ',
             c.cdefault,
             NULL,
             c.domain,
             NULL
      from syscolumns c,
           sysobjects o
      where c.usertype        in (7, 13)
        and c.length           = 4
        and c.id               = o.id
        and (o.sysstat & 15)  in (1, 2, 3)
go
sp_existsprocedure sp_datatype_int
go

create procedure sp_datatype_float (@PREC  tinyint) as
   set ansinull                      on
   set string_rtruncation            on

   insert into #datatype
      select o.id,
             (o.sysstat & 15),
             o.name,
             c.colid,
             c.name,
             o.uid,
             user_name (o.uid),
             substring (o.name + '.' + c.name, 1, 36),
             c.prec,
             convert (int, convert (bit, (c.status & 8))),
             ' ',
             c.cdefault,
             NULL,
             c.domain,
             NULL
      from syscolumns c,
           sysobjects o
      where c.usertype        in (8, 23)
        and ((c.prec = @PREC)  or  ((c.prec IS NOT NULL)  and  (@PREC IS NULL)))
        and c.id               = o.id
        and (o.sysstat & 15)  in (1, 2, 3)
go
sp_existsprocedure sp_datatype_float
go

create procedure sp_datatype_real as
   set ansinull                      on
   set string_rtruncation            on

   insert into #datatype
      select o.id,
             (o.sysstat & 15),
             o.name,
             c.colid,
             c.name,
             o.uid,
             user_name (o.uid),
             substring (o.name + '.' + c.name, 1, 36),
             c.length,
             convert (int, convert (bit, (c.status & 8))),
             ' ',
             c.cdefault,
             NULL,
             c.domain,
             NULL
      from syscolumns c,
           sysobjects o
      where ((c.usertype = 23)  or  ((c.usertype = 8) and (c.length = 4)))
        and c.prec           IS NULL
        and c.id              = o.id
        and (o.sysstat & 15) in (1, 2, 3)
go
sp_existsprocedure sp_datatype_real
go

create procedure sp_datatype_smallint as
   set ansinull                      on
   set string_rtruncation            on

   insert into #datatype
      select o.id,
             (o.sysstat & 15),
             o.name,
             c.colid,
             c.name,
             o.uid,
             user_name (o.uid),
             substring (o.name + '.' + c.name, 1, 36),
             2,
             convert (int, convert (bit, (c.status & 8))),
             ' ',
             c.cdefault,
             NULL,
             c.domain,
             NULL
      from syscolumns c,
           sysobjects o
      where c.usertype        in (6, 13)
        and c.length           = 2
        and c.id               = o.id
        and (o.sysstat & 15)  in (1, 2, 3)
go
sp_existsprocedure sp_datatype_smallint
go

create procedure sp_datatype_tinyint as
   set ansinull                      on
   set string_rtruncation            on

   insert into #datatype
      select o.id,
             (o.sysstat & 15),
             o.name,
             c.colid,
             c.name,
             o.uid,
             user_name (o.uid),
             substring (o.name + '.' + c.name, 1, 36),
             1,
             convert (int, convert (bit, (c.status & 8))),
             ' ',
             c.cdefault,
             NULL,
             c.domain,
             NULL
      from syscolumns c,
           sysobjects o
      where c.usertype        in (5, 13)
        and c.length           = 1
        and c.id               = o.id
        and (o.sysstat & 15)  in (1, 2, 3)
go
sp_existsprocedure sp_datatype_tinyint
go

create procedure sp_datatype_usertype (@USERTYPE smallint) as
   set ansinull                      on
   set flushmessage                  on
   set string_rtruncation            on

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

   create table #usertype
      (basetype  varchar (30)  not null,
       nullflag  int           not null,
       nulltext  char (4)      not null,
       dflttext  varchar (30)  not null,
       ruletext  varchar (30)  not null)

   insert into #usertype
      select t2.name,
             convert (int, t.allownulls),
             ' ',
             isnull (object_name (t.tdefault), '-'),
             isnull (object_name (t.domain), '-')
      from systypes t,
           systypes t2
      where t.usertype   = @USERTYPE
        and t.type       = t2.type
        and t2.usertype in (5, 6, 7, 11, 12, 15, 16, 17, 19, 20, 21, 22)

   insert into #usertype
      select substring (t2.name  +  ' ('  +  convert (varchar (3), t.length)  + ')', 1, 30),
             convert (int, t.allownulls),
             ' ',
             isnull (object_name (t.tdefault), '-'),
             isnull (object_name (t.domain), '-')
      from systypes t,
           systypes t2
      where t.usertype   = @USERTYPE
        and t.type       = t2.type
        and t2.usertype in (1, 2, 3, 4, 13)

   insert into #usertype
      select substring (t2.name  +  ' ('
                        +  convert (varchar (3), t.prec)  +  ', '
                        +  convert (varchar (3), t.scale)  +  ')',
                        1,
                        30),
             convert (int, t.allownulls),
             ' ',
             isnull (object_name (t.tdefault), '-'),
             isnull (object_name (t.domain), '-')
      from systypes t,
           systypes t2
      where t.usertype   = @USERTYPE
        and t.type       = t2.type
        and t2.usertype in (10, 26, 27, 28)

   insert into #usertype
      select 'double precision',
             convert (int, t.allownulls),
             ' ',
             isnull (object_name (t.tdefault), '-'),
             isnull (object_name (t.domain), '-')
      from systypes t,
           systypes t2
      where t.usertype  = @USERTYPE
        and t.type     in (62, 109)
        and t.length    = 8
        and t.prec     IS NULL

   insert into #usertype
      select 'float ('  +  convert (varchar (3), t.prec)  +  ')',
             convert (int, t.allownulls),
             ' ',
             isnull (object_name (t.tdefault), '-'),
             isnull (object_name (t.domain), '-')
      from systypes t,
           systypes t2
      where t.usertype  = @USERTYPE
        and t.type     in (59, 62, 109)
        and t.prec     IS NOT NULL

   insert into #usertype
      select 'real',
             convert (int, t.allownulls),
             ' ',
             isnull (object_name (t.tdefault), '-'),
             isnull (object_name (t.domain), '-')
      from systypes t,
           systypes t2
      where t.usertype  = @USERTYPE
        and t.type      = 59
        and t.length    = 4
        and t.prec     IS NULL

   update #usertype
   set nulltext = substring (vB.name, 1, 4)
   from #usertype u,
        sybsystemprocs.dbo.usptvalues vB
   where vB.type   = 'B'
     and vB.number = u.nullflag

   select @btlen   = char_length (basetype),
          @dfltlen = char_length (dflttext),
          @rulelen = char_length (ruletext)
   from #usertype

   select @cmdstring = 'select ''BASE TYPE'' = substring (basetype, 1,'
                       +  convert (varchar (2), @btlen)
                       +  '),'
                       +  '''NULL'' = substring (nulltext, 1, 4),'
                       +  '''DEFAULT'' = substring (dflttext, 1,'
                       +  convert (varchar (2), @dfltlen)
                       +  '),'
                       +  '''RULE'' = substring (ruletext, 1,'
                       +  convert (varchar (2), @rulelen)
                       +  ')'
                       +  ' from #usertype'

   execute  (@cmdstring)

   print ' '

   insert into #datatype
      select o.id,
             (o.sysstat & 15),
             o.name,
             c.colid,
             c.name,
             o.uid,
             user_name (o.uid),
             substring (o.name + '.' + c.name, 1, 36),
             c.length,
             convert (int, convert (bit, (c.status & 8))),
             ' ',
             c.cdefault,
             NULL,
             c.domain,
             NULL
      from syscolumns c,
           sysobjects o
      where c.usertype         = @USERTYPE
        and c.id               = o.id
        and (o.sysstat & 15)  in (1, 2, 3)
go
sp_existsprocedure sp_datatype_usertype
go

drop table #datatype
go

create procedure sp_datatype (@DATATYPE varchar (30),
                              @LEN      tinyint      = 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_datatype CANNOT BE RUN FROM WITHIN A TRANSACTION.'

         print ' '

         return 1
      end

   declare @HT         char (1),
           @LF         char (1),
           @cmdstring  varchar (255),
           @crlen      tinyint,
           @dfltlen    tinyint,
           @namelen    tinyint,
           @usertype   smallint

   create table #datatype
      (id        int           not null,
       sysstat   smallint      not null,
       objname   varchar (30)  not null,
       colid     tinyint       not null,
       colname   varchar (30)  not null,
       uid       int           not null,
       owner     varchar (30)  not null,
       prntname  varchar (36)  not null,
       len       tinyint       not null,
       nullflag  int           not null,
       nulltext  char (4)      not null,
       dflt      int           null,
       dflttext  varchar (30)  null,
       domain    int           null,
       crtext    varchar (70)  null)

   select @HT       = char (9),                       -- Horizontal Tab
          @LF       = char (10),                      -- Line Feed
          @usertype = usertype
   from systypes
   where name = @DATATYPE

   if ((@LEN IS NOT NULL)  and
       (@DATATYPE in ('bit', 'datetime', 'double precision', 'image', 'int',
                      'money', 'real', 'smalldatetime', 'smallint', 'smallmoney',
                      'sysname', 'text', 'timestamp', 'tinyint')))
      begin
         print '    DO NOT SPECIFY A LENGTH/PRECISION (%1!) WITH DATATYPE %2!.',
               @LEN, @DATATYPE

         print ' '

         return 1
      end

   if ((@LEN IS NOT NULL)  and  (@usertype > 99))
      begin
         print '    DO NOT SPECIFY A LENGTH/PRECISION (%1!) WITH USERTYPE %2!.',
               @LEN, @DATATYPE

         print ' '

         return 1
      end

   if (@LEN IS NULL)
      begin
         print '    DATATYPE: %1!', @DATATYPE
      end
   else
      begin
         print '    DATATYPE: %1! (%2!)', @DATATYPE, @LEN
      end

   print ' '

   if (@DATATYPE in ('binary', 'char', 'varbinary', 'varchar'))
      begin
         insert into #datatype
            select o.id,
                   (o.sysstat & 15),
                   o.name,
                   c.colid,
                   c.name,
                   o.uid,
                   user_name (o.uid),
                   substring (o.name + '.' + c.name, 1, 36),
                   c.length,
                   convert (int, convert (bit, (c.status & 8))),
                   ' ',
                   c.cdefault,
                   NULL,
                   c.domain,
                   NULL
            from syscolumns c,
                 sysobjects o
            where c.usertype        = @usertype
              and ((c.length = @LEN)  or  (@LEN IS NULL))
              and c.id              = o.id
              and (o.sysstat & 15) in (1, 2, 3)

         if (not exists (select *
                         from #datatype))
            begin
               print '    DATATYPE %1! (%2!) IS NOT USED IN ANY TABLE/VIEW.',
                     @DATATYPE, @LEN

               print ' '
            end
      end
   else if (@DATATYPE in ('nchar', 'nvarchar'))
      begin
         insert into #datatype
            select o.id,
                   (o.sysstat & 15),
                   o.name,
                   c.colid,
                   c.name,
                   o.uid,
                   user_name (o.uid),
                   substring (o.name + '.' + c.name, 1, 36),
                   c.length / @@ncharsize,
                   convert (int, convert (bit, (c.status & 8))),
                   ' ',
                   c.cdefault,
                   NULL,
                   c.domain,
                   NULL
            from syscolumns c,
                 sysobjects o
            where c.usertype        = @usertype
              and ((c.length = @LEN)  or  (@LEN IS NULL))
              and c.id              = o.id
              and (o.sysstat & 15) in (1, 2, 3)

         if (not exists (select *
                         from #datatype))
            begin
               print '    DATATYPE %1! (%2!) IS NOT USED IN ANY TABLE/VIEW.',
                     @DATATYPE, @LEN

               print ' '
            end
      end
   else if (@DATATYPE in ('bit', 'datetime', 'image', 'money', 'smalldatetime',
                          'smallmoney', 'sysname', 'text', 'timestamp'))
      begin
         insert into #datatype
            select o.id,
                   (o.sysstat & 15),
                   o.name,
                   c.colid,
                   c.name,
                   o.uid,
                   user_name (o.uid),
                   substring (o.name + '.' + c.name, 1, 36),
                   c.length,
                   convert (int, convert (bit, (c.status & 8))),
                   ' ',
                   c.cdefault,
                   NULL,
                   c.domain,
                   NULL
            from syscolumns c,
                 sysobjects o
            where c.usertype        = @usertype
              and c.id              = o.id
              and (o.sysstat & 15) in (1, 2, 3)

         if (not exists (select *
                         from #datatype))
            begin
               print '    DATATYPE %1! IS NOT USED IN ANY TABLE/VIEW.', @DATATYPE

               print ' '
            end
      end
   else if (@DATATYPE in ('decimal', 'numeric'))
      begin
         execute  sp_datatype_decnum  @USERTYPE=@usertype, @PREC=@LEN

         if (not exists (select *
                         from #datatype))
            begin
               print '    DATATYPE %1! (%2!) IS NOT USED IN ANY TABLE/VIEW.',
                     @DATATYPE, @LEN

               print ' '
            end
      end
   else if (@DATATYPE = 'double precision')
      begin
         execute  sp_datatype_dp

         if (not exists (select *
                         from #datatype))
            begin
               print '    DATATYPE <double precision> IS NOT USED IN ANY TABLE/VIEW.'

               print ' '
            end
      end
   else if (@DATATYPE = 'float')
      begin
         select @usertype = -1

         execute  sp_datatype_float  @PREC=@LEN

         if (not exists (select *
                         from #datatype))
            begin
               print '    DATATYPE <float (%1!)> IS NOT USED IN ANY TABLE/VIEW.',
                     @LEN

               print ' '
            end
      end
   else if (@DATATYPE = 'int')
      begin
         execute  sp_datatype_int

         if (not exists (select *
                         from #datatype))
            begin
               print '    DATATYPE <int> IS NOT USED IN ANY TABLE/VIEW.'

               print ' '
            end
      end
   else if (@DATATYPE = 'real')
      begin
         execute  sp_datatype_real

         if (not exists (select *
                         from #datatype))
            begin
               print '    DATATYPE <real> IS NOT USED IN ANY TABLE/VIEW.'

               print ' '
            end
      end
   else if (@DATATYPE = 'smallint')
      begin
         execute  sp_datatype_smallint

         if (not exists (select *
                         from #datatype))
            begin
               print '    DATATYPE <smallint> IS NOT USED IN ANY TABLE/VIEW.'

               print ' '
            end
      end
   else if (@DATATYPE = 'tinyint')
      begin
         execute  sp_datatype_tinyint

         if (not exists (select *
                         from #datatype))
            begin
               print '    DATATYPE <tinyint> IS NOT USED IN ANY TABLE/VIEW.'

               print ' '
            end
      end
   else if (@usertype > 99)
      begin
         execute  sp_datatype_usertype  @USERTYPE=@usertype

         if (not exists (select *
                         from #datatype))
            begin
               print '    USERTYPE %1! IS NOT USED IN ANY TABLE/VIEW.', @DATATYPE

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

         print ' '
      end

   if (exists (select *
               from #datatype))
      begin
         if (@LEN IS NOT NULL)
            begin
               delete from #datatype
               where len <> @LEN
            end

         update #datatype
         set nulltext = substring (vB.name, 1, 4)
         from sybsystemprocs.dbo.usptvalues vB
         where vB.type   = 'B'
           and vB.number = nullflag

         update #datatype
         set prntname = substring (prntname + ' (' + owner + ')', 1, 36)
         where #datatype.uid <> 1
            or 1              < (select count (distinct o.uid)
                                 from sysobjects o
                                 where o.name           = #datatype.objname
                                   and (o.sysstat & 15) = #datatype.sysstat)

         update #datatype
         set dflttext = ltrim (substring (c.text, 8, 30))
         from syscomments c,
              sysprocedures p
         where #datatype.dflt    = p.id
           and p.sequence        = 1
           and (p.status & 4096) = 4096
           and c.id              = p.id
           and c.colid           = 1
           and c.texttype        = 0

         update #datatype
         set dflttext = substring (dflttext, 2, 50)
         where substring (dflttext, 1, 1) in (@HT, @LF)

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

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

         update #datatype
         set dflttext = object_name (dflt)
         from sysprocedures p
         where #datatype.dflt     = p.id
           and p.sequence         = 1
           and (p.status & 4096) <> 4096

         update #datatype
         set dflttext = '-'
         where dflttext IS NULL

         update #datatype
         set crtext = substring (c.text, 1, 70)
         from syscomments c,
              sysprocedures p
         where #datatype.domain  = p.id
           and p.sequence        = 1
           and (p.status & 4096) = 4096
           and c.id              = p.id
           and c.colid           = 1
           and c.colid2          = 0
           and c.texttype        = 0

         update #datatype
         set crtext = substring (crtext, 2, 70)
         where substring (crtext, 1, 1) in (@HT, @LF)

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

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

         update #datatype
         set crtext = object_name (domain)
         from sysprocedures p
         where #datatype.domain   = p.id
           and p.sequence         = 1
           and (p.status & 4096) <> 4096

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

         select @namelen = max (char_length (prntname)),
                @dfltlen = max (char_length (dflttext)),
                @crlen   = max (char_length (crtext))
         from #datatype

         if (@namelen < 13)
            begin
               select @namelen = 13
            end

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

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

         select @cmdstring = 'select ''OBJECT.COLUMN'' = substring (prntname, 1,'
                             +  convert (varchar (2), @namelen)
                             +  '),'

         if ((@LEN IS NULL)  and  (@usertype in (1, 2, 3, 4, 24, 25)))
            begin
               select @cmdstring = @cmdstring  +  'SIZE = len,'

               if ((@namelen + @dfltlen + @crlen) > 65)
                  begin
                     select @crlen = 65 - @namelen - @dfltlen
                  end
            end
         else
            begin
               if ((@namelen + @dfltlen + @crlen) > 70)
                  begin
                     select @crlen = 70 - @namelen - @dfltlen
                  end
            end

         select @cmdstring = @cmdstring
                             +  '''NULL'' = nulltext,'
                             +  '''DEFAULT'' = substring (dflttext, 1,'
                             +  convert (varchar (2), @dfltlen)
                             +  '),'
                             +  '''CHECK / RULE'' = substring (crtext, 1,'
                             +  convert (varchar (2), @crlen)
                             +  ')'
                             +  ' from #datatype'
                             +  ' order by objname, owner, colid'

         execute  (@cmdstring)
      end
go
sp_addcomment sp_datatype, 'Lists columns of type DATATYPE and (optional) LENGTH/PRECISION'
go
sp_procxmode sp_datatype, anymode
go
