Monday, June 27, 2016

SQL Server INFORMATION_SCHEMA views

Following are the information_schema tables:-

  • An information schema view is one of several methods SQL Server provides for obtaining metadata. 
  • Information schema views provide an internal, system table-independent view of the SQL Server metadata. Information schema views enable applications to work correctly although significant changes have been made to the underlying system tables. 
  • The information schema views included in SQL Server comply with the ISO standard definition for the INFORMATION_SCHEMA.

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

Example:-select * from INFORMATION_SCHEMA.TABLES



SELECT TABLE_CATALOG, TABLE_SCHEMA, TABLE_NAME, COLUMN_NAME, COLUMN_DEFAULT  
FROM AdventureWorks2012_Data.INFORMATION_SCHEMA.COLUMNS  
WHERE TABLE_NAME = N'Product';  
GO