Hello everyone!

Today, I’d like to share an Sql Server snippet which can helps you when you need to retrieve metadata from your Sql Server database. Sql Server provides many system views that expose metadata.

One of them is the Information Schema view. If you want to retrieve information about the catalog, a table, a column … this is the perfect way to do it!

Let’s try some examples!

Our sample database is the awesome Adventurework database.

First example, I want to select every table name in my database:

select
INFORMATION_SCHEMA.TABLES.TABLE_CATALOG,
INFORMATION_SCHEMA.TABLES.TABLE_SCHEMA,
INFORMATION_SCHEMA.TABLES.TABLE_NAME
from INFORMATION_SCHEMA.TABLES
order by INFORMATION_schema.TABLES.TABLE_NAME

Retrieve every table in your Sql Server database

Now I want to select all the columns in all the tables and some other information like the type of the column and if it is nullable.

select
INFORMATION_schema.COLUMNS.TABLE_NAME,
INFORMATION_SCHEMA.COLUMNS.COLUMN_NAME,
INFORMATION_schema.COLUMNS.DATA_TYPE,
INFORMATION_schema.COLUMNS.IS_NULLABLE
from INFORMATION_SCHEMA.COLUMNS
order by INFORMATION_schema.COLUMNS.TABLE_NAME

Retrieve columns from your Sql Server database

Okay, now, little harder and in a “real” pro-context!

Imagine you want to do like an O/R mapping and generate all the entities for your application … With the INFORMATION_SCHEMA, the database can do a lot of work for you.

In a previous developpment, I needed to retrieve some information about tables and especially columns constraint. Here is a snippet to retrieve these informations.

select
INFORMATION_SCHEMA.COLUMNS.TABLE_NAME
,INFORMATION_SCHEMA.COLUMNS.COLUMN_NAME
,INFORMATION_SCHEMA.COLUMNS.IS_NULLABLE
,INFORMATION_SCHEMA.COLUMNS.DATA_TYPE
,INFORMATION_SCHEMA.TABLE_CONSTRAINTS.CONSTRAINT_TYPE
from INFORMATION_SCHEMA.COLUMNS
left outer join INFORMATION_SCHEMA.KEY_COLUMN_USAGE
on INFORMATION_SCHEMA.KEY_COLUMN_USAGE.COLUMN_NAME = INFORMATION_SCHEMA.COLUMNS.COLUMN_NAME
and INFORMATION_SCHEMA.KEY_COLUMN_USAGE.TABLE_NAME = INFORMATION_SCHEMA.COLUMNS.TABLE_NAME
and INFORMATION_SCHEMA.KEY_COLUMN_USAGE.TABLE_CATALOG = INFORMATION_SCHEMA.COLUMNS.TABLE_CATALOG
and INFORMATION_SCHEMA.KEY_COLUMN_USAGE.ORDINAL_POSITION = INFORMATION_SCHEMA.COLUMNS.ORDINAL_POSITION
left outer join INFORMATION_SCHEMA.TABLE_CONSTRAINTS on
INFORMATION_SCHEMA.TABLE_CONSTRAINTS.TABLE_NAME = INFORMATION_SCHEMA.KEY_COLUMN_USAGE.TABLE_NAME
and INFORMATION_SCHEMA.TABLE_CONSTRAINTS.CONSTRAINT_NAME = INFORMATION_SCHEMA.KEY_COLUMN_USAGE.CONSTRAINT_NAME
order by INFORMATION_schema.COLUMNS.TABLE_NAME

Retrieve constraint type in columns