Finding the total number of constraints on all tables of the database
I have written a dynamic query (cursor) for finding the total number of constraints for each constraint type on all tables of the database. Here I am submitting the cursor for your reference. It is not working.
declare totcons number(12); tabname varchar2(30); constype varchar2(1); str varchar2(200); cursor test_ROW is select table_name,constraint_type from dba_constraints; BEGIN OPEN TEST_ROW; FETCH TEST_ROW INTO TABNAME,constype; LOOP exit when test_row %notfound; str := 'select count( * ) from dba_constraints where table_NAME = ' || TABNAME || ' AND CONSTRAINT_TYPE = '|| CONSTYPE ; DBMS_OUTPUT.PUT_LINE( STR ) ; execute immediate str into totcons; DBMS_OUTPUT.PUT_LINE( TO_CHAR(TOTCONS) ) ; insert into tab_cons values (tabname,constype,totcons); FETCH TEST_ROW INTO TABNAME,constype; END LOOP; CLOSE TEST_ROW; END;
This can be done much more easily with straight SQL. Try the following query:
SELECT constraint_type, count(*) AS num_constraints FROM dba_constraints GROUP BY constraint_type;This simple query will provide the data you require.
For More Information
- What do you think about this answer? E-mail the editors at [email protected] with your feedback.
- The Best Oracle Web Links: tips, tutorials, scripts, and more.
- Have an Oracle or SQL tip to offer your fellow DBAs and developers? The best tips submitted will receive a cool prize. Submit your tip today!
- Ask your technical Oracle and SQL questions -- or help out your peers by answering them -- in our live discussion forums.
- Ask the Experts yourself: Our SQL, database design, Oracle, SQL Server, DB2, metadata, object-oriented and data warehousing gurus are waiting to answer your toughest questions.