Re: Indexes and stuff

From: Brian Peasland <peasland_at_edcmail.cr.usgs.gov>
Date: Tue, 12 Oct 1999 15:27:02 GMT
Message-ID: <38035346.8B4944BA@edcmail.cr.usgs.gov>

Here is one that I wrote some time ago. I'm not convinced that it's 100% accurate, but it is a good starting point.

HTH,
Brian

set echo off
set verify off

prompt table_dependencies.sql
prompt
accept xOwner prompt 'Enter table owner: ' accept xTable prompt 'Enter table: '
prompt
prompt Table Dependencies for &xOwner . &xTable prompt

column OWNER format a20
column TABLE_NAME format a25
column TABLESPACE_NAME format a15
column TABLE_OWNER format a20

set linesize 130
set pagesize 60

prompt DESCRIPTION OF &xTable
column "Null?" format a8
column TYPE format a30
SELECT column_name AS Name,
DECODE (nullable,'N','Not Null') AS "Null?", DECODE (data_type,'VARCHAR2',data_type||'('||data_length||')',                  

'NUMBER',data_type||'('||data_length||','||data_precision||')',

                  data_type) AS Type

FROM dba_tab_columns
WHERE owner=UPPER('&xOwner') AND table_name=UPPER('&xTable');

prompt PRIMARY KEY FOR &xTable
SELECT a.constraint_name AS pk_constraint_name,b.column_name,b.position FROM dba_constraints a,dba_cons_columns b WHERE a.constraint_type='P' AND a.owner=UPPER('&xOwner') AND a.table_name=UPPER('&xTable') AND
a.constraint_name=b.constraint_name
ORDER BY position;

prompt INDEXES ON &xTable

SELECT INDEX_NAME,UNIQUENESS,TABLESPACE_NAME,INITIAL_EXTENT,NEXT_EXTENT,        MIN_EXTENTS,MAX_EXTENTS,PCT_INCREASE FROM dba_indexes
WHERE owner = UPPER('&xOwner') AND table_name=UPPER('&xTable') ORDER BY index_name;

prompt INDEX COLUMNS FOR &xTable
SELECT index_name,column_name,column_position FROM dba_ind_columns
WHERE index_owner=UPPER('&xOwner') AND index_name IN

   (SELECT index_name FROM dba_indexes WHERE owner = UPPER('&xOwner')     AND table_name=UPPER('&xTable'))
ORDER BY index_name,column_position;

prompt CHECK CONSTRAINTS FOR &xTable
column SEARCH_CONDITION format a40
column R_OWNER format a20
SELECT constraint_name,search_condition FROM dba_constraints
WHERE owner=UPPER('&xOwner') AND table_name=UPPER('&xTable') AND constraint_type = 'C'
ORDER BY constraint_type,constraint_name;

prompt F.K. REFERENCES FOR &xTable
column DELETE_RULE format a11
SELECT
a.constraint_name,a.r_owner,b.table_name,a.r_constraint_name,a.delete_rule FROM dba_constraints a, dba_constraints b WHERE a.owner=UPPER('&xOwner') AND a.table_name=UPPER('&xTable') AND a.constraint_type='R'
AND a.r_constraint_name=b.constraint_name;

prompt F.K. REFERENCES TO &xTable
SELECT owner,table_name,constraint_name FROM dba_constraints
WHERE constraint_type='R' AND r_owner=UPPER('&xOwner') AND r_constraint_name IN

   (SELECT constraint_name FROM dba_constraints     WHERE owner=UPPER('&xOwner') AND table_name=UPPER('&xTable') AND constraint_type='P');

prompt OTHER DEPENDENCIES ON &xTable
SELECT owner,name,type
FROM dba_dependencies
WHERE referenced_owner=UPPER('&xOwner') AND referenced_name=UPPER('&xTable');

amerar_at_ci.chi.il.us wrote:
>
> Hello,
>
> I want to write a script that will show me all of the indexes and
> constraints on our tables. I want it to show all the referential stuff
> and so on......
>
> Before I spend the hours it will take to write one, I was wondering if
> one already existed........
>
> Sent via Deja.com http://www.deja.com/
> Before you buy.
Received on Tue Oct 12 1999 - 10:27:02 CDT