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_NAMECOLUMN_NAME
MoviesId
MoviesTitle
MoviesYear

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)

This outputs the following results:

TABLE_NAMECOLUMN_NAMEDATA_TYPE
ActorsFirstNamenvarchar
ActorsLastNamenvarchar
ActorsFullNamenvarchar
ShowsNamenvarchar
MoviesNamenvarchar

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_NAMEDATA_TYPEIS_NULLABLECHARACTER_MAXIMUM_LENGTHCOLLATION_NAME
BoxOfficeRevenuedecimalNONULLNULL
DescriptionnvarcharNO500SQL_Latin1_General_CP1_CI_AS
DirectornvarcharNO100SQL_Latin1_General_CP1_CI_AS
IdintNONULLNULL
NamenvarcharNO500SQL_Latin1_General_CP1_CI_AS
YearOfReleaseintNONULLNULL

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
MoviesCode language: plaintext (plaintext)

Leave a Comment