set flushmessage on
go

use sybsystemprocs
go

if exists (select 1
        from sysobjects
                where sysstat & 7 = 4
                        and name = 'sp__optdiag')
begin
        print "Dropping sp__optdiag"
        drop procedure sp__optdiag
end
go

print "Installing sp__optdiag"
go

create procedure sp__optdiag
	@tabname	varchar(62) = null,  /* user table name */
	@colname	varchar(30) = null,	/* column name */
	@option		varchar(60) = null		/* output format */
 , @proc_version varchar(78) = "sp__optdiag/0.4/0/P/KJS/AnyPlat/AnyOS/G/Fri Jan  5 14:56:32 2001"
as
/*************************************************************************************************
**
**	Description: Format opdiag info from stored procedure
**
**	Options:  NULL - default
**
**                "V/?/HELP/H" - will print the current version string of this proc
**                "CR" - will approximate cluster ratio calculations.  Note that these are simply
**                       simply approximations since cluster ratio calculations are not published.
**                       (future change, not supported yet)
**
**	Future Info:  Other options can be added in the future
**                    using the @option parameter.
**
**	Dependencies:  This proc relies on the object_id built-in
**                    and sp_namecrack
**
**	Errors:
**
**	Version:  This proc is for ASE 11.9.x and beyond
**
**	Usage:  exec <dbname>..sp__optdiag <tabname>, <colname>, <opt>
**
**	History: 10/31/2000 (ksherlock) 0.1
**                  Original
**               11/14/2000 (ksherlock) 0.2
**                  Fixed bug to handle binary histograms and handle user defined types
**               12/20/2000 (ksherlock) 0.3
**                  Fixed bug with column groups not being retrieved in col_cursor
**               01/05/2001 (ksherlock) 0.4
**                  Final version which handles numeric decimals correctly
**
*************************************************************************************************/

declare
        @colid int  /* Variable to hold colid from syscolumns */
      , @tabid int  /* Variable to hold object_id from sysobjects */
      , @tabtype char(2)  /* Variable to hold type from sysobjects */
      , @s_dbname varchar(30)
      , @s_tabowner varchar(30)
      , @s_tabname varchar(30)
      , @u_tabname varchar(30)
      , @u_tabowner varchar(30)
      , @colgroup_name varchar(255)
      , @u_dbname varchar(30)
      , @u_dbid int
      , @colidarray varbinary(100)
      , @colidarray_len smallint
      , @indid int
      , @index_cols varchar(254)
      , @index_name varchar(30)
      , @keycnt int
      , @dol_clustered int
      , @clustered int
      , @last_updt varchar(28)
      , @c1stat int
      , @statid smallint
      , @used_count int
      , @rownum int
      , @coltype int
      , @typename varchar(30)
      , @collength varchar(5)
      , @precision varchar(3)
      , @scale varchar(3)
      , @rc_density varchar(24)
      , @tot_density varchar(24)
      , @r_sel varchar(24)
      , @between_sel varchar(24)
      , @freq_cell smallint
      , @steps_act int
      , @steps_req int
      , @step char(9)
      , @weight char(10)
      , @prev_step char(9)
      , @prev_weight char(10)
      , @value_raw varbinary(255)
      , @value_c varchar(255)
      , @leafcnt varchar(32) -- int
      , @pagecnt varchar(32) -- int
      , @emptypgcnt varchar(32) -- int
      , @rowcnt varchar(32)
      , @forwrowcnt varchar(32)
      , @delrowcnt varchar(32)
      , @dpagecrcnt varchar(32)
      , @dpagecr varchar(32)
      , @ipagecrcnt varchar(32)
      , @ipagecr varchar(32)
      , @drowcrcnt varchar(32)
      , @drowcr varchar(32)
      , @oamapgcnt varchar(32) -- int
      , @extent0pgcnt varchar(32)
      , @datarowsize varchar(32)
      , @leafrowsize varchar(32)
      , @indexheight varchar(32) -- int
      , @spare1 varchar(32) -- int
      , @spare2 varchar(32)
      , @ptn_data_pgs int
      , @seq int


if @@trancount = 0
begin
	set chained off
end

set transaction isolation level 1
set nocount on
set flushmessage on

if ( (select lower(@option)) in ("v","version","?","h","help") )
begin
   print "%1!",@proc_version
   return 0
end

exec sp_namecrack @tabname, " ", @s_dbname out, @s_tabowner out, @s_tabname out
select @s_dbname = isnull(@s_dbname,db_name())

declare object_cursor cursor for
select  id,
        db_name(),
        db_id(),
        user_name(uid),
        name
from sysobjects
where user_name(uid) like isnull(@s_tabowner,"%")
and   name like isnull(@s_tabname,"%")
and type = "U" and id > 100
order by user_name(uid), name
for read only

declare index_cursor cursor for
select  st.indid
      , si.name
      , abs(sign(si.status2 & 512)) /* DOL clustered index */
      , abs(sign(si.status & 16)) /* clustered bit */
      , si.keycnt
from systabstats st, sysindexes si
where st.id = @tabid
  and si.id = @tabid
  and st.id = si.id
  and st.indid = si.indid
order by st.indid
for read only

declare col_cursor cursor for
select  sc.colid,
        ss.colidarray,
        datalength(ss.colidarray),
        sc.name,
        ss.statid,
        convert(int,ss.c1),
        convert(varchar,ss.moddate,109),
        ltrim(str(round(convert(double precision,ss.c2),16),24,16)),
        ltrim(str(round(convert(double precision,ss.c3),16),24,16)),
        convert(int,ss.c4),
        convert(int,ss.c5),
        st.name,
        ltrim(str(convert(int,ss.c7),5)),
        ltrim(str(convert(int,ss.c8),3)),
        ltrim(str(convert(int,ss.c9),3)),
        ltrim(str(round(convert(double precision,ss.c10),16),24,16)),
        ltrim(str(round(convert(double precision,ss.c11),16),24,16))
from syscolumns sc, sysstatistics ss, systypes st
where sc.id = @tabid
and   sc.name like isnull(@colname,"%")
and   ss.id = sc.id
and   convert(int,ss.c6) *= st.type
and   st.name not in ("timestamp","sysname", "nchar", "nvarchar")
and   st.usertype < 100
and   convert(tinyint,substring(ss.colidarray,1,1)) = sc.colid
and   ss.formatid = 100
order by sc.id, sc.name, ss.colidarray
for read only

declare nostats_cursor cursor for
select sc.name
from syscolumns sc,
 sysstatistics ss
where ss.id =* sc.id
and  sc.id = @tabid
and  ss.formatid = 100
and  ss.statid = 0
and  ss.sequence = 1
and  sc.colid *= convert(tinyint,substring(ss.colidarray,1,1))
and  datalength(ss.colidarray) = 1
group by sc.name
having count(ss.id) = 0
order by sc.name
for read only

create table #cells(seq int,colnum int)

/** DO NOT FOLD, SPINDAL, OR MUTILATE (unless its sysstatistics) **/
/** OK, bear with me, here we go... **/

declare histogram_cursor cursor for
select
      /** Here is the step number **/
      str(
      ((c.seq-1)*80 + 1 )*(1-abs(sign(c.colnum-1 ))) + ((c.seq-1)*80 + 2 )*(1-abs(sign(c.colnum-2 ))) +
      ((c.seq-1)*80 + 3 )*(1-abs(sign(c.colnum-3 ))) + ((c.seq-1)*80 + 4 )*(1-abs(sign(c.colnum-4 ))) +
      ((c.seq-1)*80 + 5 )*(1-abs(sign(c.colnum-5 ))) + ((c.seq-1)*80 + 6 )*(1-abs(sign(c.colnum-6 ))) +
      ((c.seq-1)*80 + 7 )*(1-abs(sign(c.colnum-7 ))) + ((c.seq-1)*80 + 8 )*(1-abs(sign(c.colnum-8 ))) +
      ((c.seq-1)*80 + 9 )*(1-abs(sign(c.colnum-9 ))) + ((c.seq-1)*80 + 10)*(1-abs(sign(c.colnum-10))) +
      ((c.seq-1)*80 + 11)*(1-abs(sign(c.colnum-11))) + ((c.seq-1)*80 + 12)*(1-abs(sign(c.colnum-12))) +
      ((c.seq-1)*80 + 13)*(1-abs(sign(c.colnum-13))) + ((c.seq-1)*80 + 14)*(1-abs(sign(c.colnum-14))) +
      ((c.seq-1)*80 + 15)*(1-abs(sign(c.colnum-15))) + ((c.seq-1)*80 + 16)*(1-abs(sign(c.colnum-16))) +
      ((c.seq-1)*80 + 17)*(1-abs(sign(c.colnum-17))) + ((c.seq-1)*80 + 18)*(1-abs(sign(c.colnum-18))) +
      ((c.seq-1)*80 + 19)*(1-abs(sign(c.colnum-19))) + ((c.seq-1)*80 + 20)*(1-abs(sign(c.colnum-20))) +
      ((c.seq-1)*80 + 21)*(1-abs(sign(c.colnum-21))) + ((c.seq-1)*80 + 22)*(1-abs(sign(c.colnum-22))) +
      ((c.seq-1)*80 + 23)*(1-abs(sign(c.colnum-23))) + ((c.seq-1)*80 + 24)*(1-abs(sign(c.colnum-24))) +
      ((c.seq-1)*80 + 25)*(1-abs(sign(c.colnum-25))) + ((c.seq-1)*80 + 26)*(1-abs(sign(c.colnum-26))) +
      ((c.seq-1)*80 + 27)*(1-abs(sign(c.colnum-27))) + ((c.seq-1)*80 + 28)*(1-abs(sign(c.colnum-28))) +
      ((c.seq-1)*80 + 29)*(1-abs(sign(c.colnum-29))) + ((c.seq-1)*80 + 30)*(1-abs(sign(c.colnum-30))) +
      ((c.seq-1)*80 + 31)*(1-abs(sign(c.colnum-31))) + ((c.seq-1)*80 + 32)*(1-abs(sign(c.colnum-32))) +
      ((c.seq-1)*80 + 33)*(1-abs(sign(c.colnum-33))) + ((c.seq-1)*80 + 34)*(1-abs(sign(c.colnum-34))) +
      ((c.seq-1)*80 + 35)*(1-abs(sign(c.colnum-35))) + ((c.seq-1)*80 + 36)*(1-abs(sign(c.colnum-36))) +
      ((c.seq-1)*80 + 37)*(1-abs(sign(c.colnum-37))) + ((c.seq-1)*80 + 38)*(1-abs(sign(c.colnum-38))) +
      ((c.seq-1)*80 + 39)*(1-abs(sign(c.colnum-39))) + ((c.seq-1)*80 + 40)*(1-abs(sign(c.colnum-40))) +
      ((c.seq-1)*80 + 41)*(1-abs(sign(c.colnum-41))) + ((c.seq-1)*80 + 42)*(1-abs(sign(c.colnum-42))) +
      ((c.seq-1)*80 + 43)*(1-abs(sign(c.colnum-43))) + ((c.seq-1)*80 + 44)*(1-abs(sign(c.colnum-44))) +
      ((c.seq-1)*80 + 45)*(1-abs(sign(c.colnum-45))) + ((c.seq-1)*80 + 46)*(1-abs(sign(c.colnum-46))) +
      ((c.seq-1)*80 + 47)*(1-abs(sign(c.colnum-47))) + ((c.seq-1)*80 + 48)*(1-abs(sign(c.colnum-48))) +
      ((c.seq-1)*80 + 49)*(1-abs(sign(c.colnum-49))) + ((c.seq-1)*80 + 50)*(1-abs(sign(c.colnum-50))) +
      ((c.seq-1)*80 + 51)*(1-abs(sign(c.colnum-51))) + ((c.seq-1)*80 + 52)*(1-abs(sign(c.colnum-52))) +
      ((c.seq-1)*80 + 53)*(1-abs(sign(c.colnum-53))) + ((c.seq-1)*80 + 54)*(1-abs(sign(c.colnum-54))) +
      ((c.seq-1)*80 + 55)*(1-abs(sign(c.colnum-55))) + ((c.seq-1)*80 + 56)*(1-abs(sign(c.colnum-56))) +
      ((c.seq-1)*80 + 57)*(1-abs(sign(c.colnum-57))) + ((c.seq-1)*80 + 58)*(1-abs(sign(c.colnum-58))) +
      ((c.seq-1)*80 + 59)*(1-abs(sign(c.colnum-59))) + ((c.seq-1)*80 + 60)*(1-abs(sign(c.colnum-60))) +
      ((c.seq-1)*80 + 61)*(1-abs(sign(c.colnum-61))) + ((c.seq-1)*80 + 62)*(1-abs(sign(c.colnum-62))) +
      ((c.seq-1)*80 + 63)*(1-abs(sign(c.colnum-63))) + ((c.seq-1)*80 + 64)*(1-abs(sign(c.colnum-64))) +
      ((c.seq-1)*80 + 65)*(1-abs(sign(c.colnum-65))) + ((c.seq-1)*80 + 66)*(1-abs(sign(c.colnum-66))) +
      ((c.seq-1)*80 + 67)*(1-abs(sign(c.colnum-67))) + ((c.seq-1)*80 + 68)*(1-abs(sign(c.colnum-68))) +
      ((c.seq-1)*80 + 69)*(1-abs(sign(c.colnum-69))) + ((c.seq-1)*80 + 70)*(1-abs(sign(c.colnum-70))) +
      ((c.seq-1)*80 + 71)*(1-abs(sign(c.colnum-71))) + ((c.seq-1)*80 + 72)*(1-abs(sign(c.colnum-72))) +
      ((c.seq-1)*80 + 73)*(1-abs(sign(c.colnum-73))) + ((c.seq-1)*80 + 74)*(1-abs(sign(c.colnum-74))) +
      ((c.seq-1)*80 + 75)*(1-abs(sign(c.colnum-75))) + ((c.seq-1)*80 + 76)*(1-abs(sign(c.colnum-76))) +
      ((c.seq-1)*80 + 77)*(1-abs(sign(c.colnum-77))) + ((c.seq-1)*80 + 78)*(1-abs(sign(c.colnum-78))) +
      ((c.seq-1)*80 + 79)*(1-abs(sign(c.colnum-79))) + ((c.seq-1)*80 + 80)*(1-abs(sign(c.colnum-80)))
       ,9),

      /** And here is the Weight of the cell **/

      str(
      isnull(convert(real,s.c0)*(1-abs(sign(c.colnum-1 ))) ,0) + isnull(convert(real,s.c1)*(1-abs(sign(c.colnum-2 ))) ,0) +
      isnull(convert(real,s.c2)*(1-abs(sign(c.colnum-3 ))) ,0) + isnull(convert(real,s.c3)*(1-abs(sign(c.colnum-4 ))) ,0) +
      isnull(convert(real,s.c4)*(1-abs(sign(c.colnum-5 ))) ,0) + isnull(convert(real,s.c5)*(1-abs(sign(c.colnum-6 ))) ,0) +
      isnull(convert(real,s.c6)*(1-abs(sign(c.colnum-7 ))) ,0) + isnull(convert(real,s.c7)*(1-abs(sign(c.colnum-8 ))) ,0) +
      isnull(convert(real,s.c8)*(1-abs(sign(c.colnum-9 ))) ,0) + isnull(convert(real,s.c9)*(1-abs(sign(c.colnum-10))) ,0) +
      isnull(convert(real,s.c10)*(1-abs(sign(c.colnum-11))) ,0) + isnull(convert(real,s.c11)*(1-abs(sign(c.colnum-12))) ,0) +
      isnull(convert(real,s.c12)*(1-abs(sign(c.colnum-13))) ,0) + isnull(convert(real,s.c13)*(1-abs(sign(c.colnum-14))) ,0) +
      isnull(convert(real,s.c14)*(1-abs(sign(c.colnum-15))) ,0) + isnull(convert(real,s.c15)*(1-abs(sign(c.colnum-16))) ,0) +
      isnull(convert(real,s.c16)*(1-abs(sign(c.colnum-17))) ,0) + isnull(convert(real,s.c17)*(1-abs(sign(c.colnum-18))) ,0) +
      isnull(convert(real,s.c18)*(1-abs(sign(c.colnum-19))) ,0) + isnull(convert(real,s.c19)*(1-abs(sign(c.colnum-20))) ,0) +
      isnull(convert(real,s.c20)*(1-abs(sign(c.colnum-21))) ,0) + isnull(convert(real,s.c21)*(1-abs(sign(c.colnum-22))) ,0) +
      isnull(convert(real,s.c22)*(1-abs(sign(c.colnum-23))) ,0) + isnull(convert(real,s.c23)*(1-abs(sign(c.colnum-24))) ,0) +
      isnull(convert(real,s.c24)*(1-abs(sign(c.colnum-25))) ,0) + isnull(convert(real,s.c25)*(1-abs(sign(c.colnum-26))) ,0) +
      isnull(convert(real,s.c26)*(1-abs(sign(c.colnum-27))) ,0) + isnull(convert(real,s.c27)*(1-abs(sign(c.colnum-28))) ,0) +
      isnull(convert(real,s.c28)*(1-abs(sign(c.colnum-29))) ,0) + isnull(convert(real,s.c29)*(1-abs(sign(c.colnum-30))) ,0) +
      isnull(convert(real,s.c30)*(1-abs(sign(c.colnum-31))) ,0) + isnull(convert(real,s.c31)*(1-abs(sign(c.colnum-32))) ,0) +
      isnull(convert(real,s.c32)*(1-abs(sign(c.colnum-33))) ,0) + isnull(convert(real,s.c33)*(1-abs(sign(c.colnum-34))) ,0) +
      isnull(convert(real,s.c34)*(1-abs(sign(c.colnum-35))) ,0) + isnull(convert(real,s.c35)*(1-abs(sign(c.colnum-36))) ,0) +
      isnull(convert(real,s.c36)*(1-abs(sign(c.colnum-37))) ,0) + isnull(convert(real,s.c37)*(1-abs(sign(c.colnum-38))) ,0) +
      isnull(convert(real,s.c38)*(1-abs(sign(c.colnum-39))) ,0) + isnull(convert(real,s.c39)*(1-abs(sign(c.colnum-40))) ,0) +
      isnull(convert(real,s.c40)*(1-abs(sign(c.colnum-41))) ,0) + isnull(convert(real,s.c41)*(1-abs(sign(c.colnum-42))) ,0) +
      isnull(convert(real,s.c42)*(1-abs(sign(c.colnum-43))) ,0) + isnull(convert(real,s.c43)*(1-abs(sign(c.colnum-44))) ,0) +
      isnull(convert(real,s.c44)*(1-abs(sign(c.colnum-45))) ,0) + isnull(convert(real,s.c45)*(1-abs(sign(c.colnum-46))) ,0) +
      isnull(convert(real,s.c46)*(1-abs(sign(c.colnum-47))) ,0) + isnull(convert(real,s.c47)*(1-abs(sign(c.colnum-48))) ,0) +
      isnull(convert(real,s.c48)*(1-abs(sign(c.colnum-49))) ,0) + isnull(convert(real,s.c49)*(1-abs(sign(c.colnum-50))) ,0) +
      isnull(convert(real,s.c50)*(1-abs(sign(c.colnum-51))) ,0) + isnull(convert(real,s.c51)*(1-abs(sign(c.colnum-52))) ,0) +
      isnull(convert(real,s.c52)*(1-abs(sign(c.colnum-53))) ,0) + isnull(convert(real,s.c53)*(1-abs(sign(c.colnum-54))) ,0) +
      isnull(convert(real,s.c54)*(1-abs(sign(c.colnum-55))) ,0) + isnull(convert(real,s.c55)*(1-abs(sign(c.colnum-56))) ,0) +
      isnull(convert(real,s.c56)*(1-abs(sign(c.colnum-57))) ,0) + isnull(convert(real,s.c57)*(1-abs(sign(c.colnum-58))) ,0) +
      isnull(convert(real,s.c58)*(1-abs(sign(c.colnum-59))) ,0) + isnull(convert(real,s.c59)*(1-abs(sign(c.colnum-60))) ,0) +
      isnull(convert(real,s.c60)*(1-abs(sign(c.colnum-61))) ,0) + isnull(convert(real,s.c61)*(1-abs(sign(c.colnum-62))) ,0) +
      isnull(convert(real,s.c62)*(1-abs(sign(c.colnum-63))) ,0) + isnull(convert(real,s.c63)*(1-abs(sign(c.colnum-64))) ,0) +
      isnull(convert(real,s.c64)*(1-abs(sign(c.colnum-65))) ,0) + isnull(convert(real,s.c65)*(1-abs(sign(c.colnum-66))) ,0) +
      isnull(convert(real,s.c66)*(1-abs(sign(c.colnum-67))) ,0) + isnull(convert(real,s.c67)*(1-abs(sign(c.colnum-68))) ,0) +
      isnull(convert(real,s.c68)*(1-abs(sign(c.colnum-69))) ,0) + isnull(convert(real,s.c69)*(1-abs(sign(c.colnum-70))) ,0) +
      isnull(convert(real,s.c70)*(1-abs(sign(c.colnum-71))) ,0) + isnull(convert(real,s.c71)*(1-abs(sign(c.colnum-72))) ,0) +
      isnull(convert(real,s.c72)*(1-abs(sign(c.colnum-73))) ,0) + isnull(convert(real,s.c73)*(1-abs(sign(c.colnum-74))) ,0) +
      isnull(convert(real,s.c74)*(1-abs(sign(c.colnum-75))) ,0) + isnull(convert(real,s.c75)*(1-abs(sign(c.colnum-76))) ,0) +
      isnull(convert(real,s.c76)*(1-abs(sign(c.colnum-77))) ,0) + isnull(convert(real,s.c77)*(1-abs(sign(c.colnum-78))) ,0) +
      isnull(convert(real,s.c78)*(1-abs(sign(c.colnum-79))) ,0) + isnull(convert(real,s.c79)*(1-abs(sign(c.colnum-80))) ,0)
      ,10,8),

      /** And finally, here is the Value of the cell **/

      substring(convert(varbinary(255),v.c0),(1-abs(sign(c.colnum-1 ))) ,255) + substring(convert(varbinary(255),v.c1),(1-abs(sign(c.colnum-2 ))) ,255) +
      substring(convert(varbinary(255),v.c2),(1-abs(sign(c.colnum-3 ))) ,255) + substring(convert(varbinary(255),v.c3),(1-abs(sign(c.colnum-4 ))) ,255) +
      substring(convert(varbinary(255),v.c4),(1-abs(sign(c.colnum-5 ))) ,255) + substring(convert(varbinary(255),v.c5),(1-abs(sign(c.colnum-6 ))) ,255) +
      substring(convert(varbinary(255),v.c6),(1-abs(sign(c.colnum-7 ))) ,255) + substring(convert(varbinary(255),v.c7),(1-abs(sign(c.colnum-8 ))) ,255) +
      substring(convert(varbinary(255),v.c8),(1-abs(sign(c.colnum-9 ))) ,255) + substring(convert(varbinary(255),v.c9),(1-abs(sign(c.colnum-10))) ,255) +
      substring(convert(varbinary(255),v.c10),(1-abs(sign(c.colnum-11))) ,255) + substring(convert(varbinary(255),v.c11),(1-abs(sign(c.colnum-12))) ,255) +
      substring(convert(varbinary(255),v.c12),(1-abs(sign(c.colnum-13))) ,255) + substring(convert(varbinary(255),v.c13),(1-abs(sign(c.colnum-14))) ,255) +
      substring(convert(varbinary(255),v.c14),(1-abs(sign(c.colnum-15))) ,255) + substring(convert(varbinary(255),v.c15),(1-abs(sign(c.colnum-16))) ,255) +
      substring(convert(varbinary(255),v.c16),(1-abs(sign(c.colnum-17))) ,255) + substring(convert(varbinary(255),v.c17),(1-abs(sign(c.colnum-18))) ,255) +
      substring(convert(varbinary(255),v.c18),(1-abs(sign(c.colnum-19))) ,255) + substring(convert(varbinary(255),v.c19),(1-abs(sign(c.colnum-20))) ,255) +
      substring(convert(varbinary(255),v.c20),(1-abs(sign(c.colnum-21))) ,255) + substring(convert(varbinary(255),v.c21),(1-abs(sign(c.colnum-22))) ,255) +
      substring(convert(varbinary(255),v.c22),(1-abs(sign(c.colnum-23))) ,255) + substring(convert(varbinary(255),v.c23),(1-abs(sign(c.colnum-24))) ,255) +
      substring(convert(varbinary(255),v.c24),(1-abs(sign(c.colnum-25))) ,255) + substring(convert(varbinary(255),v.c25),(1-abs(sign(c.colnum-26))) ,255) +
      substring(convert(varbinary(255),v.c26),(1-abs(sign(c.colnum-27))) ,255) + substring(convert(varbinary(255),v.c27),(1-abs(sign(c.colnum-28))) ,255) +
      substring(convert(varbinary(255),v.c28),(1-abs(sign(c.colnum-29))) ,255) + substring(convert(varbinary(255),v.c29),(1-abs(sign(c.colnum-30))) ,255) +
      substring(convert(varbinary(255),v.c30),(1-abs(sign(c.colnum-31))) ,255) + substring(convert(varbinary(255),v.c31),(1-abs(sign(c.colnum-32))) ,255) +
      substring(convert(varbinary(255),v.c32),(1-abs(sign(c.colnum-33))) ,255) + substring(convert(varbinary(255),v.c33),(1-abs(sign(c.colnum-34))) ,255) +
      substring(convert(varbinary(255),v.c34),(1-abs(sign(c.colnum-35))) ,255) + substring(convert(varbinary(255),v.c35),(1-abs(sign(c.colnum-36))) ,255) +
      substring(convert(varbinary(255),v.c36),(1-abs(sign(c.colnum-37))) ,255) + substring(convert(varbinary(255),v.c37),(1-abs(sign(c.colnum-38))) ,255) +
      substring(convert(varbinary(255),v.c38),(1-abs(sign(c.colnum-39))) ,255) + substring(convert(varbinary(255),v.c39),(1-abs(sign(c.colnum-40))) ,255) +
      substring(convert(varbinary(255),v.c40),(1-abs(sign(c.colnum-41))) ,255) + substring(convert(varbinary(255),v.c41),(1-abs(sign(c.colnum-42))) ,255) +
      substring(convert(varbinary(255),v.c42),(1-abs(sign(c.colnum-43))) ,255) + substring(convert(varbinary(255),v.c43),(1-abs(sign(c.colnum-44))) ,255) +
      substring(convert(varbinary(255),v.c44),(1-abs(sign(c.colnum-45))) ,255) + substring(convert(varbinary(255),v.c45),(1-abs(sign(c.colnum-46))) ,255) +
      substring(convert(varbinary(255),v.c46),(1-abs(sign(c.colnum-47))) ,255) + substring(convert(varbinary(255),v.c47),(1-abs(sign(c.colnum-48))) ,255) +
      substring(convert(varbinary(255),v.c48),(1-abs(sign(c.colnum-49))) ,255) + substring(convert(varbinary(255),v.c49),(1-abs(sign(c.colnum-50))) ,255) +
      substring(convert(varbinary(255),v.c50),(1-abs(sign(c.colnum-51))) ,255) + substring(convert(varbinary(255),v.c51),(1-abs(sign(c.colnum-52))) ,255) +
      substring(convert(varbinary(255),v.c52),(1-abs(sign(c.colnum-53))) ,255) + substring(convert(varbinary(255),v.c53),(1-abs(sign(c.colnum-54))) ,255) +
      substring(convert(varbinary(255),v.c54),(1-abs(sign(c.colnum-55))) ,255) + substring(convert(varbinary(255),v.c55),(1-abs(sign(c.colnum-56))) ,255) +
      substring(convert(varbinary(255),v.c56),(1-abs(sign(c.colnum-57))) ,255) + substring(convert(varbinary(255),v.c57),(1-abs(sign(c.colnum-58))) ,255) +
      substring(convert(varbinary(255),v.c58),(1-abs(sign(c.colnum-59))) ,255) + substring(convert(varbinary(255),v.c59),(1-abs(sign(c.colnum-60))) ,255) +
      substring(convert(varbinary(255),v.c60),(1-abs(sign(c.colnum-61))) ,255) + substring(convert(varbinary(255),v.c61),(1-abs(sign(c.colnum-62))) ,255) +
      substring(convert(varbinary(255),v.c62),(1-abs(sign(c.colnum-63))) ,255) + substring(convert(varbinary(255),v.c63),(1-abs(sign(c.colnum-64))) ,255) +
      substring(convert(varbinary(255),v.c64),(1-abs(sign(c.colnum-65))) ,255) + substring(convert(varbinary(255),v.c65),(1-abs(sign(c.colnum-66))) ,255) +
      substring(convert(varbinary(255),v.c66),(1-abs(sign(c.colnum-67))) ,255) + substring(convert(varbinary(255),v.c67),(1-abs(sign(c.colnum-68))) ,255) +
      substring(convert(varbinary(255),v.c68),(1-abs(sign(c.colnum-69))) ,255) + substring(convert(varbinary(255),v.c69),(1-abs(sign(c.colnum-70))) ,255) +
      substring(convert(varbinary(255),v.c70),(1-abs(sign(c.colnum-71))) ,255) + substring(convert(varbinary(255),v.c71),(1-abs(sign(c.colnum-72))) ,255) +
      substring(convert(varbinary(255),v.c72),(1-abs(sign(c.colnum-73))) ,255) + substring(convert(varbinary(255),v.c73),(1-abs(sign(c.colnum-74))) ,255) +
      substring(convert(varbinary(255),v.c74),(1-abs(sign(c.colnum-75))) ,255) + substring(convert(varbinary(255),v.c75),(1-abs(sign(c.colnum-76))) ,255) +
      substring(convert(varbinary(255),v.c76),(1-abs(sign(c.colnum-77))) ,255) + substring(convert(varbinary(255),v.c77),(1-abs(sign(c.colnum-78))) ,255) +
      substring(convert(varbinary(255),v.c78),(1-abs(sign(c.colnum-79))) ,255) + substring(convert(varbinary(255),v.c79),(1-abs(sign(c.colnum-80))) ,255)
from #cells c, sysstatistics s, sysstatistics v
where s.id = @tabid
and s.colidarray = convert(varbinary(1),convert(tinyint,@colid))
and s.formatid = 104
and v.id =* s.id
and v.colidarray =* s.colidarray
and v.statid =* s.statid
and v.sequence =* s.sequence
and v.formatid = 102
and c.seq = s.sequence
for read only

/** Wow, I'm glad that's over **/
/** Let's get on with the business at hand **/

print "%1!",@proc_version
print "%1!",@@version
print ''

/** Standard optdiag output **/
begin
   print 'Server name:                            "%1!"',@@servername
   print ''
   print 'Specified database:                     "%1!"',@s_dbname
   if (@s_tabowner is null)
     print 'Specified table owner:                  not specified'
   else
     print 'Specified table owner:                  "%1!"',@s_tabowner
   if (@s_tabname is null)
     print 'Specified table:                        not specified'
   else
     print 'Specified table:                        "%1!"',@s_tabname
   if (@colname is null)
     print 'Specified column:                       not specified'
   else
     print 'Specified column:                       "%1!"',@colname
   print ''

/*
** Check to see if the @tabname is in sysobjects.
*/

   open object_cursor

   fetch object_cursor into
      @tabid, @u_dbname, @u_dbid,
      @u_tabowner, @u_tabname

while (@@sqlstatus = 0)
begin
   print 'Table owner:                            "%1!"',@u_tabowner
   print 'Table name:                             "%1!"',@u_tabname
   print ''

   dbcc flushstats(@u_dbid, @tabid)

   select @ptn_data_pgs = convert(int, max(ptn_data_pgs(@tabid, partitionid)))
   from syspartitions
   where id = @tabid

   ---------------------
   -- Work on Indexes --
   ---------------------
   open index_cursor
   fetch index_cursor into
              @indid ,@index_name ,@dol_clustered, @clustered, @keycnt

   while (@@sqlstatus = 0)
   begin
      select @keycnt = @keycnt - isnull(abs(sign(@clustered - 1)),0)
            ,@index_cols = null
      while (@keycnt > 0)
      begin
         select @index_cols = substring(', ' ,abs(sign(@keycnt - 1)),2)
                            + '"' + index_col(@u_tabname, @indid, @keycnt, user_id(@u_tabowner)) + '"'
                            + @index_cols
         select @keycnt = @keycnt - 1
      end
      select @leafcnt = ltrim(convert(varchar(32),convert(int,leafcnt))),
             @pagecnt = ltrim(convert(varchar(32),convert(int,pagecnt))),
             @emptypgcnt = ltrim(convert(varchar(32),convert(int,emptypgcnt))),
             @rowcnt = ltrim(convert(varchar(32),str(round(convert(double precision,rowcnt),16),32,16))),
             @forwrowcnt = ltrim(convert(varchar(32),str(round(convert(double precision,forwrowcnt),16),32,16))),
             @delrowcnt = ltrim(convert(varchar(32),str(round(convert(double precision,delrowcnt),16),32,16))),
             @dpagecrcnt = ltrim(convert(varchar(32),str(round(convert(double precision,dpagecrcnt),16),32,16))),
             @dpagecr = ltrim(convert(varchar(32),str(round(convert(double precision,dpagecrcnt),16),32,16))),
             @ipagecrcnt = ltrim(convert(varchar(32),str(round(convert(double precision,ipagecrcnt),16),32,16))),
             @ipagecr = ltrim(convert(varchar(32),str(round(convert(double precision,ipagecrcnt),16),32,16))),
             @drowcrcnt = ltrim(convert(varchar(32),str(round(convert(double precision,drowcrcnt),16),32,16))),
             @drowcr = ltrim(convert(varchar(32),str(round(convert(double precision,drowcrcnt),16),32,16))),
             @oamapgcnt = ltrim(convert(varchar(32),convert(int,oamapgcnt))),
             @extent0pgcnt = ltrim(convert(varchar(32),convert(int,extent0pgcnt))),
             @datarowsize = ltrim(convert(varchar(32),str(round(convert(double precision,datarowsize),16),32,16))),
             @leafrowsize = ltrim(convert(varchar(32),str(round(convert(double precision,leafrowsize),16),32,16))),
             @indexheight = ltrim(convert(varchar(32),convert(smallint,indexheight))),
             @spare1 = ltrim(convert(varchar(32),convert(int,spare1))),
             @spare2 = ltrim(convert(varchar(32),str(round(convert(double precision,spare2),16),32,16)))
      from systabstats
      where id = @tabid and indid = @indid

      ----------------------
      -- print index info --
      ----------------------

      if (@indid = 0)
         print 'Statistics for table:                   "%1!"',@index_name
      else if (1 in (@clustered,@dol_clustered))
         print 'Statistics for index:                   "%1!" (clustered)',@index_name
      else
         print 'Statistics for index:                   "%1!" (nonclustered)',@index_name
      if (@indid > 0)
         print 'Index column list:                      %1!',@index_cols
      else
         print ''
      if (@clustered = 1 or @indid = 0)
         print '     Data page count:                   %1!',@pagecnt
      else
         print '     Leaf count:                        %1!',@leafcnt

      if (1 in (@clustered,@dol_clustered) or @indid = 0)
         print '     Empty data page count:             %1!',@emptypgcnt
      else
         print '     Empty leaf page count:             %1!',@emptypgcnt

      if (@clustered = 1 or @indid = 0)
      begin
         print '     Data row count:                    %1!',@rowcnt
         print '     Forwarded row count:               %1!',@forwrowcnt
         print '     Deleted row count:                 %1!',@delrowcnt
      end

      print '     Data page CR count:                %1!',@dpagecrcnt
      if ((@clustered = 0 or @dol_clustered = 1) and @indid > 0)
      begin
         print '     Index page CR count:               %1!',@ipagecrcnt
         print '     Data row CR count:                 %1!',@drowcrcnt
      end

      if (@clustered = 1 or @indid = 0)
         print '     OAM + allocation page count:       %1!',@oamapgcnt

      if (@indid = 0)
         print '     First extent data pages:           %1!',@extent0pgcnt
      else
         print '     First extent leaf pages:           %1!',@extent0pgcnt
      if (@clustered = 1 or @indid = 0)
         print '     Data row size:                     %1!',@datarowsize
      else
         print '     Leaf row size:                     %1!',@leafrowsize
      if (@indid > 0)
         print '     Index height:                      %1!',@indexheight
      if ((@clustered = 1 or @indid = 0) and @ptn_data_pgs is not null)
         print '     Pages in largest partition:        %1!',@ptn_data_pgs

      print ''
      print '  Derived statistics:'

      if ( (select lower(@option)) in ("cr","cluster ratio") )
      begin
         print '     Data page cluster ratio:           proprietary'
      end
      else
         print '     Data page cluster ratio:           proprietary'
      if ((@clustered = 0 or @dol_clustered = 1) and @indid > 0)
      begin
         print '     Index page cluster ratio:          proprietary'
         print '     Data row cluster ratio:            proprietary'
      end
      print ''

      fetch index_cursor into
                 @indid ,@index_name ,@dol_clustered ,@clustered, @keycnt
   end
   close index_cursor

   ---------------------
   -- Work on Columns --
   ---------------------
      open col_cursor
      fetch col_cursor into
         @colid, @colidarray, @colidarray_len, @colname, @statid, @c1stat, @last_updt, @rc_density, @tot_density
        ,@steps_act, @steps_req, @typename, @collength, @precision, @scale, @r_sel, @between_sel

      while (@@sqlstatus = 0)
      begin
         if (@steps_act is not null)
            print 'Statistics for column:                  "%1!"',@colname
         else
         begin   -- BUILD A COLUMN GROUP NAME
            select @colgroup_name = null
            while (@colidarray_len > 0)
            begin
               select @colgroup_name =
                                substring(', ' ,abs(sign(@colidarray_len - 1)),2)
                              + '"' + name + '"'
                              + @colgroup_name
               from syscolumns
               where id = @tabid
                 and colid = convert(tinyint,substring(@colidarray,@colidarray_len,1))
               select @colidarray_len = @colidarray_len - 1
            end
            print 'Statistics for column group:            %1!',@colgroup_name
         end
         print 'Last update of column statistics:       %1!',@last_updt
         if (@c1stat & 2 = 2)
            print 'Statistics loaded from Optdiag.'
         print ''
         print '     Range cell density:                %1!',@rc_density
         print '     Total density:                     %1!',@tot_density
         if (@r_sel is not null)
            print '     Range selectivity:                 %1!',@r_sel
         else
            print '     Range selectivity:                 default used (0.33)'
         if (@between_sel is not null)
            print '     In between selectivity:            %1!',@between_sel
         else
            print '     In between selectivity:            default used (0.25)'
         print ''
         if (@steps_act is not null) /** Print a Histogram **/
         begin
            truncate table #cells
            select @freq_cell = 0, @seq = 1
            select @used_count = isnull(sum(usedcount),0)
            from sysstatistics
            where id = @tabid
             and  statid = @statid
             and  colidarray = convert(varbinary(1),convert(tinyint,@colid))
             and  formatid = 104
             and  sequence = @seq
            while (@used_count > 0)
            begin
               select @rownum = 1
               while (@rownum <= @used_count)
               begin
                  insert into #cells(seq,colnum) values (@seq,@rownum)
                  select @rownum = @rownum + 1
               end
               select @seq = @seq + 1
               select @used_count = isnull(sum(usedcount),0)
               from sysstatistics
               where id = @tabid
                and  statid = @statid
                and  colidarray = convert(varbinary(1),convert(tinyint,@colid))
                and  formatid = 104
                and  sequence = @seq
            end

            print 'Histogram for column:                   "%1!"',@colname
            if (@typename in ("int","intn"))
               select @typename = "integer"
            if (@typename = "float" and @collength = "4")
               select @typename = "real"
            if (@typename = "float" and @collength = "8")
               select @typename = "double precision"
            if (@typename in ("varchar","nvarchar","char","nchar","binary","varbinary","float","floatn"))
               print 'Column datatype:                        %1!(%2!)',@typename,@collength
            else if (@typename in ("numeric","decimal","numericn","decimaln"))
               print 'Column datatype:                        %1!(%2!,%3!)',@typename,@precision,@scale
            else
               print 'Column datatype:                        %1!',@typename
            print 'Requested step count:                   %1!',@steps_req
            print 'Actual step count:                      %1!',@steps_act
            print ''
            print '     Step     Weight                    Value'
            print ''

            open histogram_cursor
            fetch histogram_cursor into
               @step, @weight, @value_raw
            while (@@sqlstatus = 0)
            begin
               select
                 @value_c =
                     CASE
                      WHEN @typename in ("varchar","nvarchar","char","nchar")
                       THEN '"' + convert(varchar(255),@value_raw) + '"'

                      WHEN @typename in ("int","intn","integer")
                       THEN str(convert(int,@value_raw),10)

                      WHEN @typename in ("smallint")
                       THEN str(convert(smallint,@value_raw),10)

                      WHEN @typename in ("tinyint")
                       THEN str(convert(tinyint,@value_raw),10)

                      /** Oh, oh, a scaled numeric, where does the decimal place go??? **/
                      WHEN (@typename in ("numeric","decimal","numericn","decimaln") and convert(smallint,@scale) > 0)
                       THEN str(convert(numeric(38),right(replicate(0x00,255-convert(smallint,@collength)) + @value_raw,17))
                              /* move over @scale decimal places please */
                                /power(convert(numeric,10),convert(smallint,@scale))
                              /* make room for @precision, minus, and decimal signs */
                               , convert(smallint,@precision)+2,convert(smallint,@scale))

                      WHEN (@typename in ("numeric","decimal","numericn","decimaln") and @scale = "0")
                       THEN str(convert(numeric(38),right(replicate(0x00,255-convert(smallint,@collength)) + @value_raw,17))
                                , convert(smallint,@precision))

                      WHEN (@typename in ("float","floatn","real") and @collength = "4")
                       THEN str(convert(real,@value_raw),40,8)

                      WHEN (@typename in ("float","floatn","double precision") and @collength = "8")
                       THEN str(convert(double precision,@value_raw),40,16)

                      WHEN @typename in ("money","moneyn","smallmoney")
                       THEN str(convert(money,@value_raw),22,2)

                      WHEN @typename in ("datetime","datetimn")
                       THEN '"' + convert(varchar(255),convert(datetime,@value_raw),109) + '"'

                      WHEN @typename in ("smalldatetime")
                       THEN '"' + convert(varchar(255),convert(smalldatetime,@value_raw),100) + '"'

                      ELSE @value_raw
                     END

               if (@value_raw is null)
                 select @freq_cell =1 , @prev_step = @step, @prev_weight = @weight, @value_c = "null"
               else
               begin
                 select @value_c = ltrim(@value_c)
                 if (@freq_cell = 1)
                 begin /* Printing a frequency cell */
                    if (@typename in ("binary","varbinary","timestamp"))
                    begin
                       print '%1!     %2!        <       %3!',@prev_step,@prev_weight,@value_raw
                       print '%1!     %2!        =       %3!',@step,@weight,@value_raw
                    end
                    else
                    begin
                       print '%1!     %2!        <       %3!',@prev_step,@prev_weight,@value_c
                       print '%1!     %2!        =       %3!',@step,@weight,@value_c
                    end
                 end
                 else /* NOT printing a frequency cell */
                 begin
                    if (@typename in ("binary","varbinary","timestamp"))
                       print '%1!     %2!       <=       %3!',@step,@weight,@value_raw
                    else
                       print '%1!     %2!       <=       %3!',@step,@weight,@value_c
                 end
                 select @freq_cell = 0
               end

               fetch histogram_cursor into
                  @step, @weight, @value_raw
            end
            close histogram_cursor
            /* Is there only one cell (a freqency cell) */
            if (@freq_cell = 1)
                    print '%1!     %2!        =       %3!',@prev_step,@prev_weight,@value_c
            print ''
         end /* histogram print */

      fetch col_cursor into
         @colid, @colidarray, @colidarray_len,  @colname, @statid, @c1stat, @last_updt, @rc_density, @tot_density
        ,@steps_act, @steps_req, @typename, @collength, @precision, @scale, @r_sel, @between_sel
      end
      close col_cursor
      -----------------------
      -- Done with columns --
      -----------------------

      ------------------------------
      -- print cols with no stats --
      ------------------------------
      select @keycnt = 0
      open nostats_cursor
      fetch nostats_cursor into @colname
      while (@@sqlstatus = 0)
      begin
         select @keycnt = @keycnt + 1
         if (@keycnt = 1)
            print 'No statistics for remaining columns:    "%1!"',@colname
         else if (@keycnt = 2)
            print '(default values used)                   "%1!"',@colname
         else
            print '                                        "%1!"',@colname
         fetch nostats_cursor into @colname
      end
      close nostats_cursor
      if (@keycnt = 1)
         print '(default values used)'

      print ''

      fetch object_cursor into
         @tabid, @u_dbname, @u_dbid,
         @u_tabowner, @u_tabname
   end
   close object_cursor
-----------------------
-- Done with Objects --
-----------------------
end

go

grant execute on sp__optdiag to public
go
