Harvest database schema listing and table descriptions (Oracle and SQL Server)

Products

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

Issue/Introduction

If there is a need to understand how and where certain types of data are stored within the Harvest database, such as for creating BIRT reporting queries, a report listing all the tables and columns within the database would be helpful.

Environment

CA Harvest Software Change Manager
V5.x, 7.x, 12.x, 14.x and higher
Oracle and SQL Server databases

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;

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

SELECT 
    t.name AS [Table Name],
    ISNULL(t_prop.value, 'No table description') AS [Table Description],
    c.name AS [Column Name],
    TYPE_NAME(c.user_type_id) AS [Data Type],
    ISNULL(c_prop.value, 'No column description') AS [Column Description]
FROM 
    sys.tables AS t
INNER JOIN 
    sys.columns AS c ON t.object_id = c.object_id
LEFT JOIN 
    sys.extended_properties AS t_prop ON t.object_id = t_prop.major_id
    AND t_prop.minor_id = 0 -- 0 indicates a table-level property
    AND t_prop.name = 'Caption'
LEFT JOIN 
    sys.extended_properties AS c_prop ON t.object_id = c_prop.major_id
    AND c.column_id = c_prop.minor_id
    AND c_prop.name = 'Caption'
WHERE 
    t.type = 'U' -- 'U' for user-defined tables
ORDER BY 
    [Table Name], 
    c.column_id;

Additional Information

When looking for which table contains informaiton about projects in a Harvest database, the HARENVIRONMENT table contains this information.