CODEWITHSUNDEEP
sqloracle-database
Princesden
Princesden

Reputation: 169

List table and columns in one statement

How would you in oracle SQL print the name of each table and the columns within that table- by printing the table name once followed by each column within that table on a separate line.

Format should be something like this:

Table1
  columns
Table2
  Columns

Upvotes: 0

Views: 134

Answers (1)

abhi
abhi

Reputation: 3136

This should do the trick.

    DECLARE 
TNAME user_tables.table_name%TYPE;
CNAME user_tab_columns.column_name%TYPE;
CURSOR ct1
IS SELECT table_name from user_tables;
CURSOR ct2 IS SELECT column_name FROM user_tab_columns WHERE table_name =TNAME;
BEGIN
OPEN ct1;
LOOP
FETCH ct1 INTO TNAME;
EXIT WHEN ct1%NOTFOUND;
DBMS_OUTPUT.PUT_LINE('TABLENAME:-->'|| TRIM(TNAME));
      OPEN ct2;
      LOOP
      FETCH ct2 INTO CNAME;
      EXIT WHEN ct2%NOTFOUND;
      DBMS_OUTPUT.PUT_LINE('             '|| TRIM(CNAME));
      END LOOP;
      CLOSE CT2;
END LOOP;
CLOSE ct1;
END;

Upvotes: 1

Related Questions