Oracle FAQ | Your Portal to the Oracle Knowledge Grid |
Home -> Community -> Usenet -> c.d.o.misc -> Re: more problems with listing pks and fks
Here is a copy of a quick little script that I wrote to give information on a table. It might be something that will be of use to you or others. If anyone has something similar or better I would like to see it.
Patrick
flahan_at_earthlink.net
you can copy this into a file and call from sql*plus.
You would call the script like this:
@table <owner>.<table_name>
@table scott.stocks
---Start of script
SET SCAN ON
SET VERIFY OFF
DEFINE pOwnerDotTableName = &1
set serveroutput on size 1000000
DECLARE
/*
Things to add:
Constraints --
Foreign Keys
Triggers
*/
cStage VARCHAR2(200) := 'Initial Declare'; cOwnerDotTableName VARCHAR2(100) := UPPER('&pOwnerDotTableName'); cOwner VARCHAR2(40) := SUBSTR(cOwnerDotTableName,1,(INSTR(cOwnerDotTableName,'.')-1) ); cTableName VARCHAR2(40) :=
cIndexString VARCHAR2(1000); cSize VARCHAR2(20); cNullable VARCHAR2(10); cTemp1 VARCHAR2(1); cNoIndexesFound VARCHAR2(100) := 'No indexes were found for thistable';
bPrintColumns BOOLEAN := TRUE;
--bPrintColumns BOOLEAN := FALSE;
nLengthOfTableName NUMBER := LENGTH(cOwnerDotTableName) + 8;
--Programmer Defined Exceptions
eTableNameFormattedWrong EXCEPTION;
eExitTakeNoAction EXCEPTION;
,pcOwner VARCHAR2)IS
FROM all_tables WHERE table_name = pcTableName AND owner = pcOwner; CURSOR curAllTabColumns(pcTableName VARCHAR2, pcOwner VARCHAR2)IS
FROM all_tab_columns WHERE table_name = pcTableName AND owner = pcOwner ORDER BY column_id; CURSOR curAllIndexes(pcTableName VARCHAR2 ,pcOwner VARCHAR2)IS
FROM all_indexes WHERE table_name = pcTableName AND table_owner = pcOwner ORDER BY uniqueness DESC, index_name ASC; CURSOR curAllIndColumns(pcTableName VARCHAR2 ,pcOwner VARCHAR2 ,pcIndexName VARCHAR2)IS
FROM all_ind_columns WHERE index_name = pcIndexName AND table_owner = pcOwner AND table_name = pcTableName ORDER BY column_position DESC; CURSOR curAllConstraints(pcTableName VARCHAR2 ,pcOwner VARCHAR2)IS
FROM all_constraints WHERE table_name = pcTableName AND owner = pcOwner ORDER BY DECODE(constraint_type,'P',1 ,'U',2 ,'R',3 ,'C',4 ,'V',5,6) ,constraint_name;
CURSOR curAllUsers(pcOwner VARCHAR2)
IS
SELECT 'x'
FROM all_users WHERE username = pcOwner; ----------------------------------------------------------------BEGIN
DBMS_OUTPUT.PUT_LINE(RPAD('-',nLengthOfTableName,'-')); DBMS_OUTPUT.PUT_LINE('-- '||cOwnerDotTableName||' --'); DBMS_OUTPUT.PUT_LINE(RPAD('-',nLengthOfTableName,'-'));
--Verify that the owner.name is in correct format and that
RAISE eExitTakeNoAction;
END IF;
CLOSE curAllUsers;
END IF;
DBMS_OUTPUT.PUT_LINE('-');
cTableDoesNotExist := 'Verify table name '''||cTableName||''' is correct';
cStage := 'Open curAllTables';
FOR recAllTabs IN curAllTables(cTableName,cOwner)
LOOP
cTableDoesNotExist := NULL;
DBMS_OUTPUT.PUT_LINE('Table: '||recAllTabs.table_name||' Owner:
'||recAllTabs.owner);
DBMS_OUTPUT.PUT_LINE('Tablespace: '||recAllTabs.tablespace_name);
IF bPrintColumns
THEN
DBMS_OUTPUT.PUT_LINE('-');
cStage := 'Open curAllTabColumns'; DBMS_OUTPUT.PUT_LINE('Column DataType');
DBMS_OUTPUT.PUT_LINE('--------------------------------------------------'); FOR recAllTabCols IN curAllTabColumns(recAllTabs.table_name, recAllTabs.owner) LOOP IF recAllTabCols.data_type IN ('VARCHAR2','CHAR') THEN cSize := '('||recAllTabCols.data_length||')'; ELSIF recAllTabCols.data_type = 'NUMBER' THEN IF recAllTabCols.data_precision IS NULL THEN cSize := NULL; ELSE IF recAllTabCols.data_scale IS NULL THEN cSize := '('||recAllTabCols.data_precision||')'; ELSE cSize := '('||recAllTabCols.data_precision||','||recAllTabCols.data_scale||')'; END IF; END IF; END IF; IF recAllTabCols.nullable = 'N' THEN cNullable := 'NOT NULL'; ELSE cNullable := ' '; END IF;
DBMS_OUTPUT.PUT_LINE(rpad(recAllTabCols.column_name,31)||RPAD(cNullable,10)| |recAllTabCols.data_type||cSize);
END LOOP;
END IF;
DBMS_OUTPUT.PUT_LINE('-');
cStage := 'Open curAllIndexes';
DBMS_OUTPUT.PUT_LINE(RPAD('Index',30)||'Uniqueness Columns');
DBMS_OUTPUT.PUT_LINE('----------------------------- ------------ ----------- --------------------------');
FOR recAllInds IN curAllIndexes(recAllTabs.table_name, recAllTabs.owner) LOOP
--set this to null so message will not print cNoIndexesFound := NULL; cStage := 'Open curAllIndColumns'; cIndexString := NULL; FOR recAllIndCols IN curAllIndColumns(recAllTabs.table_name ,recAllTabs.owner ,recAllInds.index_name) LOOP cIndexString := recAllIndCols.column_name ||', '||cIndexString; END LOOP; cIndexString := RTRIM(cIndexString,', ');
DBMS_OUTPUT.PUT_LINE(RPAD(recAllInds.index_name,30)||rpad(recAllInds.uniquen ess,13)|| cIndexString);
END LOOP; --IF cNoIndexesFound is null nothing will print DBMS_OUTPUT.PUT_LINE(cNoIndexesFound);
DBMS_OUTPUT.PUT_LINE('-');
cStage := 'Open curAllConstraints';
DBMS_OUTPUT.PUT_LINE(RPAD('Constraint Name',30) ||' Status Type');
DBMS_OUTPUT.PUT_LINE(RPAD('-',30,'-')||RPAD(' -',10,'-')||RPAD(' -',15,'-')) ;
FOR recAllCons IN curAllConstraints(recAllTabs.table_name
,recAllTabs.owner) LOOP cStage := 'Get Constraint Type'; cNoConstraintsFound := NULL; --set this to null so message will not print IF recAllCons.constraint_type = 'C' THEN cConstraintType := 'Check'; ELSIF recAllCons.constraint_type = 'R' THEN cConstraintType := 'Foreign Key'; ELSIF recAllCons.constraint_type = 'P' THEN cConstraintType := 'Primary Key'; ELSIF recAllCons.constraint_type = 'U' THEN cConstraintType := 'Unique'; ELSIF recAllCons.constraint_type = 'V' THEN cConstraintType := 'With Check Option'; ELSE cConstraintType := recAllCons.constraint_type; END IF;
DBMS_OUTPUT.PUT_LINE(rpad(recAllCons.constraint_name,31)||recAllCons.status || ' '||cConstraintType);
END LOOP; --If cNoConstraintsFound is null nothing will print DBMS_OUTPUT.PUT_LINE(cNoConstraintsFound);
END LOOP; DBMS_OUTPUT.PUT_LINE(cTableDoesNotExist);--this will not print if cTableDoesNotExist is null
EXCEPTION
WHEN eTableNameFormattedWrong THEN
DBMS_OUTPUT.PUT_LINE('Please verify table name '''||cOwnerDotTableName
||''' is in the correct format. <owner>.<table name>');
WHEN eExitTakeNoAction THEN
NULL;--Take no action
WHEN OTHERS THEN
DBMS_OUTPUT.PUT_LINE('Error '||SQLERRM|| ' occurred at '||cStage);
END;
/
UNDEFINE pOwnerDotTableName
UNDEFINE 1
SET VERIFY ON
---End of Script
> > >hi, > >i asked u people about how to list pks and fks of a tables and >someone told me to select table name from the all_constraints >where constraint_type is 'P'. i tried it and find it works fine >if the table's pk contains only one columns. but if the pk is >a combination of different columns, it returns no row. what >should i do? > >thanx. > >rgds, >thenard > >-----== Posted via Deja News, The Leader in Internet Discussion ==----- >http://www.dejanews.com/rg_mkgrp.xp Create Your Own Free Member ForumReceived on Wed Sep 16 1998 - 00:00:00 CDT