SQL – Select from information_schema.columns

You can query the information_schema.columns system view to get information about all columns in the database. Here’s an example:

USE MyDatabase

SELECT TABLE_NAME, COLUMN_NAME
FROM information_schema.columns
Code language: SQL (Structured Query Language) (sql)

Here’s the first few rows this returns:

TABLE_NAME	COLUMN_NAME
Movies	Id
Movies	Title
Movies	Year

In this article, I’ll show more examples of querying information_schema.columns.

Get columns filtered by name

Let’s say you want to find all columns that have the word ‘Name’ in the column name. Query information_schema.columns with a WHERE on COLUMN_NAME, like this:

USE MyDatabase

SELECT TABLE_NAME, COLUMN_NAME, DATA_TYPE
FROM information_schema.columns
WHERE COLUMN_NAME LIKE '%Name%'
Code language: SQL (Structured Query Language) (sql)

TABLE_NAME	COLUMN_NAME	DATA_TYPE
Actors	FirstName	nvarchar
Actors	LastName	nvarchar
Actors	FullName	nvarchar
Shows	Name	nvarchar
Movies	Name	nvarchar

Get all columns for a table

Query information_schema.columns to get all columns for the Movies table, sorting by the column name alphabetically:

USE MyDatabase

SELECT COLUMN_NAME, DATA_TYPE
      ,IS_NULLABLE, CHARACTER_MAXIMUM_LENGTH
      ,COLLATION_NAME
FROM information_schema.columns
WHERE TABLE_NAME = 'Movies'
ORDER BY COLUMN_NAME
Code language: SQL (Structured Query Language) (sql)

This returns the following results:

COLUMN_NAME	DATA_TYPE	IS_NULLABLE	CHARACTER_MAXIMUM_LENGTH	COLLATION_NAME
BoxOfficeRevenue	decimal	NO	NULL	NULL
Description	nvarchar	NO	500	SQL_Latin1_General_CP1_CI_AS
Director	nvarchar	NO	100	SQL_Latin1_General_CP1_CI_AS
Id	int	NO	NULL	NULL
Name	nvarchar	NO	500	SQL_Latin1_General_CP1_CI_AS
YearOfRelease	int	NO	NULL	NULL

Notice that there are a few fields that are only relevant to some data types. For example, CHARACTER_MAXIMUM_LENGTH applies to character types (such as nvarchar).

Get all tables that contain a specific column

Get all tables that have a column called ‘Id’ by querying information_schema.columns, like this:

USE MyDatabase

SELECT TABLE_NAME
FROM information_schema.columns
WHERE COLUMN_NAME = 'Id'
Code language: SQL (Structured Query Language) (sql)

This returns the following list of tables:

TABLE_NAME
Actors
Shows
Episodes
Movies
Code language: plaintext (plaintext)