Sql server® 2012 t-sql fundamentals

Information Schema Views

An information schema view is a set of views that resides in a schema called INFORMATION_SCHEMA 

and provides metadata information in a standard manner. That is, the views are defined in the SQL 

standard, so naturally they don’t cover aspects specific to SQL Server.

For example, the following query against the INFORMATION_SCHEMA.TABLES view lists the user 

tables in the current database along with their schema names.

SELECT TABLE_SCHEMA, TABLE_NAME 

FROM INFORMATION_SCHEMA.TABLES 

WHERE TABLE_TYPE = N'BASE TABLE';

The following query against the INFORMATION_SCHEMA.COLUMNS view provides most of the 

available information about columns in the Sales.Orders table.

SELECT  

  COLUMN_NAME, DATA_TYPE, CHARACTER_MAXIMUM_LENGTH,  

  COLLATION_NAME, IS_NULLABLE 

FROM INFORMATION_SCHEMA.COLUMNS 

WHERE TABLE_SCHEMA = N'Sales' 

  AND TABLE_NAME = N'Orders';