INFORMATION_SCHEMA views

 

Care of http://www.mssqltips.com/tutorial.asp?tutorial=179

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 be called from any database in your SQL Server instance.  The reason these were developed was so that they are standard across all database platforms.  In SQL 2005 and SQL 2008 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 would issue the following in that database.

USE AdventureWorks
GO
SELECT

*
FROM
INFORMATION_SCHEMA.TABLES


 

Advertisements

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s