/*
 * SQL_UDF_COLLECTION.SQL
 * 
 * Description
 * ===========
 * This file contains some SQL UDFs (User-Defined Functions) 
 * which may be useful to ASE users.
 * The purpose of this script is primarily to get you started
 * with some examples of SQL UDFs; please use the examples as
 * a basis for your own UDFs.
 *
 * If you want to share UDFs which you've developed yourself, 
 * please contact rob@sypron.nl.
 *
 *
 * Installation
 * ============
 * Execute this script using "isql", using a login having 'sa_role'.
 * The functions will be created in the sybsystemprocs database, and 
 * can be called from any database since their names all start with "sp_".
 * For more details about this naming convention, 
 * see http://www.sypron.nl/quiz2009a.html#jul09.
 *
 *
 * Contents
 * =======-
 * This script contains the following example SQL functions:
 *
 *   sp_datefmt_robv()
 *   sp_temperature_c2f()
 *   sp_temperature_f2c()
 *   sp_fmt_int_zero()
 *   sp_secs_to_interval()
 *   sp_logrectype()
 *   sp_str_replace2()
 *   sp_object_type()
 *   sp_object_type_str()
 *   sp_decode_colidarray()
 *   sp_range()
 *
 * For details about each function, see the source code and the 
 * examples in this script (search for 'Examples' below).
 *
 *
 * ASE version compatibility
 * =========================
 * This script requires ASE 15.0.2 or later, since SQL UDFs are only 
 * supported as of this version.
 *
 *
 * Revision History
 * ================
 * Version 1.0    Jan-2010  First version
 * Version 1.1    Nov-2010  Added Robert Munson's 'sp_range'
 * Version 1.2    Dec-2011  Added log record types for ASE 15.7
 * 
 *
 * Copyright Note & Disclaimer :
 * =============================
 * This software is provided "as is"; there is no warranty of any kind.
 * While this software is believed to work accurately, it may not work 
 * correctly and/or reliably in a production environment. In no event shall  
 * Rob Verschoor and/or Sypron B.V. be liable for any damages resulting 
 * from the use of this software. 
 * You are allowed to use this software free of charge for your own 
 * professional, non-commercial purposes. 
 * You are not allowed to sell or bundle this software or use it for any 
 * other commercial purpose without prior written permission from 
 * Rob Verschoor/Sypron B.V.
 * You may (re)distribute only unaltered copies of this software, which 
 * must include this copyright note.
 *
 * Note: All trademarks are acknowledged.
 *
 * Please send any comments, bugs, suggestions etc. to the below email
 * address.
 *
 * Copyright (c) 2008-2010 Rob Verschoor/Sypron B.V.
 *                         Email: sypron@sypron.nl
 *                         WWW  : http://www.sypron.nl/
 *
 * The Copyright Note & Disclaimer above do not apply to material in this file 
 * that was provided by third parties, as indicated. Applicable disclaimers
 * are provided with those materials. 
 *---------------------------------------------------------------------------- 
 */

-- We should only run this against ASE 15.0.2 or later
set flushmessage on
go

-- We need to be in 15.0.2
-- First test for 15.0...
if isnull(object_id("master.dbo.syspartitionkeys"),99) >= 99  
begin
   print ""
   print ""
   print " This script requires ASE 15.0.2 or later."
   print " Aborting..."
   print " "
   print " "
   print ""
   select syb_quit()  -- terminate this script now
end
go

-- Now test for 15.0.2...
if @@version_number/10 < 1502
begin
   print ""
   print ""
   print " This script requires ASE 15.0.2 or later."
   print " Aborting..."
   print " "
   print " "
   print ""
   select syb_quit()  -- terminate this script now
end
go



-- Let's just assume sybsystemprocs exist... 
use sybsystemprocs
go

------------------------------------------------------------------------------
-- 
-- Function sp_datefmt_robv: formats a datetime value as dd-MMM-yyyy
-- My default example of SQL UDFs: this is my favorite date format, avoiding 
-- any and all ambiguities.
-- 
if object_id('sp_datefmt_robv') <> NULL
begin
   drop function sp_datefmt_robv
end
go

print "Installing SQL Function 'sp_datefmt_robv()' in 'sybsystemprocs' database..."
go

create function sp_datefmt_robv(@d datetime) 
returns char(11) 
as 
   return str_replace(convert(char(11), @d, 106), ' ', '-')
go

dump tran sybsystemprocs with truncate_only
go


/* 
 * Examples of using this SQL function:

select dbo.sp_datefmt_robv(getdate())

select name, dbo.sp_datefmt_robv(crdate) from sysobjects

 *
 */



------------------------------------------------------------------------------
-- 
-- Function sp_temperature_c2f: converts temperature from degrees Celsius to Fahrenheit
-- 
if object_id('sp_temperature_c2f') <> NULL
begin
   drop function sp_temperature_c2f
end
go

print "Installing SQL Function 'sp_temperature_c2f()' in 'sybsystemprocs' database..."
go

create function sp_temperature_c2f (@c int)
returns float
as 
  return ( (@c*9/5.0)+32 )
go

-- shorthand....
if object_id('sp_c2f') <> NULL
begin
   drop function sp_c2f
end
go

create function sp_c2f (@c int)
returns float
as 
  return dbo.sp_temperature_c2f (@c)
go


/* 
 * Examples of using this SQL function:

select dbo.sp_temperature_c2f (20)

 *
 */


------------------------------------------------------------------------------
-- 
-- Function sp_temperature_f2c: converts temperature from degrees Fahrenheit to Celsius
-- 
if object_id('sp_temperature_f2c') <> NULL
begin
   drop function sp_temperature_f2c
end
go

print "Installing SQL Function 'sp_temperature_f2c()' in 'sybsystemprocs' database..."
go

create function sp_temperature_f2c (@f int)
returns float
as 
  return ( (@f-32) * 5/9.0 )
go

-- shorthand....
if object_id('sp_f2c') <> NULL
begin
   drop function sp_f2c
end
go

create function sp_f2c (@f int)
returns float
as 
  return dbo.sp_temperature_f2c (@f)
go




/* 
 * Examples of using this SQL function:

select dbo.sp_temperature_f2c (95)

 *
 */



------------------------------------------------------------------------------
-- 
-- Function sp_fmt_int_zero: formats an integer value into a string with leading zeroes
-- 

if object_id('sp_fmt_int_zero') <> NULL
begin
   drop function sp_fmt_int_zero
end
go

print "Installing SQL Function 'sp_fmt_int_zero()' in 'sybsystemprocs' database..."
go
create function sp_fmt_int_zero (@i bigint, @len int)
returns varchar(30)
as
   declare @v varchar(30)
   select @v = convert(varchar,abs(@i))
   if len(@v) > @len 
      return replicate('*', @len)
   return right(replicate('0',@len) + @v, @len)
go

dump tran sybsystemprocs with truncate_only
go



/* 
 * Examples of using this SQL function:

select dbo.sp_fmt_int_zero (123, 5)

select dbo.sp_fmt_int_zero (123, 3)

select dbo.sp_fmt_int_zero (12345, 2)

select dbo.sp_fmt_int_zero (1234567890123456789, 12)

select dbo.sp_fmt_int_zero (123, 10)

select dbo.sp_fmt_int_zero (0, 10)

select dbo.sp_fmt_int_zero (-123, 10)

 *
 */



------------------------------------------------------------------------------
-- 
-- Function sp_secs_to_interval: converts a number of seconds to a string formatted 'hh:mm:ss'
-- 

if object_id('sp_secs_to_interval') <> NULL
begin
   drop function sp_secs_to_interval
end
go

print "Installing SQL Function 'sp_secs_to_interval()' in 'sybsystemprocs' database..."
go

create function sp_secs_to_interval (@secs int)
returns varchar(25)
-- when @secs < 0, format = 'y d h m s'; else 'y d hh:mm:ss'
as
declare @ss int, @mm int, @hh int, @dd int, @yy int, @sign int, @v varchar(25)

   select @sign = @secs
   select @secs = abs(isnull(@secs,0))
   select @ss = @secs%60
   select @mm = @secs/60
   select @hh = @mm/60
   select @dd = @hh/24
   select @yy = @dd/365

   select @mm = @mm - (@hh * 60)
   select @hh = @hh - (@dd * 24)
   select @dd = @dd - (@yy * 365)

   if @sign >= 0
      select @v = 
           case when @yy = 0 then NULL else convert(varchar, @yy) + 'y ' end 
         + case when @yy + @dd = 0 then NULL else convert(varchar, @dd) + 'd ' end
         + dbo.sp_fmt_int_zero(@hh,2) + ':'
         + dbo.sp_fmt_int_zero(@mm,2) + ':'
         + dbo.sp_fmt_int_zero(@ss,2)
   else
      select @v = 
           case when @yy = 0 then NULL else convert(varchar, @yy) + 'y ' end
         + case when @yy + @dd = 0 then NULL else convert(varchar, @dd) + 'd ' end
         + convert(varchar, @hh) + 'h '
         + convert(varchar, @mm) + 'm '
         + convert(varchar, @ss) + 's '

   return @v
go

dump tran sybsystemprocs with truncate_only
go


/* 
 * Examples of using this SQL function:

select dbo.sp_secs_to_interval(12)

select dbo.sp_secs_to_interval(123)

select dbo.sp_secs_to_interval(1234)

select dbo.sp_secs_to_interval(1234567)

select dbo.sp_secs_to_interval(123456789)

select dbo.sp_secs_to_interval(-12)

select dbo.sp_secs_to_interval(-123)

select dbo.sp_secs_to_interval(-1234)

select dbo.sp_secs_to_interval(-123456789)


select Servername = @@servername, ServerUpTime = dbo.sp_secs_to_interval(-1*datediff(ss, @@boottime, getdate()))

 *
 */


------------------------------------------------------------------------------
-- 
-- Function sp_logrectype: translates syslogs.op into description of the log record type
-- 
if object_id('sp_logrectype') <> NULL
begin
   drop function sp_logrectype
end
go

print "Installing SQL Function 'sp_logrectype()' in 'sybsystemprocs' database..."
go

create function sp_logrectype (@op int)
returns varchar(70)
as 
  if @op = 0 return "BEGINXACT: Implicit/explicit BEGIN TRAN"
  if @op = 1 return "CHSYSIND"
  if @op = 2 return "SWITCHTO"
  if @op = 3 return "RETURN"
  if @op = 4 return "INSERT: Data page INSERT"
  if @op = 5 return "DELETE: Data page DELETE"
  if @op = 6 return "INSIND: INSERT by deferred INSERT/UPDATE (read insert from log)"
  if @op = 7 return "IINSERT: Index page INSERT"
  if @op = 8 return "IDELETE: Index page DELETE"
  if @op = 9 return "MODIFY: Direct UPDATE in-place"
  if @op = 10 return "NOOP: NOOP RECORD, skipped by recovery"
  if @op = 11 return "INOOP: First phase: deferred INSERT/UPDATE (write insert to log)"
  if @op = 12 return "DNOOP: First phase: deferred UPDATE/DELETE (write RowID to log)"
  if @op = 13 return "ALLOC: Page allocation record"
  if @op = 14 return "DBNEXTID: Next objectid that can be used to create object"
  if @op = 15 return "EXTENT: Obsolete"
  if @op = 16 return "SPLIT: Split of data page"
  if @op = 17 return "CHECKPOINT: Checkpoint record of checkpoint proces"
  if @op = 18 return "SAVEXACT: Logged by SAVE TRAN command"
  if @op = 19 return "CMD: Logged when any update begins"
  if @op = 20 return "SOPGDEALLOC: Sort page deallocation"
  if @op = 21 return "DEALLOC: Deallocate page"
  if @op = 22 return "DROPEXTS: Delete all extents on allocation page"
  if @op = 23 return "AEXTENT: Allocate extent, mark all pages used"
  if @op = 24 return "SALLOC: Alloc for new page of split"
  if @op = 25 return "PGCHGSYSIND: Direct change to sysindexes"
  if @op = 26 return "CLR: Compensation Log Record"
  if @op = 27 return "SORT: Sort record type"
  if @op = 28 return "SODEALLOC: Sort record deallocate type"
  if @op = 29 return "ALTDB"
  if @op = 30 return "ENDXACT: End of transaction (COMMIT/ROLLBACK)"
  if @op = 31 return "SORTTS: Sort timestamp record"
  if @op = 32 return "TEXT: Direct TEXT insert"
  if @op = 33 return "INOOPTEXT: Deferred TEXT insert"
  if @op = 34 return "DNOOPTEXT: Deferred TEXT delete"
  if @op = 35 return "INSINDTEXT: Indirect insert log record"
  if @op = 36 return "TEXTDELETE: Delete text log record"
  if @op = 37 return "OAMALLOC: Allocate page to OAM"
  if @op = 38 return "OAMDEALLOC: Deallocate OAM-page"
  if @op = 39 return "OAMINSERT: Insert new entry in OAM"
  if @op = 40 return "OAMDELETE: Delete entry from OAM"
  if @op = 41 return "OAMCREATE: Alloc of OAM during create or sort"
  if @op = 42 return "CMDNOOP: Noop'ed CMD-record"
  if @op = 43 return "EXECBEGIN: Rep. server support : begin stored proc"
  if @op = 44 return "EXECEND: Rep. server support : stored proc complete"
  if @op = 45 return "SYNCDPDB: Rep. server support : dump database sync."
  if @op = 46 return "TRUNC: Saved truncation point"
  if @op = 47 return "SYNCLDDB: Load database sync."
  if @op = 48 return "SYNCLDXACT: Load transaction sync."
  if @op = 49 return "ROWIMAGE"
  if @op = 51 return "OAMENTRYMOV: Move OAM-entries between OAM-pages"
  if @op = 52 return "OAMATPUT: Record attribute data on OAM-page"
  if @op = 53 return "DROPCOL: Delete column from row"
  if @op = 54 return "LOGDEALLOC: log page deallocation"
  if @op = 55 return "CTRLPGMODIFY: control page modification"
  if @op = 56 return "DEFINDROW"
  if @op = 57 return "DISTMAP"
  if @op = 58 return "OBJINFO"
  if @op = 59 return "COLINFO"
  if @op = 60 return "PREPARE"
  if @op = 61 return "BEGINTOPACTION: start of nested top action"
  if @op = 62 return "ENDTOPACTION: end of nested top action"
  if @op = 63 return "DOL_INSERT: DOL table insert"
  if @op = 64 return "DOL_INSIND: DOL table index insert"
  if @op = 65 return "DOL_UPDATE: DOL table update"
  if @op = 66 return "DOL_DELETE: DOL table delete"
  if @op = 67 return "DOL_GC: DOL table Garbage Collection"
  if @op = 68 return "DOL_UNOOP"
  if @op = 69 return "ALLOC_NOLINK"
  if @op = 70 return "DOL_DNOOP"
  if @op = 71 return "BT_INSERT: B-tree insert"
  if @op = 72 return "BT_DELETE: B-tree delete"
  if @op = 73 return "BT_SPLIT: B-tree delete"
  if @op = 74 return "BT_GC: B-tree Garbage Collection"
  if @op = 75 return "SYSCATALOG_ROW"
  if @op = 76 return "ENDSORT"
  if @op = 77 return "DROPEXTSMAP"
  if @op = 78 return "BT_REBLDLEAFS"
  if @op = 79 return "BT_BATCHDELETE"
  if @op = 80 return "BT_BATCHINSERT"
  if @op = 81 return "DEALLOCM"
  if @op = 82 return "PFTS: Page Flush Timestamp"  
  if @op = 83 return "XREC_RESTAMP_PAGE: Re-stamp all pages in a partition on a specific allocation unit"  
  if @op = 84 return "XREC_ANCHOR: Modify a system table anchor (sysanchors)"  
  if @op = 85 return "XREC_PAGEIMAGE: page image for fully recoverable DDL (can be compressed)"  
  if @op = 86 return "XREC_PAGE_COMPRESS: data compression for a page"  
  return "Unknown log record type: " + convert(varchar,@op)
go


dump tran sybsystemprocs with truncate_only
go


/* 
 * Examples of using this SQL function:

select dbo.sp_logrectype(op) from syslogs

select op, dbo.sp_logrectype(op), count(*) from syslogs 
group by op
order by 3

 *
 */

------------------------------------------------------------------------------

-- This function is called by sp_str_replace2()

if object_id('sp_strrpl2_charindex_times') <> NULL
begin
   drop function sp_strrpl2_charindex_times
end
go

print "Installing SQL Function 'sp_strrpl2_charindex_times()' in 'sybsystemprocs' database..."
go

create function sp_strrpl2_charindex_times (@srch varchar(255), @c varchar(16384))
returns int
as
   -- return the number of times @srch occurs in @c
   declare @len_diff smallint, @times smallint

   select @len_diff = len(@c) - len(str_replace(@c, @srch, NULL))
   select @times = @len_diff / len(@srch)
   return @times
go

dump tran sybsystemprocs with truncate_only
go

------------------------

-- This function is called by sp_str_replace2()

if object_id('sp_strrpl2_charindex_n') <> NULL
begin
   drop function sp_strrpl2_charindex_n
end
go

print "Installing SQL Function 'sp_strrpl2_charindex_n()' in 'sybsystemprocs' database..."
go

create function sp_strrpl2_charindex_n (@srch varchar(255), @c varchar(16384), @n int)
returns int
as
   -- enhanced version of charindex() : 
   -- when N > 0, locates Nth occurence of @srch in @c
   -- when N < 0, locates Nth occurence of @srch in @c, counting from the end of @c

-- remaining issues: NULL below is not returned
   if abs(@n) < 1 return NULL

   declare @c_len_orig smallint, @c_len smallint, @srch_len smallint, @ix smallint, @ix_c smallint, @found smallint

   if @n < 0 
   begin
      select @c = reverse(@c)
      select @srch = reverse(@srch)
      select @c_len_orig = len(@c)
   end

   select @ix_c = 0
   select @srch_len = len(@srch)
   select @found = 0
   
   while 1=1
   begin
      select @c_len = len(@c)
      select @ix = charindex(@srch, @c)

      if @ix = 0 break

      select @found = @found + 1
      if @found = abs(@n) break

      -- found but not ready
      select @c = right(@c, @c_len - @ix - @srch_len + 1)
      select @ix_c = @ix_c + @ix + @srch_len - 1
   end

   select @ix_c = @ix_c + @ix
   if @n < 0 
      select @ix_c = @c_len_orig - (@ix_c + @srch_len - 2)

   if @found = abs(@n) return @ix_c
   else return 0

   return 0 -- shouldn't be needed but is currently required
go

dump tran sybsystemprocs with truncate_only
go

------------------------

-- This function is called by sp_str_replace2()

if object_id('sp_strrpl2_cut_first') <> NULL
begin
   drop function sp_strrpl2_cut_first
end
go

print "Installing SQL Function 'sp_strrpl2_cut_first()' in 'sybsystemprocs' database..."
go

create function sp_strrpl2_cut_first (@c varchar(16384), @n smallint)
returns varchar(16384)
as
  -- cut off first N chars of a string and return the rest
  if @n < 0 return NULL
  if @n is NULL return NULL
  return right(@c, len(@c) - @n)
go

dump tran sybsystemprocs with truncate_only
go

------------------------

-- This function is called by sp_str_replace2()

if object_id('sp_strrpl2_cut_last') <> NULL
begin
   drop function sp_strrpl2_cut_last
end
go

print "Installing SQL Function 'sp_strrpl2_cut_last()' in 'sybsystemprocs' database..."
go

create function sp_strrpl2_cut_last (@c varchar(16384), @n smallint)
returns varchar(16384)
as
  -- cut off last N chars of a string and return the rest
  return left(@c, len(@c) - @n)
go

dump tran sybsystemprocs with truncate_only
go


------------------------
-- 
-- Function sp_str_replace2: a better version of str_replace(). 
-- See the examples below and the comments in the code.
-- 

if object_id('sp_str_replace2') <> NULL
begin
   drop function sp_str_replace2
end
go

print "Installing SQL Function 'sp_str_replace2()' in 'sybsystemprocs' database..."
go

create function sp_str_replace2(@c varchar(16384), @old varchar(255), @new varchar(255), 
                             @first smallint=NULL, @times smallint=NULL)
returns varchar(16384)
as
   -- @first (> 0): the Nth occurrence to start replacing; default=1
   -- @times (>=0): the max# of replacements to perform. Default = 0 or NULL (= all)
   -- @times (<0):stop replacing at Nth occurrence counting backwards from the end
   if @first = 0 select @first = 1
   if @first is NULL select @first = 1
   if @times is NULL select @times = 0
   if @first < 0 and @times < 0 return NULL

   declare @ix_start smallint, @ix_end smallint
   declare @prefix varchar(16384), @postfix varchar(16384)
   declare @last smallint, @tmp smallint


   if @first < 0 and @times = 0
   begin
       select @times = @first + 1
       select @first = 1
   end

   select @ix_start = dbo.sp_strrpl2_charindex_n(@old, @c, @first)
   if @first < 0
       select @ix_start = @ix_start + len(@old)
   
   if @times < 0
      select @last = @times
   else
      if @times > 0
         if @first < 0
            select @last = @first - (@times - 1)
         else
            select @last = @first + @times 
      else
         select @last = @first + 16384 -- make sure it exceeds the full string

   select @ix_end = dbo.sp_strrpl2_charindex_n(@old, @c, @last)
   if @first < 0
   begin
      select @tmp = @ix_start
      select @ix_start = @ix_end
      select @ix_end = @tmp
   end

   select @prefix = left(@c, @ix_start - 1)

   if @ix_end > 0
   begin
      select @postfix = right(@c, len(@c) - @ix_end + 1)
      select @c = dbo.sp_strrpl2_cut_last(@c, len(@c) - @ix_end + 1)
   end
   select @c = dbo.sp_strrpl2_cut_first(@c, @ix_start - 1)

   select @c = @prefix + str_replace(@c, @old, @new) + @postfix

-- we should still check for 16384 overflow somewhere....!

   return @c
go

dump tran sybsystemprocs with truncate_only
go


/* 
 * Examples of using this SQL function:

-- 'start replacing at the Nth'
select dbo.sp_str_replace2('a b c d e f', ' ', '-', 1, 0)

select dbo.sp_str_replace2('a b c d e f', ' ', '-', 2, 0)

select dbo.sp_str_replace2('a b c d e f', ' ', '-', 3, 0)

select dbo.sp_str_replace2('a b c d e f', ' ', '-', 2, 1)

 
-- 'replace until N from the end'
select dbo.sp_str_replace2('a b c d e f', ' ', '-',1,-1)

select dbo.sp_str_replace2('a b c d e f', ' ', '-',2,-1)

select dbo.sp_str_replace2('a b c d e f', ' ', '-',2,-2)

select dbo.sp_str_replace2('a b c d e f', ' ', '-',2,-3)


-- 'replace only the last N'
select dbo.sp_str_replace2('a b c d e f', ' ', '-',-1,1)

select dbo.sp_str_replace2('a b c d e f', ' ', '-',-1,2)

select dbo.sp_str_replace2('a b c d e f', ' ', '-',-2,1)

select dbo.sp_str_replace2('a b c d e f', ' ', '-',-3,1)

 *
 */


------------------------
-- 
-- Function sp_object_type: returns the type of an object; 
-- this can save you from writing a join with sysobjects
-- 

if object_id('sp_object_type') <> NULL
begin
   drop function sp_object_type
end
go

print "Installing SQL Function 'sp_object_type()' in 'sybsystemprocs' database..."
go

create function sp_object_type(@id int)
returns varchar(2)
as
begin
   return (select type from sysobjects where id = @id)
end
go

dump tran sybsystemprocs with truncate_only
go


/* 
 * Examples of using this SQL function:

-- show existing indexes for user tables only:
select TabIndexName= object_name(id) + '.' + name 
from sysindexes where dbo.sp_object_type(id) = 'U' and indid > 0

 *
 */



------------------------
-- 
-- Function sp_object_type_str: returns the type of an object, but more verbose than sp_object_type
-- This can be useful for tools or reporting or if you don't want to decode 
-- status bits in sysobjects (see the example below)
-- 

if object_id('sp_object_type_str') <> NULL
begin
   drop function sp_object_type_str
end
go

print "Installing SQL Function 'sp_object_type_str()' in 'sybsystemprocs' database..."
go

create function sp_object_type_str(@id int)
returns varchar(30)
as
begin
   declare @type_str varchar(30), @sysstat2 int, @type varchar(2)

	select @type = type, @sysstat2 = sysstat2 
	from sysobjects 
	where id = @id

   if @type = 'C'  set @type_str = 'Computed Column'
   if @type = 'D'  set @type_str = 'Default'
   if @type = 'EK' set @type_str = 'Encryption Key'
   if @type = 'DD' set @type_str = 'Decrypt Default'
   if @type = 'F'  set @type_str = 'SQLJ (Java) Function'
   if @type = 'N'  set @type_str = 'Partition Condition'
   if @type = 'R'  set @type_str = 'Rule'
   if @type = 'RI' set @type_str = 'Referential Integrity Constraint'
   if @type = 'S'  set @type_str = 'System Table (Catalog)'
   if @type = 'TR' set @type_str = 'Trigger (on a table)'
   if @type = 'IT' set @type_str = 'Instead-Of Trigger (on a view)'
   if @type = 'V'  set @type_str = 'View'
   if @type = 'XP' set @type_str = 'Extended Stored Procedure'
   if @type = 'SF' set @type_str = 'SQL Function (SQL UDF)'

   if @type = 'P' 
	begin
		if @sysstat2 & 33554432 > 0  
			set @type_str = 'SQLJ (Java) procedure'
		else
			set @type_str = 'Stored Procedure'
	end

   if @type = 'U' 
	begin
		if @sysstat2 & 3072 = 1024
			set @type_str = 'Proxy Table' --  created with sp_addobjectdef + 'create table'
		else
			if  @sysstat2 & 3072 = 3072
				set @type_str = 'Proxy Table' --  created with 'create existing table' or 'create proxy_table'
			else
				set @type_str = 'User Table'
	end

   return @type_str
end
go

dump tran sybsystemprocs with truncate_only
go


/* 
 * Examples of using this SQL function:

-- show existing proxy tables:
select name from sysobjects 
where dbo.sp_object_type_str(id) = 'Proxy Table'


-- show all SQL functions:
select name from sysobjects 
where dbo.sp_object_type_str(id) like 'SQL Func%'

 *
 */




------------------------
-- 
-- Function sp_decode_colidarray: Decoding the column IDs in systatistics..colidarray.
-- See http://www.sypron.nl/2009Q4_StatsDecode.pdf for more details. Note that the code below
-- is a better (and longer) version of the code in the PDF article above, which had to be 
-- kept brief to fit in the publication; the version in the PDF can only handle 
-- tables with < 255 columns.
-- Also see the examples below.
-- 

if object_id('sp_decode_colidarray') <> NULL
begin
   drop function sp_decode_colidarray
end
go

print "Installing SQL Function 'sp_decode_colidarray()' in 'sybsystemprocs' database..."
go

create function sp_decode_colidarray 
	@colidarray varbinary(100), @id int
	returns varchar (1500)
as
begin
	declare @s varchar (1500)	  -- assuming/hoping that will be long enough for the resulting string...
	declare @len int, @colid int, @colname longsysname

	if datalength(@colidarray)%2 = 1
	begin 
	    set @colidarray = @colidarray + 0x00
	end

	set @len = 1 
	while @len < datalength(@colidarray) 
	begin
		set @colid = convert(smallint, substring(@colidarray, @len, 2))
		set @colname = col_name(@id, @colid)
		set @s = @s + case @s when NULL then NULL else ', ' end + @colname 
		set @len = @len + 2
	end
	return @s
end
go

dump tran sybsystemprocs with truncate_only
go


/* 
 * Examples of using this SQL function:

-- decoding an array of column IDs:
select object_name(id), formatid, dbo.sp_decode_colidarray(colidarray, id)
from sysstatistics where formatid in (100, 102, 104, 110)


-- a stored proc to print information about 'missing statistics'
-- in ASE 15.0.3 ESD#1 (or later). First, enable the config
-- param 'capture missing statistics', then run this proc in
-- the databases where the application tables are located
-- Also see http://www.sypron.nl/2009Q4_StatsDecode.pdf

create proc sp_decode_missing_stats
	@tabname varchar (100) = '%'
as
begin
	select 
		DBname     = db_name(),
		Tabname    = object_name(s.id),
		NrRows     = row_count(db_id(), s.id),
		ColumnList = dbo.sp_decode_colidarray(colidarray, s.id),
		Captured   = moddate,
		Occurs     = convert (smallint,c0)
	into #missing
	from sysstatistics s, sysobjects o
	where s.id = o.id
	and object_name(s.id) like @tabname
	and formatid = 110
	and datalength(colidarray) > 0

	exec sp_autoformat #missing,@orderby='order by 2'
end

 *
 */


------------------------------------------------------------------------------

---- Material below is contributed by third parties; all rights remain with the respective author(s) --- 


if object_id('sp_range') <> NULL
begin
   drop function sp_range
end
go

print "Installing SQL Function 'sp_range()' in 'sybsystemprocs' database..."
go

create function sp_range ( @non_neg_int int )
  returns char(40) as
-- Author and owner Robert Munson copyright (c) 2010 @gmail.com, robtmunson
-- You are allowed to use this software free of charge for your own
-- professional, non-commercial purposes.
-- You are not allowed to sell or bundle this software or use it for any
-- other commercial purpose without prior written permission from
-- Robert L Munson.
-- You may (re)distribute only unaltered copies of this software, which
-- must include this copyright note.
-- This software is provided "as is," with no warranty as to suitability or reliability. No liabilty for damage.

-- Function: Passed a non negative integer, returns a character string of the encompassing range.
-- Example: select dbo.sp_range(304) returns "         200-499". Ranges always start with digits 0, 1, 2 or 5,
-- giving a roughly logarithmic increase in range sizes. Leading blanks are for sorting and presentation.

-- Use in group by and order by to get a statistical distribution of unknown data.
-- Select dbo.sp_range( cast(ContributionAmt as int)) as "Range",  -- How many donors and $
--   count(*) as "Donors", sum(ContributionAmt) as "Total Contributed"  -- by contribution amount
--   from Contributions group by dbo.sp_range(cast(ContributionAmt as int))
--   order by dbo.sp_range( cast(ContributionAmt as int))

return substring(space(11-char_length(convert(varchar,@non_neg_int))) +
    substring("0122255555?",
    charindex(substring(convert(varchar,@non_neg_int),1,1),"0123456789-"),1) +
    replicate("0",char_length(convert(varchar,@non_neg_int))-1) +"-" +
    substring("0144499999?",
    charindex(substring(convert(varchar,@non_neg_int),1,1),"0123456789-"),1) +
    replicate("9",char_length(convert(varchar,@non_neg_int))-1),1,24)
go

------------------------------------------------------------------------------
--
-- end of file
---