Database table descriptions

Products

• CA Harvest Software Change Manager
• CA Harvest Software Change Manager - OpenMake Meister

Issue/Introduction

Is it possible to generate a detailed listing of table descriptions for the Harvest database?

Environment

Release : All versions

Component : CA Harvest Software Change Manager

Resolution

If your Harvest database is on the Oracle DBMS you can use this SQL*Plus script to generate table and column descriptions for any version of Harvest:

---------

column table_name new_value tblnm noprint
column table_comments new_value tblcom noprint
column column_name format a30
column data_type format a10
column comments format a1000

set wrap on
set linesize 1100
set pagesize 5000
set trimspool on
set trimout on
set colsep '    '

ttitle left 'Table: ' tblnm skip 1 tblcom skip 2
break on table_name skip page
btitle off
spool erd.txt

SELECT 
  user_tables.TABLE_NAME,
  user_tab_comments.COMMENTS AS TABLE_COMMENTS,
  user_tab_cols.COLUMN_NAME,
  user_tab_cols.DATA_TYPE,
  user_tab_cols.DATA_LENGTH,
  DECODE(user_tab_cols.NULLABLE, 'Y', 'NULLABLE','') AS NULLABLE,
  user_col_comments.COMMENTS
FROM user_tables
LEFT JOIN user_tab_comments
ON user_tables.TABLE_NAME = user_tab_comments.TABLE_NAME
INNER JOIN user_tab_cols
ON user_tables.TABLE_NAME = user_tab_cols.TABLE_NAME
LEFT JOIN user_col_comments
ON user_tab_cols.TABLE_NAME   = user_col_comments.TABLE_NAME
AND user_tab_cols.COLUMN_NAME = user_col_comments.COLUMN_NAME
WHERE user_tables.TEMPORARY != 'Y'
ORDER BY user_tables.TEMPORARY,
  user_tables.TABLE_NAME,
  user_tab_cols.COLUMN_NAME;

---------