set nocount on
go 

-- ------------------------------------------------------------------
-- Enable capture

--exec sp_configure 'enable monitoring', 1
--exec sp_configure 'per object statistics active', 1
go 


-- ------------------------------------------------------------------
-- Create proc

use sybsystemprocs
go
dump tran sybsystemprocs with truncate_only
go
drop proc sp_dba_getobj
go
create proc sp_dba_getobj
/*
# ############################################################################
# @(#) Proc    : sp_dba_getobj
# @(#) Auteur  : FA
# @(#) Cree le : 20101117
# @(#) Objet   : Object in cache info using MDA. Object must be on current database
# @(#) Usage   : sp_dba_getobj [TABLE] [ , OUTPUTTYPE=0|1|10|11 ]  [ , SHOWUSAGE=0|1 ]
# @(#)           TABLE : Table name, wildcards managed
# @(#)           OUTPUTTYPE : 0 = table only / 1 = include index / +10 = active only
# @(#)           SHOWUSAGE : 0 = hide usage / 1 = show usage
# @(#) Exemples: sp_dba_getobj TEST
# @(#) Depend. : 
# @(#) Version : ASE 12.5
# ############################################################################
# Mises a jour
# ----------------------------------------------------------------------------
# ############################################################################
*/

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

	@table 		sysname = null,
	@outputtype 	tinyint = 0 ,
	@showusage 	tinyint = 1
as
begin

/*
# ----------------------------------------------------------------------------
# Show usage
*/

if @showusage=1
begin
	print ""
	print "-- Usage   : sp_dba_getobj [TABLE] [ , OUTPUTTYPE=0|1|10|11 ] [ , SHOWUSAGE=0|1 ] "
	print "-- Running : sp_dba_getobj %1!, %2!, %3!",  @table, @outputtype, @showusage
	print ""
end


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

	set nocount on

	if @table is null
		set @table = '%'

/*
# ----------------------------------------------------------------------------
# Extract MDA
*/

	select 	db_id() 'dbid', 
		id 	
	into 	#tmp_scope 
	from 	sysobjects 
	where 	name like @table
	and 	type='U' 

	select 	a.* 
	into 	#tmp_monOpenObjectActivity 
	from 	#tmp_scope 			s 
	join 	master..monOpenObjectActivity 	a on a.DBID=s.dbid and a.ObjectID=s.id

	select 	c.* 
	into 	#tmp_monCachedObject
	from 	#tmp_scope 			s 
	join 	master..monCachedObject 	c on c.DBID=s.dbid and c.ObjectID=s.id


/*
# ----------------------------------------------------------------------------
# Compute stats
*/


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

/*
# ----------------------------------------------------------------------------
# OUTPUT TYPE 0 = Table report
*/
if @outputtype in ( 0, 10 ) 
begin
	select		Cache		= c.CacheName,
			'Tbl' 		= object_name(a.ObjectID) ,
			'Used#'		= a.UsedCount ,
			'LastUsed' 	= convert(char(6),a.LastUsedDate,12) + ' ' + convert(char(5),a.LastUsedDate,108), 
			'CachedMb' 	= c.CachedKB / 1024 
--			'SizeMb' 	= c.TotalSizeKB / 1024 ,
--			'Pct'		= 100 * c.CachedKB / c.TotalSizeKB
	into		#result0
	from 		#tmp_monOpenObjectActivity 	a 
	left join  	#tmp_monCachedObject 		c on c.DBID=a.DBID and c.ObjectID=a.ObjectID and c.IndexID=a.IndexID
	where 		a.IndexID=0
	and 		(
				( @outputtype = 10 and (c.CachedKB> 0 or a.UsedCount > 0 )  ) 
				or
				( @outputtype = 0 )
			)
	order by 4 desc ,  2 asc

	exec sp_autoformat #result0
end


/*
# ----------------------------------------------------------------------------
# OUTPUT TYPE 1 = Class report
*/

if @outputtype in ( 1, 11 ) 
begin
	select		Cache		= c.CacheName,
			'Object' 	= case when a.IndexID > 0 then object_name(a.ObjectID) + '.' else null end + object_name(a.ObjectID) ,
			'Used#'		= a.UsedCount ,
			'LastUsed' 	= convert(char(6),a.LastUsedDate,12) + ' ' + convert(char(5),a.LastUsedDate,108), 
			'CachedMb' 	= c.CachedKB / 1024  
--			'SizeMb' 	= c.TotalSizeKB / 1024 ,
--			'Pct'		= 100 * c.CachedKB / c.TotalSizeKB
	into 		#result1
	from 		#tmp_monOpenObjectActivity 	a 
	left join  	#tmp_monCachedObject 		c on c.DBID=a.DBID and c.ObjectID=a.ObjectID and c.IndexID=a.IndexID
	where 		1=1
	and 		(
				( @outputtype = 11 and (c.CachedKB> 0 or a.UsedCount > 0 )  ) 
				or
				( @outputtype = 1 )
			)
	order by CachedKB desc

	exec sp_autoformat #result1

end


end

go

-- ------------------------------------------------------------------
-- Permissions

grant exec on sp_dba_getobj to public
go

-- ------------------------------------------------------------------
-- Control

if object_id('sp_dba_getobj') is not null
        print 'sp_dba_getobj install OK on %1!', @@servername
else
        print 'sp_dba_getobj install FAILED on %1!', @@servername
go

