Information_schema views for SQL Server
The information schema views are defined in a special schema named INFORMATION_SCHEMA. This schema is contained in each database. Each information schema view contains metadata for all data objects stored in that particular database. The following table shows the relationships between the SQL Server names and the SQL standard names.
Tables
To get table information from a database, use the following query
select table_name , table_schema
from AdventureWorks.INFORMATION_SCHEMA.TABLES
order by table_schema,table_name
To fetch the column for a particular table
select column_name,data_type,isnull(character_maximum_length,numeric_precision) length,column_default,is_nullable
from AdventureWorks.INFORMATION_SCHEMA.COLUMNS
where table_name =’AWBuildVersion’
and table_schema=’dbo’
Views
select table_name as view_name, table_schema view_schema
from AdventureWorks.INFORMATION_SCHEMA.VIEWS
order by 2,1
