Generate SELECT For All Columns

I have a table with a long list of column names and I want to build a SELECT statement from the catalog metadata. If I supply schema (library) and table (physical file) names, is there a way to generate a SELECT statement?

—Four Hundred Guru Reader

Thanks to dynamic compound statements and global variables, the answer is yes, it’s fairly easy to construct a SQL statement for a table or view as shown below:

/* Build Select Statement and stuff it in QGPL.TEMPSQL variable */
BEGIN
    -- Fill in your table/schema name
    DECLARE @TABLE_NAME   VARCHAR(128) NOT NULL 
                          DEFAULT 'SYSCOLUMNS';
    DECLARE @TABLE_SCHEMA VARCHAR(128) NOT NULL 
                          DEFAULT 'QSYS2';
    DECLARE @SQL          VARCHAR(24576) NOT NULL 
                          DEFAULT '';

    -- Create QGPL.TEMPSQL variable if it doesn't exist
    IF NOT EXISTS (
    SELECT *
      FROM QSYS2.SYSVARIABLES
     WHERE VARIABLE_SCHEMA='QGPL' 
       AND VARIABLE_NAME='TEMPSQL') THEN
        EXECUTE IMMEDIATE 'CREATE VARIABLE QGPL.TEMPSQL VARCHAR(24576)';
    END IF;

    -- Build comma delimited column name list
    FOR TEMP AS COLUMNS CURSOR FOR
    SELECT COLUMN_NAME
      FROM QSYS2.SYSCOLUMNS
     WHERE TABLE_SCHEMA=@TABLE_SCHEMA
       AND TABLE_NAME=@TABLE_NAME
    DO
        SET @SQL = @SQL
                   || CASE WHEN LENGTH(@SQL)>0 THEN ',' ELSE '' END
                   || COLUMN_NAME;
    END FOR;

    -- Build SQL statement
    SET @SQL='SELECT '||@SQL||
              ' FROM '||@TABLE_SCHEMA||'.'||@TABLE_NAME;

    -- Save SQL statement in variable
    SET QGPL.TEMPSQL=@SQL;
END
;
-- Display the SQL statement
VALUES(QGPL.TEMPSQL);

The result is:

SELECT COLUMN_NAME,TABLE_NAME,TABLE_OWNER,ORDINAL_POSITION,DATA_TYPE,LENGTH,NUMERIC_
SCALE,IS_NULLABLE,IS_UPDATABLE,LONG_COMMENT,HAS_DEFAULT,COLUMN_HEADING,STORAGE,
NUMERIC_PRECISION,CCSID,TABLE_SCHEMA,COLUMN_DEFAULT,CHARACTER_MAXIMUM_LENGTH,
CHARACTER_OCTET_LENGTH,NUMERIC_PRECISION_RADIX,DATETIME_PRECISION,COLUMN_TEXT,
SYSTEM_COLUMN_NAME,SYSTEM_TABLE_NAME,SYSTEM_TABLE_SCHEMA,USER_DEFINED_TYPE_SCHEMA,
USER_DEFINED_TYPE_NAME,IS_IDENTITY,IDENTITY_GENERATION,IDENTITY_START,
IDENTITY_INCREMENT,IDENTITY_MINIMUM,IDENTITY_MAXIMUM,IDENTITY_CYCLE,IDENTITY_
CACHE,IDENTITY_ORDER,COLUMN_EXPRESSION,HIDDEN,HAS_FLDPROC FROM QSYS2.SYSCOLUMNS

This isn’t the prettiest to look at but it beats typing everything by hand. It’s easy to modify the code to insert a carriage return/line feed after each column name if, for example, each column is preferred on its own line. It’s also easy to modify the code to ignore columns with certain data types, unwanted column names, etc.

This same principle can be applied to automatically build the base of INSERT/VALUES and UPDATE statements. For an advanced treatment of a similar topic involving the generation of INSERT statements with data, see Paul Touhy’s tip Generating An Insert Script From Existing Data.

This script should be run in a graphical SQL tool like iNavigator’s RunSQL scripts. The above statements can be run in STRSQL but you first have to remove the double-hyphenated comments and remove the semicolons after the dynamic compound statement and after the VALUES statement.

–Mike Sansoterra

Michael Sansoterra is a DBA for Broadway Systems in Grand Rapids, Michigan. Send your questions or comments for Mike via the IT Jungle Contact page.

RELATED STORY

Generating An Insert Script From Existing Data