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.