DATABASE ALL DETAILS for the TABLE

DATABASE ALL DETAILS for the TABLE



*********************************************************************************
PRIVILAGES
*********************************************************************************
SELECT "PRIVILEGE", "GRANTEE", "GRANTABLE", "GRANTOR", "OBJECT_NAME" 
FROM(Select PRIVILEGE, GRANTEE, GRANTABLE, GRANTOR, COLUMN_NAME object_name from dba_col_privs where owner = :OBJECT_OWNER and TABLE_NAME =  :OBJECT_NAME) 
union all 
Select PRIVILEGE, GRANTEE, GRANTABLE, GRANTOR, table_NAME object_name from dba_tab_privs where owner = :OBJECT_OWNER and TABLE_NAME = :OBJECT_NAME);



*********************************************************************************
INDEXES
*********************************************************************************
SELECT "INDEX_OWNER", "INDEX_NAME", "UNIQUENESS", "STATUS", "INDEX_TYPE", "TEMPORARY", "PARTITIONED", "FUNCIDX_STATUS", "JOIN_INDEX", "COLUMNS", "COLUMN_EXPRESSION" FROM(
select ind.index_owner,ind.index_name,ind.uniqueness, 
         ind.status,ind.index_type,ind.temporary, ind.partitioned,ind.funcidx_status, 
         ind.join_index,ind.columns,ie.column_expression
         ,ind.index_name sdev_link_name,'INDEX' sdev_link_type, ind.index_owner sdev_link_owner     
  from (select index_owner,table_owner,index_name,uniqueness, status,index_type,temporary, partitioned,funcidx_status, join_index,       
  max(decode(position,1 ,column_name))||           
  max(decode(position,2 ,', '||column_name))||           
  max(decode(position,3 ,', '||column_name))||           
  max(decode(position,4 ,', '||column_name)) columns 
from(   
select di.owner index_owner,dc.table_owner,dc.index_name,di.uniqueness, di.status,               
di.index_type, di.temporary, di.partitioned,di.funcidx_status, di.join_index,               
dc.column_name,dc.column_position position          
from Dba_ind_columns dc,Dba_indexes di         
where di.table_owner = :OBJECT_OWNER           
and di.table_name  =  :OBJECT_NAME           
and dc.index_name = di.index_name           
and dc.index_owner = di.owner 
) group by index_owner,table_owner,index_name,uniqueness, status, index_type, temporary, partitioned,funcidx_status, join_index) ind,
Dba_IND_EXPRESSIONS ie  
where ind.index_name = ie.index_name(+)
and ind.index_owner = ie.index_owner(+)
);




*********************************************************************************
CONSTRAINTS
*********************************************************************************
SELECT "CONSTRAINT_NAME", "CONSTRAINT_TYPE", "SEARCH_CONDITION", "R_OWNER", "R_TABLE_NAME", "R_CONSTRAINT_NAME", "DELETE_RULE", "STATUS", "DEFERRABLE", "VALIDATED", "GENERATED", "BAD", "RELY", "LAST_CHANGE", "INDEX_OWNER", "INDEX_NAME", "INVALID", "VIEW_RELATED" FROM(
select c.constraint_name,decode(c.constraint_type,'P','Primary_Key','U','Unique','R','Foreign_Key','C','Check',c.constraint_type) constraint_type,c.search_condition,c.r_owner,(select r.table_name from Dba_constraints r where c.r_owner = r.owner and c.r_constraint_name = r.constraint_name) r_table_name,c.r_constraint_name,c.delete_rule,c.status,c.deferrable,c.validated,c.generated,c.bad,c.rely,c.last_change,c.index_owner,c.index_name,c.invalid,c.view_related from Dba_constraints c where c.owner = :OBJECT_OWNER and c.table_name = :OBJECT_NAME order by c.constraint_name
);



*********************************************************************************
STATISTICS
*********************************************************************************
SELECT "OWNER", "TABLE_NAME", "COLUMN_NAME", "NUM_DISTINCT", "LOW_VALUE", "HIGH_VALUE", "DENSITY", "NUM_NULLS", "NUM_BUCKETS", "LAST_ANALYZED", "SAMPLE_SIZE", "GLOBAL_STATS", "USER_STATS", "AVG_COL_LEN", "HISTOGRAM" FROM(
select * from sys.Dba_TAB_COL_STATISTICS where  owner = :OBJECT_OWNER and table_name = :OBJECT_NAME);



*********************************************************************************
DEPENDENCIES
*********************************************************************************
SELECT "OWNER", "NAME", "TYPE", "REFERENCED_OWNER", "REFERENCED_NAME", "REFERENCED_TYPE" FROM(
select owner, name, type, referenced_owner, referenced_name, referenced_type ,owner sdev_link_owner, name sdev_link_name, type sdev_link_type from Dba_DEPENDENCIES where referenced_owner = :OBJECT_OWNER and referenced_name = :OBJECT_NAME);