Information Schema

On this page

dotConnect for MongoDB provides metadata tables, compliant with the SQL-92 information schema standard, that describe the collections and views of the database of the connection. Collections are mapped to tables and views to views. Fields, including the fields of embedded documents and the elements of arrays, are mapped to columns. The _id field is mapped to the primary key, and the indexes of MongoDB are listed with their fields. MongoDB has no foreign keys. The following metadata tables are available:

SYS_TABLES - lists the tables (collections and views) and their schema validation settings.
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 - is meant for foreign keys and is always empty.
SYS_KEY_COLUMN_USAGE - contains information to match columns with constraints.
SYS_INDEXES - lists the indexes of the tables and their options.
SYS_INDEX_COLUMNS - lists the fields of each index in key order.

The metadata tables are available in SQL mode (Sql Engine=true, the default). They describe the database specified in the Database connection string parameter: TABLE_CATALOG holds its name, and TABLE_SCHEMA is always NULL. Collections of other databases of the server and system collections (system.*) are not listed; use the GetSchema method to get the metadata of another database. The metadata tables are read-only.

The metadata is read from the server each time a query uses these tables, so a collection created after the connection was opened is listed at once. SYS_COLUMNS lists the columns as the queries of the connection see them.

You may query these tables in the same way you query collections, with joins, conditions and parameters, and you may use metadata tables and collections in one query.

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_NAME, COLUMN_NAME FROM SYS_COLUMNS ORDER BY TABLE_NAME, ORDINAL_POSITION

The following query displays the tables with their primary key constraints and the key columns:


SELECT C.TABLE_NAME, C.CONSTRAINT_NAME, U.COLUMN_NAME FROM SYS_TABLE_CONSTRAINTS AS C, SYS_KEY_COLUMN_USAGE AS U WHERE C.CONSTRAINT_NAME = U.CONSTRAINT_NAME ORDER BY C.TABLE_NAME

The following query displays the indexes of the customers table with their fields; a compound index has a row for each field:


SELECT I.INDEX_NAME, I.IS_UNIQUE, C.COLUMN_NAME, C.IS_DESCENDING FROM SYS_INDEXES AS I JOIN SYS_INDEX_COLUMNS AS C ON C.TABLE_NAME = I.TABLE_NAME AND C.INDEX_NAME = I.INDEX_NAME WHERE I.TABLE_NAME = 'customers' ORDER BY I.INDEX_NAME, C.ORDINAL_POSITION

SELECT I.INDEX_NAME, I.IS_UNIQUE, C.COLUMN_NAME, C.IS_DESCENDING
  FROM SYS_INDEXES AS I
  JOIN SYS_INDEX_COLUMNS AS C ON C.TABLE_NAME = I.TABLE_NAME AND C.INDEX_NAME = I.INDEX_NAME
  WHERE I.TABLE_NAME = 'customers'
  ORDER BY I.INDEX_NAME, C.ORDINAL_POSITION

For a collection with a unique index on name, a unique index on email with a filter, a sparse index on tags and a compound index on address.city ascending and name descending, the query returns:


INDEX_NAME IS_UNIQUE COLUMN_NAME IS_DESCENDING _id_ True _id False ix_city_name False address.city False ix_city_name False name True ix_email True email False ix_name True name False ix_tags False tags False

INDEX_NAME    IS_UNIQUE  COLUMN_NAME   IS_DESCENDING
_id_          True       _id           False
ix_city_name  False      address.city  False
ix_city_name  False      name          True
ix_email      True       email         False
ix_name       True       name          False
ix_tags       False      tags          False

The following query displays the tables that have a schema validator and its settings:


SELECT TABLE_NAME, VALIDATION_LEVEL, VALIDATION_ACTION FROM SYS_TABLES WHERE VALIDATOR IS NOT NULL

The following code reads the columns of the table, specified in the table parameter, and their types:


using (var connection = new MongoDBConnection("Host=localhost;Database=sample;User Id=user;Password=pwd;License Key=***")) { connection.Open(); var command = new MongoDBCommand("SELECT COLUMN_NAME, DATA_TYPE FROM SYS_COLUMNS WHERE TABLE_NAME = :table ORDER BY ORDINAL_POSITION", connection); command.Parameters.Add("table", "customers"); using (var reader = command.ExecuteReader()) while (reader.Read()) Console.WriteLine("{0}\t{1}", reader.GetString(0), reader.GetString(1)); }

using (var connection = new MongoDBConnection("Host=localhost;Database=sample;User Id=user;Password=pwd;License Key=***")) {
  connection.Open();
  var command = new MongoDBCommand("SELECT COLUMN_NAME, DATA_TYPE FROM SYS_COLUMNS WHERE TABLE_NAME = :table ORDER BY ORDINAL_POSITION", connection);
  command.Parameters.Add("table", "customers");
  using (var reader = command.ExecuteReader())
    while (reader.Read())
      Console.WriteLine("{0}\t{1}", reader.GetString(0), reader.GetString(1));
}

For documents of the { name, email, address: { city, street }, tags: ["vip"] } structure, the example outputs the following:


_id OID name VARCHAR email VARCHAR address.city VARCHAR address.street VARCHAR tags.0 VARCHAR

See Also