Extender Table

An Extender Table is a sibling table to a baseline table.

Nearly every baseline table has an extender table.

For every row in the baseline table, there will be one related row in the extender table. The PK of the extender table is also a FK to the PK of the baseline table.

Extender Columns

An Extender column is a Column in an Extender Table in the database. Extender columns are added to client netFORUM tables and hold data unique to a particular client.

An extender column will always end with _ext, for example, co_individual.ind_home_town_ext.

Queries

Here are some queries to show you all extender columns.

Count of Extender Columns in Extender Tables

This query shows you all the extender tables that have extender columns, and how many extender columns are in the table:

SELECT DISTINCT 
[Baseline TABLE] = mdc_mdt_name , 
[COLUMN Count] = count(*)
FROM md_column
JOIN md_table ON mdt_name = mdc_mdt_name
WHERE  mdc_ext = 1
AND mdc_name != mdt_key_column
AND mdc_name != mdt_prefix +'_key_ext'
AND mdc_table_name NOT IN 
('am_survey_x_accreditation', 'am_surveyor', 'xx_template_ext')
GROUP BY mdc_mdt_name
ORDER BY mdc_mdt_name

All Extender Columns

This shows a detailed list of ALL extender columns:

SELECT DISTINCT 
[Baseline TABLE] = mdc_mdt_name ,
[Extender COLUMN] = mdc_name
FROM md_column
JOIN md_table ON mdt_name = mdc_mdt_name
WHERE  mdc_ext = 1
AND mdc_name != mdt_key_column
AND mdc_name != mdt_prefix +'_key_ext'
AND mdc_table_name NOT IN 
('am_survey_x_accreditation', 'am_surveyor', 'xx_template_ext')
ORDER BY mdc_mdt_name, 2