Metadata Tables

An introduction to the metadata tables: Schemata, Tables, 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
SELECT *
  FROM Tables
SELECT *
  FROM Tablecolumns

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

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.