INFORMATION_SCHEMA - WitDatabase

A database describes itself through INFORMATION_SCHEMA, which is queryable like any other table. This is how scaffolding builds a model, how tooling lists what is in a file, and how Studio fills its object inspector.

sql

SELECT TABLE_NAME FROM INFORMATION_SCHEMA.TABLES;

The views

View	Holds
`TABLES`	Tables and views
`COLUMNS`	Every column, with type, nullability, default and position
`INDEXES`	Indexes, with uniqueness and the filter of a partial one
`KEY_COLUMN_USAGE`	Which columns take part in which key
`TABLE_CONSTRAINTS`	Named constraints: primary keys, unique, foreign keys, checks
`REFERENTIAL_CONSTRAINTS`	Foreign keys, with their update and delete actions
`VIEWS`	Views, with their definitions
`TRIGGERS`	Triggers, with their timing, event and body
`TRIGGERED_UPDATE_COLUMNS`	Which columns an `UPDATE OF` trigger watches. No rows for a trigger that watches every column, which is where ISO 9075-11 and PostgreSQL put it
`SEQUENCES`	Sequences
`ROUTINES`	Functions and procedures
`PARAMETERS`	Their parameters

ROUTINE_TYPE is FUNCTION or PROCEDURE and DATA_TYPE is the return type, null for a procedure. PARAMETER_MODE is always IN, since there are no OUT parameters.

Some queries worth keeping

What is in this database:

sql

SELECT TABLE_NAME, TABLE_TYPE FROM INFORMATION_SCHEMA.TABLES ORDER BY TABLE_NAME;

What a table looks like:

sql

SELECT COLUMN_NAME, DATA_TYPE, IS_NULLABLE, COLUMN_DEFAULT
FROM INFORMATION_SCHEMA.COLUMNS
WHERE TABLE_NAME = 'Orders'
ORDER BY ORDINAL_POSITION;

What points at a table, which is the question to ask before dropping it:

sql

SELECT * FROM INFORMATION_SCHEMA.REFERENTIAL_CONSTRAINTS
WHERE UNIQUE_CONSTRAINT_NAME IN (
    SELECT CONSTRAINT_NAME FROM INFORMATION_SCHEMA.TABLE_CONSTRAINTS
    WHERE TABLE_NAME = 'Customers'
);

Which indexes exist and on what:

sql

SELECT TABLE_NAME, INDEX_NAME, IS_UNIQUE FROM INFORMATION_SCHEMA.INDEXES
ORDER BY TABLE_NAME, INDEX_NAME;

Implicit indexes are hidden

An index created automatically for a primary key does not appear. What you see are the indexes somebody declared, which is what you want when auditing a schema.

A definition is withheld rather than approximated

VIEW_DEFINITION, CHECK_EXPRESSION, COLUMN_DEFAULT, FILTER_CONDITION and ACTION_STATEMENT are null in the rare case where the engine cannot render the stored definition faithfully.

That is deliberate. A view over A UNION B reported as SELECT Id FROM A is a false statement about a database, and somebody would copy it out and act on it. A null says "ask the catalogue" rather than lying.

Schema has been stored as a parsed tree since 9.0.0 and rendered on demand, so this case is uncommon and gets rarer.

From code

The same information is available without SQL. WitSqlEngine answers GetTable, GetIndex, GetTableIndexes and GetTableRowCount in the engine's own types; see the SQL engine. ADO.NET's GetSchema answers the standard collections; see the ADO.NET provider.

Use INFORMATION_SCHEMA when you want the answer in SQL, and the typed calls when you are writing a tool.

Where to go next

DDL, what these views describe
Routines, what ROUTINES and PARAMETERS hold
SQL engine, the same questions in code