Let’s say you want to find all columns in a database that have the word ‘Name’ in the column name. You can query INFORMATION_SCHEMA.COLUMNS, like this:
USE StreamingService
SELECT TABLE_NAME, COLUMN_NAME, DATA_TYPE
FROM [INFORMATION_SCHEMA].[COLUMNS]
WHERE COLUMN_NAME LIKE '%Name%'
Code language: SQL (Structured Query Language) (sql)This query returns the following five rows showing the table name, column name, and column data type:
| TABLE_NAME | COLUMN_NAME | DATA_TYPE |
| Actors | FirstName | nvarchar |
| Actors | LastName | nvarchar |
| Actors | Fullname | nvarchar |
| Shows | Name | nvarchar |
| Movies | Name | nvarchar |
You can use the INFORMATION_SCHEMA system views for finding important metadata in the database. The most commonly used system views are COLUMNS, TABLES, VIEWS, and ROUTINES. In this article, I’ll show examples of querying the INFORMATION_SCHEMA system views for metadata.
Table of Contents
What are all the INFORMATION_SCHEMA system views?
To check what all the system views are available to you, you can execute the following query:
USE StreamingService
SELECT [name]
FROM [sys].[system_views]
WHERE schema_id = 3
ORDER BY [name]
Code language: SQL (Structured Query Language) (sql)Note: You can also find this information in SSMS (Databases > your database > Views > System Views).
This returns the following 21 system views:
Code language: plaintext (plaintext)CHECK_CONSTRAINTS COLUMN_DOMAIN_USAGE COLUMN_PRIVILEGES COLUMNS CONSTRAINT_COLUMN_USAGE CONSTRAINT_TABLE_USAGE DOMAIN_CONSTRAINTS DOMAINS KEY_COLUMN_USAGE PARAMETERS REFERENTIAL_CONSTRAINTS ROUTINE_COLUMNS ROUTINES SCHEMATA SEQUENCES TABLE_CONSTRAINTS TABLE_PRIVILEGES TABLES VIEW_COLUMN_USAGE VIEW_TABLE_USAGE VIEWS
Some of the system views contain raw definitions – like VIEWS and ROUTINES. Other system views have the raw definitions from those other system views parsed out into individual rows. For example, VIEW_TABLE_USAGE shows which tables are referenced by a view.
The best way to learn about what’s available in each system view is by querying them and looking at all of their columns and data.
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 StreamingService
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 string fields.
Get all columns that are part of the primary key for a table
To get all of the columns that are part of the primary key for a specific table, join INFORMATION_SCHEMA.TABLE_CONSTRAINTS and INFORMATION_SCHEMA.CONSTRAINT_COLUMN_USAGE, like this:
USE StreamingService
SELECT ccu.COLUMN_NAME
FROM [INFORMATION_SCHEMA].[TABLE_CONSTRAINTS] tc
INNER JOIN [INFORMATION_SCHEMA].[CONSTRAINT_COLUMN_USAGE] ccu
ON tc.CONSTRAINT_NAME = ccu.CONSTRAINT_NAME
WHERE tc.TABLE_NAME = 'Movies' AND tc.CONSTRAINT_TYPE = 'PRIMARY KEY'
Code language: SQL (Structured Query Language) (sql)This returns the following results:
Code language: plaintext (plaintext)COLUMN_NAME Id
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 [StreamingService]
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:
Code language: plaintext (plaintext)TABLE_NAME Actors Shows Episodes Movies
Get all views referencing a specific table
Query INFORMATION_SCHEMA.VIEW_TABLE_USAGE to get all views that reference the ‘Employee’ table, like this:
USE [AdventureWorks2016]
SELECT VIEW_NAME
FROM [INFORMATION_SCHEMA].[VIEW_TABLE_USAGE]
WHERE TABLE_NAME = 'Employee'
Code language: SQL (Structured Query Language) (sql)This returns the following views:
Code language: plaintext (plaintext)VIEW_NAME vEmployee vEmployeeDepartment vEmployeeDepartmentHistory vSalesPerson vSalesPersonSalesByFiscalYears
Get all views that have an INNER JOIN
Query INFORMATION_SCHEMA.VIEWS and look in the VIEW_DEFINITION field for ‘INNER JOIN’, like this:
USE [AdventureWorks2016]
SELECT TABLE_NAME, VIEW_DEFINITION
FROM [INFORMATION_SCHEMA].[VIEWS]
WHERE VIEW_DEFINITION LIKE '%INNER JOIN%'
Code language: SQL (Structured Query Language) (sql)This returns a list of views and their raw definitions (omitted here for brevity).
Looking for things in the raw definition can be useful, but I wouldn’t recommend this approach if you’re looking for references to tables/columns, since it can return false positives. Use VIEW_TABLE_USAGE and VIEW_COLUMN_USAGE instead if you’re looking for specific table/column references.
Get all stored procs that reference a specific table
To look for stored procs that reference a specific table, query INFORMATION_SCHEMA.ROUTINES, like this:
USE [AdventureWorks2016]
SELECT ROUTINE_NAME, ROUTINE_DEFINITION
FROM [INFORMATION_SCHEMA].[ROUTINES]
WHERE ROUTINE_TYPE = 'PROCEDURE'
AND ROUTINE_DEFINITION LIKE '%\[HumanResources\].\[Employee\]%' ESCAPE '\'
Code language: SQL (Structured Query Language) (sql)This returns all stored procs that have ‘[HumanResources].[Employee]’ in the definition.
First, ROUTINE_TYPE = ‘PROCEDURE’ means you only want stored procs and not functions.
Second, this is looking in the stored proc’s raw definition. The stored procs in this database (AdventureWorks2016) conform to the convention of using square brackets, so it’s simple to find all references to the Employee table. In the real world, it can be tricky to find exactly what you’re looking for when you’re querying the raw definition.
Note: When you use LIKE, square brackets have special meaning, so you have to escape them. This is why the query above is escaping the square brackets with a \ character and then using ESCAPE ‘\’.
