Information Schema

On this page

dotConnect for Google BigQuery provides the information schema tables that you can query with SQL to retrieve metadata. They describe the tables, views, columns, and primary keys of the dataset specified in the DataSet Id connection string parameter, or of all datasets of the project if DataSet Id is not specified.

The information schema contains the following tables:

SYS_TABLES - lists available tables and views and their parameters.
SYS_COLUMNS - lists table columns, determines to which tables they belong, their position in tables, data type, and other parameters.
SYS_TABLE_CONSTRAINTS - lists constraints and specifies their types and tables they belong to.
SYS_REFERENTIAL_CONSTRAINTS - contains details on foreign keys. dotConnect for Google BigQuery does not return foreign keys, so this table is always empty.
SYS_KEY_COLUMN_USAGE - contains information to match columns with constraints.

The information schema tables are provided by dotConnect for Google BigQuery; they are not the INFORMATION_SCHEMA views of Google BigQuery. You can query them the same way you query Google BigQuery tables. However, you should not use both information schema tables and Google BigQuery tables in one query. If you query both in a single SELECT statement, you get an exception.

The information schema is built from the metadata that dotConnect for Google BigQuery retrieves when it is needed for the first time. To get fresh metadata after tables or columns have changed, call the RefreshMetadata method of BigQueryConnection.

Here are examples of such queries:

The following query returns the list of tables and their columns, ordered in the same way they are ordered in their tables:
SELECT TABLE_SCHEMA, TABLE_NAME, COLUMN_NAME FROM SYS_COLUMNS ORDER BY TABLE_SCHEMA, TABLE_NAME, ORDINAL_POSITION


SELECT TABLE_SCHEMA, TABLE_NAME, COLUMN_NAME FROM SYS_COLUMNS ORDER BY TABLE_SCHEMA, TABLE_NAME, ORDINAL_POSITION

The following query returns the tables with their primary keys and the primary key columns:


SELECT C.TABLE_SCHEMA, C.TABLE_NAME, C.CONSTRAINT_NAME, U.COLUMN_NAME, U.ORDINAL_POSITION FROM SYS_TABLE_CONSTRAINTS AS C, SYS_KEY_COLUMN_USAGE AS U WHERE C.CONSTRAINT_NAME = U.CONSTRAINT_NAME AND C.CONSTRAINT_TYPE = 'PRIMARY KEY' ORDER BY C.TABLE_SCHEMA, C.TABLE_NAME, U.ORDINAL_POSITION

SELECT C.TABLE_SCHEMA, C.TABLE_NAME, C.CONSTRAINT_NAME, U.COLUMN_NAME, U.ORDINAL_POSITION
  FROM SYS_TABLE_CONSTRAINTS AS C, SYS_KEY_COLUMN_USAGE AS U
  WHERE C.CONSTRAINT_NAME = U.CONSTRAINT_NAME AND C.CONSTRAINT_TYPE = 'PRIMARY KEY'
  ORDER BY C.TABLE_SCHEMA, C.TABLE_NAME, U.ORDINAL_POSITION

The following query returns the columns of a table, specified in the @table parameter:


SELECT COLUMN_NAME, DATA_TYPE, IS_NULLABLE FROM SYS_COLUMNS WHERE TABLE_NAME = @table ORDER BY ORDINAL_POSITION

See Also