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.
Table of Contents
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_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)