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;

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;