SQL Server – How to use INFORMATION_SCHEMA to find database metadata

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_NAMECOLUMN_NAMEDATA_TYPE
ActorsFirstNamenvarchar
ActorsLastNamenvarchar
ActorsFullnamenvarchar
ShowsNamenvarchar
MoviesNamenvarchar

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.

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:

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

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_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 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:

COLUMN_NAME Id
Code language: plaintext (plaintext)

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:

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

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:

VIEW_NAME vEmployee vEmployeeDepartment vEmployeeDepartmentHistory vSalesPerson vSalesPersonSalesByFiscalYears
Code language: plaintext (plaintext)

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 ‘\’.

Leave a Comment