--
-- Author: Mohit Dubey
-- Visit http://www.geocities.com/md_seraphin for more goodies!
--
-- This program is distributed under the GNU Public License Version 2
-- with the additional privisio that the original author's name and
-- contact details must be retained as-is in any modified or copied
-- versions of this program.
--

set feedback off 

drop table genReport;

@popRepTab.sql

col userid heading SchemaName format a15
col numtab Heading Tabs format 9999
col numpri heading PKs format 9999
col numunique heading UKs format 9999
col numfk heading FKs format 9999
col numcheck heading Chks format 9999
col numuidx heading UIs format 9999
col numnuidx heading nUIs format 9999
col numdefault heading Dflt format 9999
col numpack heading Pkgs format 9999
col numproc heading Proc format 9999
col numfunc heading Func format 9999
col numtrig heading Trig format 9999
col numsyn heading Syns format 9999
col numlink heading Link format 9999
col numtype heading Type format 9999
set lines 132
select userid,numtab,numpri,numunique,numfk,numcheck,numuidx,numnuidx,
		 numdefault,numpack,numproc,numfunc,numtrig,numsyn,numlink,numtype   
from genReport
order by userid;

set head off pages 0
col type heading "Object Type" format a30
col status heading Status format a10
col uniq Heading Cardinal format a10
set head on pages 24
select status||
		 ' '||
		 substr(object_type,1,20) type, 
	    count(1) num
from user_objects
where status <> 'VALID'
group by substr(object_type,1,20), status
union
select status||
		 ' '||
		 decode(constraint_type,'P','PRIMARY CONSTRAINT',
										'R','FOREIGN CONSTRAINT',
										'C','CHECK CONSTRAINT',
										'U','UNIQUE CONSTRAINT',
										'*OTHER* CONSTRAINT') type,
	    count(1) num
from user_constraints
where status <> 'ENABLED'
group by constraint_type, status
union
select status||
		 ' '||
		 uniqueness||
		 ' INDEX' type,
	    count(1) num
from user_indexes
where status <> 'VALID'
group by uniqueness, status
order by 1;

drop table genReport;