Genesys Hacking - CCA Finding Table Name from Column Name

Product: Genesys
Component: CCA DataMart

For any consultant who is not familiar with database, it is time consuming document and find CCA report. Especially if the report is designed by someone else, reverse engineering, and analysis is often require

This post will contains tips to check table and column definition quickly from MS SQL Server and Oracle

#1 Find table definition by table name S_AGENT_DAY

Oracle

select owner, table_name, column_name, data_type, data_length from all_tab_columns where table_name = 'S_AGENT_DAY'

Note: Or replace all_tab_columns with dba_tab_columns

MS SQL Server

select TableName, COLUMN_NAME, DATA_TYPE, CHARACTER_MAXIMUM_LENGTH  from
INFORMATION_SCHEMA.COLUMNS where table_name = 'S_AGENT_DAY'

#2 Find table definition by column name T_ANSWER
This SQL can be used to find out table name using the specific column name find in Hyperion

Oracle

select owner, table_name, column_name, data_type, data_length from all_tab_columns where column_name = 'T_ANSWER'

Note: Or replace all_tab_columns with dba_tab_columns

MS SQL Server

select TableName as TableName, COLUMN_NAME, DATA_TYPE, CHARACTER_MAXIMUM_LENGTH  from INFORMATION_SCHEMA.COLUMNS where column_name = 'T_ANSWER'