Business Intelligence Blogs

View blogs by industry experts on topics such as SSAS, SSIS, SSRS, Power BI, Performance Tuning, Azure, Big Data and much more! You can also sign up to post your own business intelligence blog.

What are the Information Schema Views?

  • 2 July 2013
  • Author: Kathi Kellenberger
  • Number of views: 5907
  • 0 Comments

There is no shortage of system views and DMVs to give us information about SQL Server databases. One of my favorites is the series of Information Schema views. The Information Schema views are easy to use and cover the metadata of the tables and other objects in the database.

Each Information Schema view covers one type of object or property such as foreign key constraints and permissions within a database. The view I use most often covers the columns, INFORMATION_SCHEMA.COLUMNS. Here is a list of the views:

View

Purpose

CHECK_CONSTRAINTS

The check constraints

COLUMN_DOMAIN_USAGE

The columns using a user defined data type

COLUMN_PRIVILEGES

The columns with specific permissions defined

COLUMNS

The columns, including information about data types

CONSTRAINT_COLUMN_USAGE

The constraints along with the columns affected

CONSTRAINT_TABLE_USAGE

The constraints along with the tables affected

DOMAIN_CONSTRAINTS

The user defined data types with rules defined

DOMAINS

The user defined data types

KEY_COLUMN_USAGE

The primary keys

PARAMETERS

The parameters of user defined functions or stored procedures

REFERENTIAL_CONSTRAINTS

The foreign key constraints

ROUTINES

The user defined functions and stored procedures

ROUTINE_COLUMNS

The columns returned by table-valued functions

SCHEMATA

The schemas

TABLE_CONSTRAINTS

The tables with constraints defined

TABLE_PRIVILEGES

The permissions granted to the tables

TABLES

The tables

VIEW_COLUMN_USAGE

The columns used in view definitions

VIEW_TABLE_USAGE

The tables used in view definitions

VIEWS

The views

 

To use one of the Information Schema views, query it just like a table:

SELECT * FROM INFORMATION_SCHEMA.COLUMNS

As a consultant, I am often looking at databases I have never seen before, sometimes with documentation and sometimes not. I use the INFORMATION_SCHEMA.COLUMNS view to help me find tables containing a particular column and more.

The Information Schema views can be another tool to help you work more efficiently!

 

Print
Categories: SQL Server
Tags:
Rate this article:
No rating

Please login or register to post comments.