
use sybsystemprocs
go

IF EXISTS (SELECT 1 FROM sysobjects o, sysusers u WHERE o.uid=u.uid AND o.name = 'sp_dba_statsdetail' AND u.name = 'dbo' AND o.type = 'P')
BEGIN
	setuser 'dbo'
	drop procedure sp_dba_statsdetail

END
go 

IF (@@error != 0)
BEGIN
	PRINT "Error CREATING Stored Procedure 'sybsystemprocs.dbo.sp_dba_statsdetail;1'"
	SELECT syb_quit()
END
go

setuser 'dbo'
go 



create proc sp_dba_statsdetail
/*
# ############################################################################
# @(#) Proc    : sp_dba_statsdetail
# @(#) Auteur  : FA
# @(#) Cree le : 20110621
# @(#) Objet   : statistics overview - detail
# @(#) Usage   : sp_dba_statsdetail [ TABLE ] [ , OUTPUTTYPE ]
# @(#)           OUTPUTTYPE : 0 = all / 1 = table only / 2 = index only
# @(#)           OUTPUTTYPE : OUTPUTTYPE + 10 ( 10,11,12 ) = SQL correction
# @(#) Exemples: sp_dba_statsdetail              => show err summary
# @(#)           sp_dba_statsdetail null,1       => show sql corrections
# @(#) Version : ASE 15.0
# ############################################################################
# Historique
# ----------------------------------------------------------------------------
# ############################################################################
*/

/*
# ############################################################################
# Parameters
*/

        @tablename      sysname= null,
        @outputtype     tinyint = 0
as 
begin

/*
# ############################################################################
# Environment
*/

    set nocount on

    declare @id int
    select @id=object_id(@tablename)


/*
# ############################################################################
# Program
*/


/*
# ----------------------------------------------------------------------------
# Extract stats
*/

select 	'Tbl' = object_name(a.id),
	'Idx' =  case when a.indid <= 1 then null else isnull(b.name,'text') end ,
	Lck		= upper(convert(char(3),str_replace(lockscheme(a.id),'data',null))), 
	a.id,
	a.indid,

	pages		= case when a.indid <= 1 then  data_pages(db_id(),a.id,0) else  data_pages(db_id(),a.id,a.indid) end ,
--	pages		= convert(numeric(18),pagecnt) ,
	pgempty		= emptypgcnt,
	pgunused 	= unusedpgcnt,

	dpcr		= convert(smallint,100*derived_stat(a.id,a.indid,"dpcr")),
	ipcr		= convert(smallint,100*derived_stat(a.id,a.indid,"ipcr")),
	drcr		= convert(smallint,100*derived_stat(a.id,a.indid,"drcr")),
	lgio		= convert(smallint,100*derived_stat(a.id,a.indid,"lgio")),
	sput		= convert(smallint,100*derived_stat(a.id,a.indid,"sput")),

	idxheight 	= indexheight,
	idxleaf 	= leafcnt,

	rowcnt 		= convert(numeric(18),rowcnt) ,
	rowfwd 		= convert(numeric(18),forwrowcnt),
	rowdel		= convert(numeric(18),delrowcnt),
	rowsize		= case when a.indid <= 1 then convert(numeric(18),datarowsize) else convert(numeric(18),leafrowsize)  end ,

	dpagecrcnt	= convert(numeric(18),dpagecrcnt),
	ipagecrcnt	= convert(numeric(18),ipagecrcnt) ,
	drowcrcnt	= convert(numeric(18),drowcrcnt) ,

	moddate		= convert(varchar(8),statmoddate,112)

into 	#tmp
from 	systabstats a
left join sysindexes b on b.id=a.id and b.indid=a.indid
where a.id > 100
and a.id not in ( select o.id from sysobjects o where ( sysstat2 & 2048 ) = 2048 )
and a.id = isnull(@id,a.id)
order by 1,4


/*
# ----------------------------------------------------------------------------
# Generate sql instructions
*/

create table #sql ( indid int ,cmd varchar(1000) )
declare @cr 		char(1), 
	@go 		char(4)
select 	@cr = char(10)

select	@go = @cr + 'go' + @cr


-- unused -----------------------

insert #sql ( indid, cmd ) 
select 	distinct indid, 
	'-- unusedpgcnt TBL : ' + convert(varchar,pgunused) + ' // ' + convert(varchar,pages/512) + ' Mo' + @cr + 
	'reorg rebuild ' + Tbl + @go
from #tmp
where pgunused > 0 and indid <= 1 and ( pages + pgempty + pgunused ) > 8

insert #sql ( indid, cmd ) 
select 	distinct indid, 
	'-- unusedpgcnt IDX : ' + convert(varchar,pgunused) + '  // ' + convert(varchar,pages/512) + ' Mo' + @cr + 
	case Lck when 'ALL' then '-- not allowed for APL tables : recreate manually. ' else '' end + 
	'reorg rebuild ' + Tbl + ' ' + Idx + @go
from #tmp
where pgunused > 0 and indid > 1 and Idx != 'text' and ( pages + pgempty + pgunused ) > 8 


-- lgio -------------------------

insert #sql ( indid, cmd ) 
select 	distinct indid, 
	'-- lgio TBL : ' + convert(varchar,lgio) + ' // ' + convert(varchar,pages/512) + ' Mo' + @cr + 
	'reorg rebuild ' + Tbl + @go
from #tmp
where lgio < 80 and indid <= 1 and pages between 2 and 1000000 and ( pages + pgempty + pgunused ) > 8

insert #sql ( indid, cmd ) 
select 	distinct indid, 
	'-- lgio TBL : ' + convert(varchar,lgio) + ' // ' + convert(varchar,pages/512) + ' Mo' + @cr + 
	'reorg compact ' + Tbl + ' with resume' + @go
from #tmp
where lgio < 80 and indid <= 1 and pages > 1000000

insert #sql ( indid, cmd ) 
select 	distinct indid, 
	'-- lgio IDX : ' + convert(varchar,lgio) + ' // ' + convert(varchar,pages/512) + ' Mo' + @cr + 
	case Lck when 'ALL' then '-- not allowed for APL tables : recreate manually. ' else '' end + 
	'reorg rebuild ' + Tbl + ' ' + Idx + @go
from #tmp
where lgio < 80 and indid > 1 



/*
# ############################################################################
# show result 
*/


/*
# ----------------------------------------------------------------------------
# OUTPUTTYPE = 0 : show result
*/

if @outputtype = 0
begin
select 	name		= convert(varchar(60),Tbl + case when Idx is null then '' else '.' + Idx end),

	Lck,
	pages		,
	pgempty		,
	pgunused	,

	dpcr		,
	ipcr		,
	drcr		,
	lgio		,
	sput		,

	rowcnt 		,
	rowfwd 		,
	rowdel		,
	rowsize		,

	indid		,
	idxheight 	,
	idxleaf 	,

	dpagecrcnt	,
	ipagecrcnt	,
	drowcrcnt	,

	moddate	
into #result0
from #tmp
order by 1,2

exec sp_autoformat #result0
end

if @outputtype = 1
begin
select 	name		= convert(varchar(30),Tbl),

	Lck,
	pages		,
	pgempty		,
	pgunused	,

	dpcr		,
	drcr		,
	lgio		,
	sput		,

	rowcnt 		,
	rowfwd 		,
	rowdel		,
	rowsize		,

	dpagecrcnt	,

	moddate	
into #result1
from #tmp
where indid <= 1
order by 1

exec sp_autoformat #result1
end

if @outputtype = 2
begin
select 	name		= convert(varchar(60),Tbl + case when Idx is null then '' else '.' + Idx end),

	pages		,
	pgempty		,
	pgunused	,

	dpcr		,
	ipcr		,
	drcr		,
	lgio		,
	sput		,

	rowsize		,

	indid		,
	idxheight 	,
	idxleaf 	,

	ipagecrcnt	,
	drowcrcnt	,

	moddate	

into #result2
from #tmp
where indid > 1
order by 1,2
exec sp_autoformat #result2
end

if @outputtype = 10
begin
select distinct cmd from #sql 	
order by 1
end

if @outputtype = 11
begin
select distinct cmd from #sql where indid <= 1	
order by 1
end

if @outputtype = 12
begin
select distinct cmd from #sql where indid > 1	
order by 1
end


end

go 

Grant Execute on dbo.sp_dba_statsdetail to public 
go

sp_procxmode 'sp_dba_statsdetail', unchained
go 


