Describe Tables
This page provides examples that let you query the database catalog, and discover the descriptions of all tables and views. The script limitations when running against an Oracle database is the schema, but you can modify to capture all tables and views in the database provided you enjoy the DBA role. The script limitations when running against MySQL and SQL Server databases limits you to a specific database.
Oracle
You can query the database catalog to discover much about the database. In fact, you can build a robust tool set for discovery if you’re not paying license fees for tools like Quest’s Toad. This shows you how to query the catalog to describe all tables, views, or both in an Oracle database. At least, it shows you how for a schema. If you want the all scheme, change the USER_ to DBA_ and with the DBA Role you can do so.
Since you probably don’t want to run a big query each time, and then play with the formatting, this shows you how to create a view. Then, it provides you with a SQL*Plus enabled reporting script.
CREATE OR REPLACE VIEW schema_tables AS SELECT o.object_type AS object_type , c.table_name AS TABLE_NAME , c.column_id AS column_id , c.column_name AS column_name , DECODE(c.nullable,'N','NOT NULL','') AS NULLABLE , DECODE(c.data_type , 'BFILE' ,'BINARY FILE LOB' , 'BINARY_FLOAT' ,c.data_type , 'BINARY_DOUBLE',c.data_type , 'BLOB' ,c.data_type , 'CLOB' ,c.data_type , 'CHAR' ,DECODE(NVL(c.data_length,0),0,c.data_type , c.data_type||'('||c.data_length||')') , 'DATE' ,c.data_type , 'FLOAT' ,c.data_type , 'LONG RAW' ,c.data_type , 'NCHAR' ,DECODE(NVL(c.data_length,0),0,c.data_type , c.data_type||'('||c.data_length||')') , 'NVARCHAR2' ,DECODE(NVL(c.data_length,0),0,c.data_type , c.data_type||'('||c.data_length||')') , 'NUMBER' ,DECODE(NVL(c.data_precision||c.data_scale,0) , 0,c.data_type , DECODE(NVL(c.data_scale,0),0 , c.data_type||'('||c.data_precision||')' , c.data_type||'('||c.data_precision||','|| c.data_scale||')')) , 'RAW' ,DECODE(NVL(c.data_length,0),0,c.data_type , c.data_type||'('||c.data_length||')') , 'VARCHAR' ,DECODE(NVL(c.data_length,0),0,c.data_type , c.data_type||'('||c.data_length||')') , 'VARCHAR2' ,DECODE(NVL(c.data_length,0),0,c.data_type , c.data_type||'('||c.data_length||')') , 'TIMESTAMP' , c.data_type,c.data_type) AS data_type , CASE WHEN c.data_default IS NULL THEN 'N' ELSE 'Y' END AS data_default FROM user_tab_columns c,user_objects o WHERE o.object_name = c.table_name ORDER BY c.table_name, c.column_id; |
CREATE OR REPLACE VIEW schema_tables AS SELECT o.object_type AS object_type , c.table_name AS table_name , c.column_id AS column_id , c.column_name AS column_name , DECODE(c.nullable,'N','NOT NULL','') AS nullable , DECODE(c.data_type , 'BFILE' ,'BINARY FILE LOB' , 'BINARY_FLOAT' ,c.data_type , 'BINARY_DOUBLE',c.data_type , 'BLOB' ,c.data_type , 'CLOB' ,c.data_type , 'CHAR' ,DECODE(NVL(c.data_length,0),0,c.data_type , c.data_type||'('||c.data_length||')') , 'DATE' ,c.data_type , 'FLOAT' ,c.data_type , 'LONG RAW' ,c.data_type , 'NCHAR' ,DECODE(NVL(c.data_length,0),0,c.data_type , c.data_type||'('||c.data_length||')') , 'NVARCHAR2' ,DECODE(NVL(c.data_length,0),0,c.data_type , c.data_type||'('||c.data_length||')') , 'NUMBER' ,DECODE(NVL(c.data_precision||c.data_scale,0) , 0,c.data_type , DECODE(NVL(c.data_scale,0),0 , c.data_type||'('||c.data_precision||')' , c.data_type||'('||c.data_precision||','|| c.data_scale||')')) , 'RAW' ,DECODE(NVL(c.data_length,0),0,c.data_type , c.data_type||'('||c.data_length||')') , 'VARCHAR' ,DECODE(NVL(c.data_length,0),0,c.data_type , c.data_type||'('||c.data_length||')') , 'VARCHAR2' ,DECODE(NVL(c.data_length,0),0,c.data_type , c.data_type||'('||c.data_length||')') , 'TIMESTAMP' , c.data_type,c.data_type) AS data_type , CASE WHEN c.data_default IS NULL THEN 'N' ELSE 'Y' END AS data_default FROM user_tab_columns c,user_objects o WHERE o.object_name = c.table_name ORDER BY c.table_name, c.column_id;
After you create the schema_tables view, you can query the contents and format the output meaningfully with the following script:
CLEAR BREAKS CLEAR COLUMNS CLEAR COMPUTES TTITLE OFF SET ECHO ON SET FEEDBACK OFF SET NULL '' SET PAGESIZE 999 SET PAUSE OFF SET TERM ON SET TIME OFF SET TIMING OFF SET VERIFY OFF ACCEPT INPUT PROMPT "Enter [TABLE | VIEW ] unless you want both: " SET HEADING ON TTITLE LEFT o1 o2 ' >' SKIP 1 - '--------------------------------------------------------' SKIP 1 CLEAR COLUMNS CLEAR BREAKS BREAK ON REPORT BREAK ON c2 SKIP PAGE COL c1 NEW_VALUE o1 NOPRINT COL c2 NEW_VALUE o2 NOPRINT COL c3 FORMAT A32 HEADING "Name" COL c4 FORMAT A8 HEADING "Null?" COL c5 FORMAT A33 HEADING "Type" COL c6 FORMAT A1 HEADING "Default?" SELECT DECODE(st.object_type,'TABLE','Table Name: < ' , 'VIEW' ,'View Name: < ') c1 , st.table_name c2 , st.column_name c3 , st.nullable c4 , st.data_type c5 , st.data_default c6 FROM schema_tables st WHERE st.table_name LIKE UPPER('&input')||'%' ORDER BY st.table_name , st.column_id; |
CLEAR BREAKS CLEAR COLUMNS CLEAR COMPUTES TTITLE OFF SET ECHO ON SET FEEDBACK OFF SET NULL '' SET PAGESIZE 999 SET PAUSE OFF SET TERM ON SET TIME OFF SET TIMING OFF SET VERIFY OFF ACCEPT input PROMPT "Enter [TABLE | VIEW ] unless you want both: " SET HEADING ON TTITLE LEFT o1 o2 ' >' SKIP 1 - '--------------------------------------------------------' SKIP 1 CLEAR COLUMNS CLEAR BREAKS BREAK ON REPORT BREAK ON c2 SKIP PAGE COL c1 NEW_VALUE o1 NOPRINT COL c2 NEW_VALUE o2 NOPRINT COL c3 FORMAT A32 HEADING "Name" COL c4 FORMAT A8 HEADING "Null?" COL c5 FORMAT A33 HEADING "Type" COL c6 FORMAT A1 HEADING "Default?" SELECT DECODE(st.object_type,'TABLE','Table Name: < ' , 'VIEW' ,'View Name: < ') c1 , st.table_name c2 , st.column_name c3 , st.nullable c4 , st.data_type c5 , st.data_default c6 FROM schema_tables st WHERE st.table_name LIKE UPPER('&input')||'%' ORDER BY st.table_name , st.column_id;
This should provide you with a formatted view of the tables and views that looks like this:
TABLE Name: < MILITARY_LOOKUP > -------------------------------------------------------- Name NULL? TYPE D -------------------------------- -------- --------------------------------- - MILITARY_LOOKUP_ID NOT NULL NUMBER N TABLE_NAME NOT NULL VARCHAR2(30) N COLUMN_NAME NOT NULL VARCHAR2(30) N LOOKUP_TYPE NOT NULL VARCHAR2(30) N LOOKUP_VALUE NOT NULL VARCHAR2(30) N LOOKUP_MEANING NOT NULL VARCHAR2(80) N LOOKUP_CODE VARCHAR2(10) N CREATED_BY NUMBER N CREATION_DATE NOT NULL DATE Y LAST_UPDATED_BY NUMBER N LAST_UPDATE_DATE NOT NULL DATE Y |
Table Name: < MILITARY_LOOKUP > -------------------------------------------------------- Name Null? Type D -------------------------------- -------- --------------------------------- - MILITARY_LOOKUP_ID NOT NULL NUMBER N TABLE_NAME NOT NULL VARCHAR2(30) N COLUMN_NAME NOT NULL VARCHAR2(30) N LOOKUP_TYPE NOT NULL VARCHAR2(30) N LOOKUP_VALUE NOT NULL VARCHAR2(30) N LOOKUP_MEANING NOT NULL VARCHAR2(80) N LOOKUP_CODE VARCHAR2(10) N CREATED_BY NUMBER N CREATION_DATE NOT NULL DATE Y LAST_UPDATED_BY NUMBER N LAST_UPDATE_DATE NOT NULL DATE Y
MySQL
… under development …
SQL Server
… under development …
Written by michaelmclaughlin
January 17th, 2010 at 9:31 pm
Posted in
