Home » RDBMS Server » Server Administration » procedure to display info about a table
| procedure to display info about a table [message #374127] |
Fri, 25 May 2001 19:58  |
ashley
Messages: 3
Registered: May 2001
|
Junior Member |
|
|
I am working on a lab for my class and part of it is to
create a procedure that will display all the fields in
a table, along with the keys, relations, and constaints
associated with the table. Any advice would be great. I
am somewhat of a beginner and am a little lost.
Thanks so much!
|
|
|
|
| Re: procedure to display info about a table [message #374146 is a reply to message #374127] |
Mon, 28 May 2001 05:08  |
Martin
Messages: 83
Registered: February 2000
|
Member |
|
|
Here's one I prepared earlier
DECLARE
v_table_name VARCHAR2(30);
v_index_name VARCHAR2(30);
v_index_description VARCHAR2(2000);
v_constraint_name VARCHAR2(2000);
v_constraint_description VARCHAR2(2000);
v_foreign_table VARCHAR2(30);
v_foreign_cols VARCHAR2(2000);
v_trigger_name VARCHAR2(30);
v_trigger_description VARCHAR2(2000);
v_grant_description VARCHAR2(2000);
CURSOR cur_tables IS
SELECT TABLE_NAME FROM USER_TABLES where table_name not like 'EUL%' and TABLE_NAME not LIKE 'SMP%';
CURSOR cur_table_desc IS
SELECT COLUMN_NAME,
DECODE(NULLABLE , 'Y',' ', 'NOT NULL') AS NULLABLE,
DATA_TYPE,
DATA_LENGTH,
DATA_PRECISION,
DATA_SCALE,
DATA_DEFAULT
FROM USER_TAB_COLUMNS
WHERE TABLE_NAME = v_table_name;
CURSOR cur_indexes IS
SELECT INDEX_NAME
FROM USER_INDEXES
WHERE TABLE_NAME = v_table_name;
CURSOR cur_index_cols IS
SELECT COLUMN_NAME
FROM USER_IND_COLUMNS
WHERE TABLE_NAME = v_table_name AND INDEX_NAME = v_index_name;
CURSOR cur_constraints IS
SELECT CONSTRAINT_NAME ,
CONSTRAINT_TYPE,
SEARCH_CONDITION,
R_CONSTRAINT_NAME
FROM USER_CONSTRAINTS
WHERE TABLE_NAME = v_table_name;
CURSOR cur_cons_cols IS
SELECT TABLE_NAME , COLUMN_NAME
FROM USER_CONS_COLUMNS
WHERE CONSTRAINT_NAME = v_constraint_name;
CURSOR cur_triggers IS
SELECT TRIGGER_NAME,
TRIGGER_TYPE,
TRIGGERING_EVENT,
STATUS
FROM USER_TRIGGERS
WHERE TABLE_NAME = v_table_name;
CURSOR cur_grants_made IS
SELECT * FROM USER_TAB_PRIVS_MADE
WHERE TABLE_NAME = v_table_name;
BEGIN
FOR rec_tables IN cur_tables
LOOP
-- output the table description data
v_table_name := rec_tables.TABLE_NAME;
DBMS_OUTPUT.PUT_LINE(' ');
DBMS_OUTPUT.PUT_LINE(' ');
DBMS_OUTPUT.PUT_LINE('Table definition for ' || v_table_name);
DBMS_OUTPUT.PUT_LINE(' ');
DBMS_OUTPUT.PUT_LINE(' ');
DBMS_OUTPUT.PUT_LINE (RPAD('Column Name', 30) || ' ' ||
RPAD('NULL ?', 7) || ' ' ||
RPAD('Data Type', 9)|| ' ' ||
RPAD('Length',8) || ' ' ||
RPAD('Precision' ,10) || ' ' ||
RPAD('Scale',8) || ' ' ||
RPAD('Default' ,8));
DBMS_OUTPUT.PUT_LINE (RPAD('-', 86, '-'));
FOR rec_table_desc IN cur_table_desc
LOOP
DBMS_OUTPUT.PUT_LINE (RPAD(rec_table_desc.COLUMN_NAME, 30) || '' ||
RPAD(rec_table_desc.NULLABLE , 8) || ' ' ||
RPAD(rec_table_desc.DATA_TYPE, 9)|| ' ' ||
RPAD(rec_table_desc.DATA_LENGTH,8) || ' ' ||
RPAD(rec_table_desc.DATA_PRECISION,10) || ' ' ||
RPAD(rec_table_desc.DATA_SCALE,8) || ' ' ||
RPAD(rec_table_desc.DATA_DEFAULT,8));
END LOOP;
-- output data about constraints
DBMS_OUTPUT.PUT_LINE(' ');
DBMS_OUTPUT.PUT_LINE(' ');
DBMS_OUTPUT.PUT_LINE('Constraints ');
DBMS_OUTPUT.PUT_LINE(' ');
DBMS_OUTPUT.PUT_LINE(' ');
DBMS_OUTPUT.PUT_LINE (RPAD('Constraint Name', 30) || ' ' ||
RPAD('Constraint Type', 20));
DBMS_OUTPUT.PUT_LINE (RPAD('-', 86, '-'));
FOR rec_constraints IN cur_constraints
LOOP
v_constraint_name := rec_constraints.CONSTRAINT_NAME;
v_constraint_description := RPAD(v_constraint_name, 30) || ' ';
IF rec_constraints.CONSTRAINT_TYPE = 'R' THEN
v_constraint_description := v_constraint_description || RPAD('Foreign Key', 20) || '(';
FOR rec_cons_cols IN cur_cons_cols
LOOP
v_constraint_description := v_constraint_description || rec_cons_cols.COLUMN_NAME || ',';
END LOOP;
-- remove the last character
v_constraint_description := SUBSTR(v_constraint_description, 1, LENGTH(v_constraint_description) - 1);
v_constraint_description := v_constraint_description || ')';
-- GET THE foreign table name and columns
v_constraint_name := rec_constraints.R_CONSTRAINT_NAME;
v_foreign_cols := '(';
FOR rec_cons_cols IN cur_cons_cols
LOOP
v_foreign_cols := v_foreign_cols || rec_cons_cols.COLUMN_NAME || ',';
v_foreign_table := rec_cons_cols.TABLE_NAME;
END LOOP;
-- remove the last character
v_foreign_cols := SUBSTR(v_foreign_cols, 1, LENGTH(v_foreign_cols) - 1);
v_foreign_cols := v_foreign_cols || ')';
v_constraint_description := v_constraint_description || ' Foreign Key table is ' || v_foreign_table || ' ' || v_foreign_cols;
ELSIF rec_constraints.CONSTRAINT_TYPE = 'C' THEN
v_constraint_description := v_constraint_description || RPAD('Check Constraint ', 20) || '(';
FOR rec_cons_cols IN cur_cons_cols
LOOP
v_constraint_description := v_constraint_description || rec_cons_cols.COLUMN_NAME || ',';
END LOOP;
-- remove the last character
v_constraint_description := SUBSTR(v_constraint_description, 1, LENGTH(v_constraint_description) - 1);
v_constraint_description := v_constraint_description || ')';
-- GET THE foreign table name and columns
v_constraint_name := rec_constraints.SEARCH_CONDITION;
v_constraint_description := v_constraint_description || ' Check constraint is ' || v_constraint_name;
ELSIF rec_constraints.CONSTRAINT_TYPE = 'U' THEN
v_constraint_description := v_constraint_description || RPAD('Unique Key', 20) || '(';
FOR rec_cons_cols IN cur_cons_cols
LOOP
v_constraint_description := v_constraint_description || rec_cons_cols.COLUMN_NAME || ',';
END LOOP;
-- remove the last character
v_constraint_description := SUBSTR(v_constraint_description, 1, LENGTH(v_constraint_description) - 1);
v_constraint_description := v_constraint_description || ')';
ELSIF rec_constraints.CONSTRAINT_TYPE = 'P' THEN
v_constraint_description := v_constraint_description || RPAD('Primary Key', 20) || '(';
FOR rec_cons_cols IN cur_cons_cols
LOOP
v_constraint_description := v_constraint_description || rec_cons_cols.COLUMN_NAME || ',';
END LOOP;
-- remove the last character
v_constraint_description := SUBSTR(v_constraint_description, 1, LENGTH(v_constraint_description) - 1);
v_constraint_description := v_constraint_description || ')';
END IF;
IF LENGTH(v_constraint_description) <= 255 THEN
DBMS_OUTPUT.PUT_LINE(v_constraint_description);
ELSE
WHILE LENGTH(v_constraint_description) > 255
LOOP
DBMS_OUTPUT.PUT_LINE(SUBSTR(v_constraint_description, 1, 255));
v_constraint_description := SUBSTR(v_constraint_description, 256);
END LOOP;
DBMS_OUTPUT.PUT_LINE(v_constraint_description);
END IF;
END LOOP;
-- output data about indexes
DBMS_OUTPUT.PUT_LINE(' ');
DBMS_OUTPUT.PUT_LINE(' ');
DBMS_OUTPUT.PUT_LINE('Indexes ');
DBMS_OUTPUT.PUT_LINE(' ');
DBMS_OUTPUT.PUT_LINE(' ');
DBMS_OUTPUT.PUT_LINE (RPAD('Index Name', 30) || ' ' ||
RPAD('Indexed Columns', 20));
DBMS_OUTPUT.PUT_LINE (RPAD('-', 86, '-'));
v_index_description := NULL;
FOR rec_indexes IN cur_indexes
LOOP
v_index_description := RPAD(rec_indexes.INDEX_NAME, 30) || ' ';
v_index_description := v_index_description || '(';
v_index_name := rec_indexes.INDEX_NAME;
FOR rec_index_columns IN cur_index_cols
LOOP
v_index_description := v_index_description || rec_index_columns.COLUMN_NAME || ',';
END LOOP;
-- remove the last character
v_index_description := SUBSTR(v_index_description, 1, LENGTH(v_index_description) - 1);
v_index_description := v_index_description || ')';
DBMS_OUTPUT.PUT_LINE(v_index_description);
END LOOP;
IF v_index_description = NULL THEN
DBMS_OUTPUT.PUT_LINE('(None)');
END IF;
-- TRIGGERS
DBMS_OUTPUT.PUT_LINE(' ');
DBMS_OUTPUT.PUT_LINE(' ');
DBMS_OUTPUT.PUT_LINE('Triggers ');
DBMS_OUTPUT.PUT_LINE(' ');
DBMS_OUTPUT.PUT_LINE(' ');
DBMS_OUTPUT.PUT_LINE (RPAD('Trigger Name', 30) || ' ' ||
RPAD('Trigger Type', 20)|| ' ' ||
RPAD('Triggering Event',25) || ' ' ||
RPAD('Status' ,10));
DBMS_OUTPUT.PUT_LINE (RPAD('-', 86, '-'));
FOR rec_triggers IN cur_triggers
LOOP
v_trigger_description := RPAD(rec_triggers.TRIGGER_NAME, 31) ||
RPAD(rec_triggers.TRIGGER_TYPE, 21) ||
RPAD(rec_triggers.TRIGGERING_EVENT, 26) ||
RPAD(rec_triggers.STATUS, 11) ;
DBMS_OUTPUT.PUT_LINE(v_trigger_description);
END LOOP;
IF v_trigger_description IS NULL THEN
DBMS_OUTPUT.PUT_LINE('(None)');
END IF;
-- GRANTS
DBMS_OUTPUT.PUT_LINE(' ');
DBMS_OUTPUT.PUT_LINE(' ');
DBMS_OUTPUT.PUT_LINE('Grants ');
DBMS_OUTPUT.PUT_LINE(' ');
DBMS_OUTPUT.PUT_LINE(' ');
DBMS_OUTPUT.PUT_LINE(RPAD( 'Granted to ', 21) ||
RPAD('On Table', 31) ||
RPAD('Granted by', 21) ||
RPAD('Privilege', 11) ||
RPAD('Grantable', 10));
DBMS_OUTPUT.PUT_LINE(RPAD('-', 94, '-' ));
FOR rec_grants_made IN cur_grants_made
LOOP
v_grant_description := RPAD( rec_grants_made.GRANTEE, 21) ||
RPAD(rec_grants_made.TABLE_NAME, 31) ||
RPAD(rec_grants_made.GRANTOR, 21) ||
RPAD(rec_grants_made.PRIVILEGE, 11) ||
RPAD(rec_grants_made.GRANTABLE, 10);
DBMS_OUTPUT.PUT_LINE(v_grant_description);
END LOOP;
IF v_grant_description IS NULL THEN
DBMS_OUTPUT.PUT_LINE('(None)');
END IF;
END LOOP;
EXCEPTION WHEN OTHERS THEN
DBMS_OUTPUT.PUT_LINE('(Table causing error is ' || v_table_name || ' )');
END;
/
|
|
|
|
Goto Forum:
Current Time: Mon Dec 23 12:25:38 CST 2024
|
] 
Blog
Search
Help
Members
Register
Login
Home
