mssqltips logo

SQL Server INFORMATION_SCHEMA views Tutorial



By:

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

Last Update: 7/2/2009




More SQL Server Solutions











Post a comment or let the author know this tip helped.

All comments are reviewed, so stay on subject or we may delete your comment. Note: your email address is not published. Required fields are marked with an asterisk (*).

*Name
*Email
Email me updates

Signup for our newsletter

I agree by submitting my data to receive communications, account updates and/or special offers about SQL Server from MSSQLTips and/or its Sponsors. I have read the privacy statement and understand I may unsubscribe at any time.





Tuesday, April 09, 2019 - 12:56:45 PM - problen Back To Top

 Thank you that was very useful !



download





get free sql tips

I agree by submitting my data to receive communications, account updates and/or special offers about SQL Server from MSSQLTips and/or its Sponsors. I have read the privacy statement and understand I may unsubscribe at any time.



Learn more about SQL Server tools