Learn more about SQL Server tools

   
   























Latest SQL Server Tips














Free SQL Server Learning

SQL Server Audit with SQL Compliance Manager and SQL Secure

How to Roll your Own Value, RegEx and SoundEx Pattern Profiler in SSIS

Are You Making the Right Choices for SQL Server HA?

SQL Server Security Essentials

Implementing a SANLess SQL Server Cluster in Under an Hour















SQL Server INFORMATION_SCHEMA views

(Introduction)


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






 
Sponsor Information