REM
/*
 * SQLKickStart.Sql (c) Tim Onions 2007
 * Please check this script works as expected on a test system before using on a production database.
 * The author can accept no liability or responsibility for any ill-effect it may have.
 * You are free to use this script on one condition - use it to improve the performance of your database!
 */
ACCEPT table_name PROMPT "Enter table name to SQL*Plus SQL for for: "

SET VER off ECHO off PAGES 0 HEAD off FEEDBACK off
SELECT 'COLUMN '
      ||RPAD(LOWER(column_name),32)
      ||' PRINT FORMAT '
      ||(CASE data_type WHEN 'VARCHAR2' THEN 'A'||GREATEST(data_length,LENGTH(column_name))
                        WHEN 'VARCHAR'  THEN 'A'||GREATEST(data_length,LENGTH(column_name))
                        WHEN 'CHAR'     THEN 'A'||GREATEST(data_length,LENGTH(column_name))
                        WHEN 'NUMBER'   THEN RPAD('9',NVL(data_precision,data_length),'9')||(CASE WHEN data_scale>0 THEN RPAD('.',data_scale,'9') END)
                        WHEN 'DATE'     THEN 'A'||GREATEST(LENGTH(sys_context( 'userenv', 'nls_date_format' )),LENGTH(column_name))
         END)
      ||' HEADING "'||INITCAP(REPLACE(column_name,'_',' '))||'"'
FROM user_tab_columns 
WHERE table_name LIKE UPPER('&&table_name')
ORDER BY column_id
/
SELECT DECODE(rownum,1,'SELECT ',
                       '      ,')||lower(column_name)
FROM (SELECT column_name
        FROM user_tab_columns 
       WHERE table_name LIKE UPPER('&&table_name')
       ORDER BY column_id)
UNION ALL
SELECT 'FROM '||LOWER('&&table_name') FROM dual
/

SET PAGES 60 HEAD on FEEDBACK on

SET DOC off
/* SUMMARY - Create COLUMN statements for all columns in a table and produce first cut SELECT  FROM  for use with SQL*Plus
 *
 * DETAIL - When in SQL*Plus it is really easy to interrogate tables using SELECT * FROM mytable; etc. However, for anything but the simplest of tables
 *          the output gets messy and difficult to read. To format the output SQL*Plus allow syou to create COLUMN commands, to define the field types, sizes
 *          and headings. Typing these in for each column can be, let us say, a bit boring as can writing nicely formatted SQL code.
 *
 *          This script uses the USER_TAB_COLUMNS data dictionary view to produce a first cut of COLUMN commands - one for each column in a given table with the FORMAT
 *          clause defined based on the data type and length. The column name is used as the HEADING in INITCAP with underscores replaced with spaces.
 *          
 *          It also creates a neatly indented SELECT .. FROM .. statement with each column specifically listed. The idea is that you can then cut-and-paste the sample code
 *          and either run it directly within SQL*Plus or place it in a script to be run later. It's not recket science but sure beats having to type line after lines of code
 *          (and then go back and fix all the typos that always seem to get introduced).
 *
 *          Sample code might look like:
SCOTT@RACDB> start sqlkickstart
Enter table name to SQL*Plus SQL for for: EMP
COLUMN empno                            FORMAT 9999 HEADING "Empno"
COLUMN ename                            FORMAT A10 HEADING "Ename"
COLUMN job                              FORMAT A9 HEADING "Job"
COLUMN mgr                              FORMAT 9999 HEADING "Mgr"
COLUMN hiredate                         FORMAT A22 HEADING "Hiredate"
COLUMN sal                              FORMAT 9999999.9 HEADING "Sal"
COLUMN comm                             FORMAT 9999999.9 HEADING "Comm"
COLUMN deptno                           FORMAT 99 HEADING "Deptno"
SELECT empno
      ,ename
      ,job
      ,mgr
      ,hiredate
      ,sal
      ,comm
      ,deptno
FROM emp

 */
REM