Archives of the TeradataForum

Message Posted: Wed, 09 Nov 2005 @ 11:47:36 GMT

	<Prev	Next>		<<First	<Prev	Next>	Last>>

According to ACCESSLOG (details below) it looks like SQA\ODBC uses a combination of dbc.columns and help column db.table.*.

I think the help column is used for the data types.

We also do this here to populate our Metadata website.

I agree that I have never seen dbc.columns contain data types for views.

..kiki..
Continental Airlines

     SELECT
     NULL(VARCHAR(30)),DATABASENAME(VARCHAR(30)),TRIM(TABLENAME(VARCHAR(30)))
     ,TRIM(SUBSTR('TABLE       VIEW        MACRO       JOURNAL     JOIN INDEX
     SYSTEM TABLESYSTEM TABLESYSTEM MACROSYSTEM JOURNSYSTEM
     JINDX',(((INDEX('TVMJI',TABLEKIND)-1)*12)+1+INDEX('!'||DATABASENAME,'!DBC          ')*60),12))(NAMED
     TABLE_TYPE),COMMENTSTRING(VARCHAR(254),NAMED REMARKS)
     FROM DBC.TABLES
     WHERE UPPER(TRIM(DATABASENAME)) LIKE UPPER(TRIM('DBNAME_HERE')) ESCAPE '\'
       AND UPPER(TRIM(TABLENAME)) LIKE UPPER(TRIM('%')) ESCAPE '\'
       AND (TABLEKIND <> 'M'
       AND TABLEKIND <> 'J'
       AND TABLEKIND <> 'I'
       AND TABLEKIND <> 'G'
       AND TABLEKIND = 'T'
       OR TABLEKIND = 'V')
     ORDER BY 4,1,2,3;

     SELECT TRIM(COLUMNNAME)
     FROM DBC.COLUMNS
     WHERE UPPER(TRIM(COLUMNNAME)) LIKE UPPER(TRIM('%')) ESCAPE '\'
       AND UPPER(TRIM(DATABASENAME)) LIKE
     UPPER(TRIM('DBNAME_HERE')) ESCAPE '\'
       AND UPPER(TRIM(TABLENAME)) LIKE
     UPPER(TRIM('TABLENAME_HERE')) ESCAPE '\'
     GROUP BY COLUMNNAME
     ORDER BY 1;

     SELECT TRIM(DATABASENAME),TRIM(TABLENAME)
     FROM DBC.TABLES
     WHERE UPPER(TRIM(DATABASENAME)) LIKE UPPER(TRIM('DBNAME_HERE')) ESCAPE '\'
       AND UPPER(TRIM(TABLENAME)) LIKE
     UPPER(TRIM('TABLENAME_HERE')) ESCAPE '\'
       AND TABLEKIND IN ('T','V')
     ORDER BY 1,2;

     HELP TABLE "DBNAME_HERE"."TABLENAME_HERE";

     HELP COLUMN "DBNAME_HERE"."TABLENAME_HERE".*;

	<Prev	Next>		<<First	<Prev	Next>	Last>>