Concepts/Advanced/

Metadata Tables

An introduction to the metadata tables: Schemata, Tables, Views and Tablecolumns.

So far we have run queries to return data from a dataset. However queries can also be used to provide information about the underlying dataset structure. Each dataset contains the tables Schemata, Tables, and Tablecolumns that contain information about the the dataset, the tables, and the columns. These tables can be queried just like any other tables in the dataset.

The queries are the same for every dataset and are as follows:

SELECT *
  FROM Schemata
siyeh/crm-project Run query Copy code 
SELECT *
  FROM Tables
siyeh/crm-project Run query Copy code 
SELECT *
  FROM Views
siyeh/crm-project Run query Copy code 
SELECT *
  FROM Tablecolumns
siyeh/crm-project Run query Copy code

For the project we have been using the results of these queries are:

Schemata:

owner dataset
siyeh crm-project
siyeh central-office-orders
siyeh east-region-orders
siyeh sql-crm-example-data
siyeh west-office-orders

Tables:

tableId tableName tableTitle tableDescription owner dataset
siyeh.central-office-orders.central_office_orders central_office_orders central_office_orders siyeh central-office-orders
siyeh.east-region-orders.east_office_orders east_office_orders east_office_orders siyeh east-region-orders
siyeh.sql-crm-example-data.accounts accounts accounts siyeh sql-crm-example-data
siyeh.sql-crm-example-data.apr_2017_orders apr_2017_orders apr_2017_orders siyeh sql-crm-example-data
siyeh.sql-crm-example-data.aug_2017_orders aug_2017_orders aug_2017_orders siyeh sql-crm-example-data
siyeh.sql-crm-example-data.dec_2017_orders dec_2017_orders dec_2017_orders siyeh sql-crm-example-data
siyeh.sql-crm-example-data.employees employees employees siyeh sql-crm-example-data
siyeh.sql-crm-example-data.intl_accounts intl_accounts intl_accounts siyeh sql-crm-example-data
siyeh.sql-crm-example-data.jul_2017_orders jul_2017_orders jul_2017_orders siyeh sql-crm-example-data
siyeh.sql-crm-example-data.jun_2017_orders jun_2017_orders jun_2017_orders siyeh sql-crm-example-data
siyeh.sql-crm-example-data.mar_2017_orders mar_2017_orders mar_2017_orders siyeh sql-crm-example-data
siyeh.sql-crm-example-data.may_2017_orders may_2017_orders may_2017_orders siyeh sql-crm-example-data
siyeh.sql-crm-example-data.nov_2017_orders nov_2017_orders nov_2017_orders siyeh sql-crm-example-data
siyeh.sql-crm-example-data.oct_2017_orders oct_2017_orders oct_2017_orders siyeh sql-crm-example-data
siyeh.sql-crm-example-data.products products products siyeh sql-crm-example-data
siyeh.sql-crm-example-data.sales_pipeline sales_pipeline sales_pipeline siyeh sql-crm-example-data
siyeh.sql-crm-example-data.sales_teams sales_teams sales_teams siyeh sql-crm-example-data
siyeh.sql-crm-example-data.sep_2017_orders sep_2017_orders sep_2017_orders siyeh sql-crm-example-data
siyeh.west-office-orders.west_office_orders west_office_orders west_office_orders siyeh west-office-orders

Views:

viewId viewName viewTitle viewDescription viewLanguage viewQuery viewOwner viewDataset owner dataset
siyeh.sql_crm_example_data.q4_2017_orders q4_2017_orders q4_2017_orders SQL select * from dec_2017_orders UNION select * from nov_2017_orders UNION select * from oct_2017_orders siyeh sql-crm-example-data siyeh sql_crm_example_data

Tablecolumns:

tableId tableName columnIndex columnName columnTitle columnDescription columnDatatype columnNullable owner dataset
siyeh.central-office-orders.central_office_orders central_office_orders 1 sales_agent sales_agent “http://www.w3.org/2001/XMLSchema#string" false siyeh central-office-orders
siyeh.central-office-orders.central_office_orders central_office_orders 2 account account “http://www.w3.org/2001/XMLSchema#string" false siyeh central-office-orders
siyeh.central-office-orders.central_office_orders central_office_orders 3 opportunity_id opportunity_id “http://www.w3.org/2001/XMLSchema#string" false siyeh central-office-orders
siyeh.central-office-orders.central_office_orders central_office_orders 4 product product “http://www.w3.org/2001/XMLSchema#string" false siyeh central-office-orders
siyeh.central-office-orders.central_office_orders central_office_orders 5 create_date create_date “http://www.w3.org/2001/XMLSchema#date" false siyeh central-office-orders
siyeh.central-office-orders.central_office_orders central_office_orders 6 order_total order_total “http://www.w3.org/2001/XMLSchema#integer" false siyeh central-office-orders
siyeh.central-office-orders.central_office_orders central_office_orders 7 manager manager “http://www.w3.org/2001/XMLSchema#string" false siyeh central-office-orders
siyeh.east-region-orders.east_office_orders east_office_orders 1 sales_agent sales_agent “http://www.w3.org/2001/XMLSchema#string" false siyeh east-region-orders
siyeh.east-region-orders.east_office_orders east_office_orders 2 account account “http://www.w3.org/2001/XMLSchema#string" false siyeh east-region-orders
siyeh.east-region-orders.east_office_orders east_office_orders 3 opportunity_id opportunity_id “http://www.w3.org/2001/XMLSchema#string" false siyeh east-region-orders
siyeh.east-region-orders.east_office_orders east_office_orders 4 product product “http://www.w3.org/2001/XMLSchema#string" false siyeh east-region-orders
siyeh.east-region-orders.east_office_orders east_office_orders 5 create_date create_date “http://www.w3.org/2001/XMLSchema#date" false siyeh east-region-orders
siyeh.east-region-orders.east_office_orders east_office_orders 6 order_total order_total “http://www.w3.org/2001/XMLSchema#integer" false siyeh east-region-orders
siyeh.east-region-orders.east_office_orders east_office_orders 7 manager manager “http://www.w3.org/2001/XMLSchema#string" false siyeh east-region-orders
siyeh.sql-crm-example-data.accounts accounts 1 account Account foo “http://www.w3.org/2001/XMLSchema#string" false siyeh sql-crm-example-data
siyeh.sql-crm-example-data.accounts accounts 2 revenue Revenue bar “http://www.w3.org/2001/XMLSchema#decimal" false siyeh sql-crm-example-data
siyeh.sql-crm-example-data.accounts accounts 3 employees Employees baz “http://www.w3.org/2001/XMLSchema#integer" false siyeh sql-crm-example-data
siyeh.sql-crm-example-data.apr_2017_orders apr_2017_orders 1 sales_agent sales_agent “http://www.w3.org/2001/XMLSchema#string" false siyeh sql-crm-example-data
siyeh.sql-crm-example-data.apr_2017_orders apr_2017_orders 2 account account “http://www.w3.org/2001/XMLSchema#string" false siyeh sql-crm-example-data
siyeh.sql-crm-example-data.apr_2017_orders apr_2017_orders 3 product product “http://www.w3.org/2001/XMLSchema#string" false siyeh sql-crm-example-data
siyeh.sql-crm-example-data.apr_2017_orders apr_2017_orders 4 order_value order_value “http://www.w3.org/2001/XMLSchema#integer" false siyeh sql-crm-example-data
siyeh.sql-crm-example-data.apr_2017_orders apr_2017_orders 5 create_date create_date “http://www.w3.org/2001/XMLSchema#date" false siyeh sql-crm-example-data
siyeh.sql-crm-example-data.aug_2017_orders aug_2017_orders 1 sales_agent sales_agent “http://www.w3.org/2001/XMLSchema#string" false siyeh sql-crm-example-data
siyeh.sql-crm-example-data.aug_2017_orders aug_2017_orders 2 account account “http://www.w3.org/2001/XMLSchema#string" false siyeh sql-crm-example-data
siyeh.sql-crm-example-data.aug_2017_orders aug_2017_orders 3 product product “http://www.w3.org/2001/XMLSchema#string" false siyeh sql-crm-example-data
siyeh.sql-crm-example-data.aug_2017_orders aug_2017_orders 4 order_value order_value “http://www.w3.org/2001/XMLSchema#integer" false siyeh sql-crm-example-data
siyeh.sql-crm-example-data.aug_2017_orders aug_2017_orders 5 create_date create_date “http://www.w3.org/2001/XMLSchema#date" false siyeh sql-crm-example-data
siyeh.sql-crm-example-data.dec_2017_orders dec_2017_orders 1 sales_agent sales_agent “http://www.w3.org/2001/XMLSchema#string" false siyeh sql-crm-example-data
siyeh.sql-crm-example-data.dec_2017_orders dec_2017_orders 2 account account “http://www.w3.org/2001/XMLSchema#string" false siyeh sql-crm-example-data
siyeh.sql-crm-example-data.dec_2017_orders dec_2017_orders 3 product product “http://www.w3.org/2001/XMLSchema#string" false siyeh sql-crm-example-data
siyeh.sql-crm-example-data.dec_2017_orders dec_2017_orders 4 order_value order_value “http://www.w3.org/2001/XMLSchema#integer" false siyeh sql-crm-example-data
siyeh.sql-crm-example-data.dec_2017_orders dec_2017_orders 5 create_date create_date “http://www.w3.org/2001/XMLSchema#date" false siyeh sql-crm-example-data
siyeh.sql-crm-example-data.employees employees 1 emp_id emp_id “http://www.w3.org/2001/XMLSchema#integer" false siyeh sql-crm-example-data
siyeh.sql-crm-example-data.employees employees 2 name name “http://www.w3.org/2001/XMLSchema#string" false siyeh sql-crm-example-data
siyeh.sql-crm-example-data.employees employees 3 mgr_id mgr_id “http://www.w3.org/2001/XMLSchema#integer" false siyeh sql-crm-example-data
siyeh.sql-crm-example-data.employees employees 4 regional_office Regional_Office “http://www.w3.org/2001/XMLSchema#string" false siyeh sql-crm-example-data
siyeh.sql-crm-example-data.employees employees 5 status Status “http://www.w3.org/2001/XMLSchema#string" false siyeh sql-crm-example-data
siyeh.sql-crm-example-data.intl_accounts intl_accounts 1 account Account “http://www.w3.org/2001/XMLSchema#string" false siyeh sql-crm-example-data
siyeh.sql-crm-example-data.intl_accounts intl_accounts 2 revenue Revenue “http://www.w3.org/2001/XMLSchema#decimal" false siyeh sql-crm-example-data
siyeh.sql-crm-example-data.intl_accounts intl_accounts 3 employees Employees “http://www.w3.org/2001/XMLSchema#integer" false siyeh sql-crm-example-data
siyeh.sql-crm-example-data.intl_accounts intl_accounts 4 office_location Office Location “http://www.w3.org/2001/XMLSchema#string" false siyeh sql-crm-example-data
siyeh.sql-crm-example-data.jul_2017_orders jul_2017_orders 1 sales_agent sales_agent “http://www.w3.org/2001/XMLSchema#string" false siyeh sql-crm-example-data
siyeh.sql-crm-example-data.jul_2017_orders jul_2017_orders 2 account account “http://www.w3.org/2001/XMLSchema#string" false siyeh sql-crm-example-data
siyeh.sql-crm-example-data.jul_2017_orders jul_2017_orders 3 product product “http://www.w3.org/2001/XMLSchema#string" false siyeh sql-crm-example-data
siyeh.sql-crm-example-data.jul_2017_orders jul_2017_orders 4 order_value order_value “http://www.w3.org/2001/XMLSchema#integer" false siyeh sql-crm-example-data
siyeh.sql-crm-example-data.jul_2017_orders jul_2017_orders 5 create_date create_date “http://www.w3.org/2001/XMLSchema#date" false siyeh sql-crm-example-data
siyeh.sql-crm-example-data.jun_2017_orders jun_2017_orders 1 sales_agent sales_agent “http://www.w3.org/2001/XMLSchema#string" false siyeh sql-crm-example-data
siyeh.sql-crm-example-data.jun_2017_orders jun_2017_orders 2 account account “http://www.w3.org/2001/XMLSchema#string" false siyeh sql-crm-example-data
siyeh.sql-crm-example-data.jun_2017_orders jun_2017_orders 3 product product “http://www.w3.org/2001/XMLSchema#string" false siyeh sql-crm-example-data
siyeh.sql-crm-example-data.jun_2017_orders jun_2017_orders 4 order_value order_value “http://www.w3.org/2001/XMLSchema#integer" false siyeh sql-crm-example-data
siyeh.sql-crm-example-data.jun_2017_orders jun_2017_orders 5 create_date create_date “http://www.w3.org/2001/XMLSchema#date" false siyeh sql-crm-example-data
siyeh.sql-crm-example-data.mar_2017_orders mar_2017_orders 1 sales_agent sales_agent “http://www.w3.org/2001/XMLSchema#string" false siyeh sql-crm-example-data
siyeh.sql-crm-example-data.mar_2017_orders mar_2017_orders 2 account account “http://www.w3.org/2001/XMLSchema#string" false siyeh sql-crm-example-data
siyeh.sql-crm-example-data.mar_2017_orders mar_2017_orders 3 product product “http://www.w3.org/2001/XMLSchema#string" false siyeh sql-crm-example-data
siyeh.sql-crm-example-data.mar_2017_orders mar_2017_orders 4 order_value order_value “http://www.w3.org/2001/XMLSchema#integer" false siyeh sql-crm-example-data
siyeh.sql-crm-example-data.mar_2017_orders mar_2017_orders 5 create_date create_date “http://www.w3.org/2001/XMLSchema#date" false siyeh sql-crm-example-data
siyeh.sql-crm-example-data.may_2017_orders may_2017_orders 1 sales_agent sales_agent “http://www.w3.org/2001/XMLSchema#string" false siyeh sql-crm-example-data
siyeh.sql-crm-example-data.may_2017_orders may_2017_orders 2 account account “http://www.w3.org/2001/XMLSchema#string" false siyeh sql-crm-example-data
siyeh.sql-crm-example-data.may_2017_orders may_2017_orders 3 product product “http://www.w3.org/2001/XMLSchema#string" false siyeh sql-crm-example-data
siyeh.sql-crm-example-data.may_2017_orders may_2017_orders 4 order_value order_value “http://www.w3.org/2001/XMLSchema#integer" false siyeh sql-crm-example-data
siyeh.sql-crm-example-data.may_2017_orders may_2017_orders 5 create_date create_date “http://www.w3.org/2001/XMLSchema#date" false siyeh sql-crm-example-data
siyeh.sql-crm-example-data.nov_2017_orders nov_2017_orders 1 sales_agent sales_agent “http://www.w3.org/2001/XMLSchema#string" false siyeh sql-crm-example-data
siyeh.sql-crm-example-data.nov_2017_orders nov_2017_orders 2 account account “http://www.w3.org/2001/XMLSchema#string" false siyeh sql-crm-example-data
siyeh.sql-crm-example-data.nov_2017_orders nov_2017_orders 3 product product “http://www.w3.org/2001/XMLSchema#string" false siyeh sql-crm-example-data
siyeh.sql-crm-example-data.nov_2017_orders nov_2017_orders 4 order_value order_value “http://www.w3.org/2001/XMLSchema#integer" false siyeh sql-crm-example-data
siyeh.sql-crm-example-data.nov_2017_orders nov_2017_orders 5 create_date create_date “http://www.w3.org/2001/XMLSchema#date" false siyeh sql-crm-example-data
siyeh.sql-crm-example-data.oct_2017_orders oct_2017_orders 1 sales_agent sales_agent “http://www.w3.org/2001/XMLSchema#string" false siyeh sql-crm-example-data
siyeh.sql-crm-example-data.oct_2017_orders oct_2017_orders 2 account account “http://www.w3.org/2001/XMLSchema#string" false siyeh sql-crm-example-data
siyeh.sql-crm-example-data.oct_2017_orders oct_2017_orders 3 product product “http://www.w3.org/2001/XMLSchema#string" false siyeh sql-crm-example-data
siyeh.sql-crm-example-data.oct_2017_orders oct_2017_orders 4 order_value order_value “http://www.w3.org/2001/XMLSchema#integer" false siyeh sql-crm-example-data
siyeh.sql-crm-example-data.oct_2017_orders oct_2017_orders 5 create_date create_date “http://www.w3.org/2001/XMLSchema#date" false siyeh sql-crm-example-data
siyeh.sql-crm-example-data.products products 1 product Product “http://www.w3.org/2001/XMLSchema#string" false siyeh sql-crm-example-data
siyeh.sql-crm-example-data.products products 2 sales_price Sales_Price “http://www.w3.org/2001/XMLSchema#integer" false siyeh sql-crm-example-data
siyeh.sql-crm-example-data.sales_pipeline sales_pipeline 1 account Account “http://www.w3.org/2001/XMLSchema#string" true siyeh sql-crm-example-data
siyeh.sql-crm-example-data.sales_pipeline sales_pipeline 2 opportunity_id Opportunity_ID “http://www.w3.org/2001/XMLSchema#string" false siyeh sql-crm-example-data
siyeh.sql-crm-example-data.sales_pipeline sales_pipeline 3 sales_agent Sales_Agent “http://www.w3.org/2001/XMLSchema#string" false siyeh sql-crm-example-data
siyeh.sql-crm-example-data.sales_pipeline sales_pipeline 4 deal_stage Deal_Stage “http://www.w3.org/2001/XMLSchema#string" false siyeh sql-crm-example-data
siyeh.sql-crm-example-data.sales_pipeline sales_pipeline 5 product Product “http://www.w3.org/2001/XMLSchema#string" false siyeh sql-crm-example-data
siyeh.sql-crm-example-data.sales_pipeline sales_pipeline 6 created_date Created_Date “http://www.w3.org/2001/XMLSchema#date" true siyeh sql-crm-example-data
siyeh.sql-crm-example-data.sales_pipeline sales_pipeline 7 close_date Close_Date “http://www.w3.org/2001/XMLSchema#date" true siyeh sql-crm-example-data
siyeh.sql-crm-example-data.sales_pipeline sales_pipeline 8 close_value Close_Value “http://www.w3.org/2001/XMLSchema#integer" true siyeh sql-crm-example-data
siyeh.sql-crm-example-data.sales_teams sales_teams 1 sales_agent Sales_Agent “http://www.w3.org/2001/XMLSchema#string" false siyeh sql-crm-example-data
siyeh.sql-crm-example-data.sales_teams sales_teams 2 manager Manager “http://www.w3.org/2001/XMLSchema#string" false siyeh sql-crm-example-data
siyeh.sql-crm-example-data.sales_teams sales_teams 3 regional_office Regional_Office “http://www.w3.org/2001/XMLSchema#string" false siyeh sql-crm-example-data
siyeh.sql-crm-example-data.sales_teams sales_teams 4 status Status “http://www.w3.org/2001/XMLSchema#string" false siyeh sql-crm-example-data
siyeh.sql-crm-example-data.sep_2017_orders sep_2017_orders 1 sales_agent sales_agent “http://www.w3.org/2001/XMLSchema#string" false siyeh sql-crm-example-data
siyeh.sql-crm-example-data.sep_2017_orders sep_2017_orders 2 account account “http://www.w3.org/2001/XMLSchema#string" false siyeh sql-crm-example-data
siyeh.sql-crm-example-data.sep_2017_orders sep_2017_orders 3 product product “http://www.w3.org/2001/XMLSchema#string" false siyeh sql-crm-example-data
siyeh.sql-crm-example-data.sep_2017_orders sep_2017_orders 4 order_value order_value “http://www.w3.org/2001/XMLSchema#integer" false siyeh sql-crm-example-data
siyeh.sql-crm-example-data.sep_2017_orders sep_2017_orders 5 create_date create_date “http://www.w3.org/2001/XMLSchema#date" false siyeh sql-crm-example-data
siyeh.west-office-orders.west_office_orders west_office_orders 1 sales_agent sales_agent “http://www.w3.org/2001/XMLSchema#string" false siyeh west-office-orders
siyeh.west-office-orders.west_office_orders west_office_orders 2 account account “http://www.w3.org/2001/XMLSchema#string" false siyeh west-office-orders
siyeh.west-office-orders.west_office_orders west_office_orders 3 opportunity_id opportunity_id “http://www.w3.org/2001/XMLSchema#string" false siyeh west-office-orders
siyeh.west-office-orders.west_office_orders west_office_orders 4 product product “http://www.w3.org/2001/XMLSchema#string" false siyeh west-office-orders
siyeh.west-office-orders.west_office_orders west_office_orders 5 create_date create_date “http://www.w3.org/2001/XMLSchema#date" false siyeh west-office-orders
siyeh.west-office-orders.west_office_orders west_office_orders 6 order_total order_total “http://www.w3.org/2001/XMLSchema#integer" false siyeh west-office-orders
siyeh.west-office-orders.west_office_orders west_office_orders 7 manager manager “http://www.w3.org/2001/XMLSchema#string" false siyeh west-office-orders

The column data for each table can be particularly useful for figuring out how the tables relate to each other, what information they share, and how they might be joined to provide additional data.