Version 2021

Read Primary-Foreign Key

MicroStrategy supports reading primary and foreign keys by using the Read the table Primary and Foreign tables setting in MicroStrategy Developer. Currently the setting only works for MicroStrategy Developer and not MicroStrategy Workstation or Web.

  1. In MicroStrategy Developer, go to Schema > Warehouse Catalog.

     

  2. Click Options.

     

  3. Go to Catalog > Read Settings.
  4. Select the Read the table Primary and Foreign keys checkbox.

     

The following query is for the primary key: 

Copy 
SELECT constr.table_schema AS NAME_SPACE, constr.table_name AS TAB_NAME, constr.constraint_name AS CONSTR_NAME, k.column_name AS COL_NAME FROM information_schema.table_constraints constr JOIN information_schema.key_column_usage k ON constr.table_name = k.table_name AND constr.table_schema = k.table_schema AND constr.table_catalog = k.table_catalog AND constr.constraint_name = k.constraint_name WHERE constr.table_name IN (#TABLE_LIST#) AND constr.constraint_type = 'PRIMARY KEY' ORDER BY 1,2,3" 

The following query is for the foreign key: 

Copy 
SELECT fk.table_schema AS NAME_SPACE, fk.table_name AS TAB_NAME, fk.constraint_name AS CONSTR_NAME, fk.column_name AS COL_NAME,  pk.table_schema AS REF_NAME_SPACE, pk.table_name AS REF_TAB_NAME, pk.column_name AS REF_COL_NAME FROM information_schema.table_constraints tab JOIN information_schema.referential_constraints ref ON ref.constraint_name = tab.constraint_name AND ref.constraint_schema = tab.constraint_schema AND ref.constraint_catalog = tab.constraint_catalog JOIN information_schema.key_column_usage fk ON fk.constraint_name = ref.constraint_name AND fk.constraint_schema = ref.constraint_schema AND fk.constraint_catalog = ref.constraint_catalog JOIN information_schema.key_column_usage pk ON pk.constraint_name = ref.unique_constraint_name AND pk.constraint_schema = ref.unique_constraint_schema AND pk.constraint_catalog = ref.unique_constraint_catalog WHERE tab.table_name IN (#TABLE_LIST#) AND tab.constraint_type = 'FOREIGN KEY' AND fk.ordinal_position = pk.ordinal_position ORDER BY 1,2,3,4,5,6