SQL Server INFORMATION_SCHEMA views Tutorial

Overview

The INFORMATION_SCHEMA views allow you to retrieve metadata about the objects within a database.  These views can be found in the master database under Views / System Views and can be called from any database in your SQL Server instance.  The reason these were developed was so that they would be standard across all database platforms.  In SQL 2005 and later, these Information Schema views comply with the ISO standard.

Following is a list of each of the views that exist:

• INFORMATION_SCHEMA.CHECK_CONSTRAINTS
• INFORMATION_SCHEMA.COLUMN_DOMAIN_USAGE
• INFORMATION_SCHEMA.COLUMN_PRIVILEGES
• INFORMATION_SCHEMA.COLUMNS
• INFORMATION_SCHEMA.CONSTRAINT_COLUMN_USAGE
• INFORMATION_SCHEMA.CONSTRAINT_TABLE_USAGE
• INFORMATION_SCHEMA.DOMAIN_CONSTRAINTS
• INFORMATION_SCHEMA.DOMAINS
• INFORMATION_SCHEMA.KEY_COLUMN_USAGE
• INFORMATION_SCHEMA.PARAMETERS
• INFORMATION_SCHEMA.REFERENTIAL_CONSTRAINTS
• INFORMATION_SCHEMA.ROUTINE_COLUMNS
• INFORMATION_SCHEMA.ROUTINES
• INFORMATION_SCHEMA.SCHEMATA
• INFORMATION_SCHEMA.TABLE_CONSTRAINTS
• INFORMATION_SCHEMA.TABLE_PRIVILEGES
• INFORMATION_SCHEMA.TABLES
• INFORMATION_SCHEMA.VIEW_COLUMN_USAGE
• INFORMATION_SCHEMA.VIEW_TABLE_USAGE
• INFORMATION_SCHEMA.VIEWS

These views can be used from any of your databases.  So, if you want to gather data about Tables from the AdventureWorks database, you issue the following in that database:

USE AdventureWorks
GO
SELECT * FROM INFORMATION_SCHEMA.TABLES

Greg Robidoux

Greg Robidoux has been working with databases for 35+ years with extensive hands on SQL Server experience from version 6.5 to 2025. He has authored over 250 technical articles and delivered several presentations online and at various conventions. Greg is also the President and founder of Edgewood Solutions, a technology services company delivering services and solutions for Microsoft SQL Server.