Here are two scripts: Run the first to create a view. Then run the second
anytime you want to look at a table. You get most everything you need
except trigger information, which I didn't get around to putting in yet.
Van
- This script creates a function and a view that lets you get
- constraint information on a table in plain English.
- You need to run the script once in a database. Then you can use
- the view to get information on every constraint like this:
- SELECT *
- FROM constraint_info
- WHERE owner = 'SomeOwnerName'
- and table_name = 'SomeTableName';
- Van March, 1999
PROMPT CONSTRAINT_INFO_F function
CREATE OR REPLACE FUNCTION constraint_info_f(p_constraint_owner IN VARCHAR2,
p_constraint_name IN VARCHAR2) RETURN VARCHAR2
--
- Returns a plain-English text describing the constraint
--
AS
c_cr CONSTANT VARCHAR2(1) := CHR(10);
CURSOR c_cons_cols (cp_cons_owner IN VARCHAR2, cp_cons_name IN VARCHAR2)
IS
SELECT table_name,
column_name
FROM all_cons_columns
WHERE owner = cp_cons_owner
and constraint_name = cp_cons_name
ORDER BY position;
v_cons all_constraints%ROWTYPE;
v_description VARCHAR2(32767);
v_r_table VARCHAR2(30);
v_col_count BINARY_INTEGER;
i BINARY_INTEGER := 0;
BEGIN
--
- Fetch the constraint definition from the data dictionary
--
BEGIN
SELECT *
INTO v_cons
FROM all_constraints
WHERE owner = p_constraint_owner
and constraint_name = p_constraint_name;
EXCEPTION
WHEN NO_DATA_FOUND THEN
RETURN ('No information found for constraint '||
p_constraint_name);
END;
IF v_cons.constraint_type = 'C' THEN
--
- A check constraint. Just indicate the check clause.
--
v_description := 'The following must be true for each row inserted '||
'or updated' || c_cr || v_cons.search_condition;
ELSIF v_cons.constraint_type IN('U', 'P') THEN
--
- Primary and Unique keys. Indicate the columns that must be
- unique. For primary keys, these columns must also not be null.
--
SELECT count(*)
INTO v_col_count
FROM all_cons_columns
WHERE owner = p_constraint_owner
and constraint_name = p_constraint_name;
v_description := 'The ';
FOR r IN c_cons_cols (p_constraint_owner, p_constraint_name) LOOP
i := i + 1;
IF v_col_count > 2 AND i < v_col_count THEN
v_description := v_description ||r.column_name || ', ';
ELSE
v_description := v_description ||r.column_name || ' ';
END IF;
IF i = v_col_count -1 THEN
v_description := v_description || 'and ';
END IF;
END LOOP;
IF v_col_count = 1 THEN
v_description := v_description ||'column must be unique';
ELSE
v_description := v_description ||'columns must be unique';
END IF;
IF v_cons.constraint_type = 'P' THEN
v_description := v_description ||' and not null';
END IF;
ELSIF v_cons.constraint_type = 'R' THEN
--
- Foreign keys: Indicate the relevant columns, and the table
- and columns they must reference.
--
SELECT count(*)
INTO v_col_count
FROM all_cons_columns
WHERE owner = p_constraint_owner
and constraint_name = p_constraint_name;
v_description := 'The ';
FOR r IN c_cons_cols (P_constraint_owner, p_constraint_name) LOOP
i := i + 1;
IF v_col_count > 2 and i < v_col_count THEN
v_description := v_description || r.column_name || ', ';
ELSE
v_description := v_description || r.column_name || ' ';
END IF;
IF i = v_col_count - 1 THEN
v_description := v_description || 'and ';
END IF;
END LOOP;
IF v_col_count = 1 THEN
v_description := v_description || 'column must reference the ';
ELSE
v_description := v_description || 'columns must reference the ';
END IF;
i := 0;
FOR r IN c_cons_cols (v_cons.r_owner, v_cons.r_constraint_name) LOOP
i := i + 1;
IF v_col_count > 2 and i < v_col_count THEN
v_description := v_description || r.column_name || ', ';
ELSE
v_description := v_description || r.column_name || ' ';
END IF;
IF i = v_col_count - 1 THEN
v_description := v_description || 'and ';
END IF;
v_r_table := r.table_name;
END LOOP;
IF v_col_count = 1 THEN
v_description := v_description || 'column of the '|| v_r_table
|| ' table';
ELSE
v_description := v_description || 'columns of the ' || v_r_table
|| ' table';
END IF;
IF v_cons.delete_rule = 'CASCADE' THEN
v_description := v_description || c_cr
|| 'Cascade delete is in effect for this constraint';
END IF;
ELSIF v_cons.constraint_type = 'V' THEN
--
- View with check option. Indicate that the view was
- created WITH CHECK OPTION.
--
v_description := 'View ' || v_cons.table_name
|| ' created WITH CHECK OPTION';
ELSIF v_cons.constraint_type = 'O' THEN
--
- Read only view. Indicate that the view was created
- WITH READ ONLY. (Note this is correct for version 8.
- In version 7 views created with read only show up with
- a type V constraint. Version 7 does not have a type O constraint.)
--
v_description := 'View ' || v_cons.table_name
|| 'created WITH READ ONLY';
ELSE
--
- Unrecognized constraint type
--
v_description := 'This constraint has unrecognized ' || 'type "' ||
v_cons.constraint_type || '"';
END IF;
IF v_cons.status <> 'ENABLED' THEN
v_description := v_description || c_cr || 'This constraint is ' ||
v_cons.status;
END IF;
RETURN SUBSTR(v_description, 1, 2000);
EXCEPTION
WHEN OTHERS THEN
v_description := 'Oracle error "' || SQLERRM
||'" occurred while retrieving information for constraint ' ||
p_constraint_name;
RETURN SUBSTR(v_description, 1, 2000);
END constraint_info_f;
/
REM Often you get an ORA-04028 error the very first time the constraint_info
REM view is created. The following dummy select statement prevents the
REM error - an Oracle quirk.
SELECT constraint_info_f ('SYS', 'x') "Dummy Query"
FROM SYS.dual;
PROMPT CONSTRAINT_INFO view
CREATE OR REPLACE VIEW constraint_info (
owner,
table_name,
constraint_name,
constraint_info )
AS
SELECT
owner,
table_name,
constraint_name,
constraint_info_f (owner, constraint_name)
FROM
all_constraints;
CREATE public synonym constraint_info for sys.constraint_info;
- These connected scripts draw information on a table from a number
- of sources.
- In script one you see table placement and storage information.
- You get the table owner, table tablespace, table extents, table
- size in bytes, the initial extent in bytes, the next extent in
- bytes, pctincrease, the minextents and the maxextents.
- In script two you see constraint information including columns
- You get constraint owner, constraint name, constraint type,
- status, as well as the column name, column position and
- constraint description for each column involved.
- In script three you see index placement and storage information.
- You get index owner, uniqueness, index name, index tablespace,
- extents, index size in bytes, the initial extent in bytes, the
- next extent in bytes, pctincrease, the minextents and
- the maxextents
- In script four you see index column information
- You get the index name, the column name and the column position
- In script five you see privilege information.
- You get the table owner, the grantor, the grantee, the privilege
- and whether the privilege is grantable.
- In script six you see the foreign keys that point to this table.
- In script seven you see for every column in the table its name,
- datatype, whether nullable, the column's default value (if any)
- and the number of different values.
--
- ADD TRIGGERS
--
- To run this script you must already have created the necessary
- view and function by running the script MakeConstraintView.sql
- Van February, 1999
set pagesize 66
set newpage 3
set verify off
column towner heading 'Owner' format a11
column tspace heading 'Tablespace' format a12
column texts heading 'Exts' format 999
column tbytes heading 'Bytes' format 9999999999
column tinitial heading ' Init |In Bytes' format 999999999
column tnext heading ' Next |In Bytes' format 999999999
column tpct heading 'Pct|Inc' format 999
column tmin heading ' Min|Exts' format 9999
column tmax heading ' Max|Exts' format 999999999
TTITLE ' TABLE LOCATION AND STORAGE INFORMATION '
ACCEPT tname PROMPT 'Enter Name of Table You Want to Look at:'
ACCEPT qowner PROMPT 'Enter Name of Table Owner:'
SELECT
s.owner towner,
s.tablespace_name tspace,
s.extents texts,
s.bytes tbytes,
s.initial_extent tinitial,
s.next_extent tnext,
s.pct_increase tpct,
s.min_extents tmin,
s.max_extents tmax
FROM
dba_segments s
WHERE
s.segment_name = UPPER('&tname')
and s.segment_type = 'TABLE'
and s.owner = UPPER('&qowner')
;
column conowner heading 'Constraint| Owner' format a11
column conname heading ' Constraint| Name' format a20
column constatus heading ' Status' format a9
column consinfo heading ' Constraint Information'
TTITLE ' CONSTRAINT INFORMATION FOR THIS TABLE '
SELECT
v.owner conowner,
v.constraint_name conname,
lpad(c.status, 9) constatus,
v.constraint_info consinfo
FROM
dba_constraints c,
constraint_info v
WHERE
v.table_name = UPPER('&tname')
and v.owner = UPPER('&qowner')
and c.owner = v.owner
and c.table_name = v.table_name
and v.constraint_name = c.constraint_name
;
column iowner heading 'Index|Owner' format a5
column iuniq heading 'Unique' format a6
column iname heading 'Index Name' format a21
column itspace heading 'Tablespace' format a11
column iexts heading 'Exts' format 999
column ibytes heading 'Bytes' format 999999999
column iinitial heading ' Init |In Bytes' format 999999999
column inext heading ' Next |In Bytes' format 999999999
column ipct heading 'Pct|Inc' format 999
column imin heading ' Min|Exts' format 999
column imax heading ' Max|Exts' format 999
TTITLE 'INDEX LOCATION AND STORAGE INFORMATION FOR THIS TABLE'
SELECT
i.owner iowner,
i.uniqueness iuniq,
i.index_name iname,
i.tablespace_name itspace,
s.extents iexts,
s.bytes ibytes,
i.initial_extent iinitial,
i.next_extent inext,
i.pct_increase ipct,
i.min_extents imin,
i.max_extents imax
FROM
dba_indexes i,
dba_segments s
WHERE
i.table_name = UPPER('&tname')
and i.table_owner = UPPER('&qowner')
and s.owner = i.table_owner
and s.segment_name = i.index_name
and s.segment_type = 'INDEX'
;
column indname heading ' Index| Name' format a20
column indcolumn heading ' Column| Name' format a20
column indposition heading ' Column |Position' format 999
TTITLE ' INDEX COLUMN INFORMATION FOR THIS TABLE'
SELECT
l.index_name indname,
lpad(l.column_name, 20) indcolumn,
l.column_position indposition
FROM
dba_ind_columns l
WHERE
l.table_name = UPPER('&tname')
and l.index_owner = UPPER('&qowner')
;
column powner heading 'Table|Owner' format a15
column pgrantor heading 'Grantor' format a15
column pgrantee heading 'Grantee' format a15
column ppriv heading 'Privilege|Name' format a15
column pgrantable heading 'Grantable?' format a10
TTITLE ' PRIVILEGES GRANTED ON THIS TABLE '
SELECT
p.owner powner,
p.grantor pgrantor,
p.grantee pgrantee,
p.privilege ppriv,
p.grantable pgrantable
FROM
dba_tab_privs p
WHERE
p.table_name = UPPER('&tname')
and p.owner = UPPER('&qowner')
;
column fowner heading 'Owner Of |Table With|Foreign Key' format a11
column ftable heading ' Name Of | Table With| Foreign Key' format a16
column fconname heading ' FK Name ' format a22
column fdel heading 'Delete|Rule' format a10
column fref heading 'Constraint in|Parent Table|Pointed to by FK' format a22
column fstatus heading 'FK Status' format a8
TTITLE ' FOREIGN KEYS THAT POINT TO THIS TABLE '
SELECT
frn.owner fowner,
frn.table_name ftable,
frn.constraint_name fconname,
frn.delete_rule fdel,
frn.r_constraint_name fref,
frn.status fstatus
FROM
dba_constraints par,
dba_constraints frn
WHERE
par.table_name = UPPER('&tname')
and par.owner = UPPER('&qowner')
and par.constraint_name = frn.r_constraint_name
;
column colnam format a20 heading 'Column Name'
column datype format a15 heading 'Type'
column nullble format a8 heading 'Null'
column numdist format 99,999,999 heading '# of Diff Values'
column defval format a13 heading 'Default Value'
ttitle 'DESCRIPTION OF THE COLUMNS IN THIS TABLE'
SELECT
column_name colnam,
decode(NULLABLE,'Y','','NOT NULL') nullble,
DATA_TYPE ||
decode(DATA_TYPE,
'NUMBER', '('||to_char(DATA_PRECISION)||
decode(DATA_SCALE,0,'',','||
to_char(DATA_SCALE))||')',
'VARCHAR2', '('||to_char(DATA_LENGTH)||')',
'CHAR', '('||to_char(DATA_LENGTH)||')',
'DATE','', 'Error') datype,
num_distinct numdist,
data_default defval
FROM
dba_tab_columns
WHERE
table_name = UPPER('&tname')
and owner = UPPER('&qowner')
ORDER BY
column_id
;
set verify on
TTITLE off
<jscichocki_at_myezmail.com> wrote in message
news:8qqpil$6mv$1_at_nnrp1.deja.com...
> How do I find out what the keys and constraints of a table are. I did
> not create the table, but I am suppose to create a app that updates
> it. It is a general ledger table & I really don't need any mistakes to
> happen. I've tried the describe command, but that just gives me the
> basics. Can anyone help?
>
> Thanks
>
>
> Sent via Deja.com http://www.deja.com/
> Before you buy.
Received on Tue Sep 26 2000 - 17:04:45 CDT
