To retrieve metadata information about tables using Information_schema related views

List of the tables:
-----------------
The below query gives the information about the list of the base tables in the given database.This will list all the base tables in the order of the table_schema and table_name.

Query 1 :
--------
Select
'['+TABLE_SCHEMA+'].'+'['+TABLE_NAME+']' AS TableName,
TABLE_TYPE as TableType
from
INFORMATION_SCHEMA.TABLES
where
TABLE_TYPE='BASE TABLE'
order by
TABLE_SCHEMA,TABLE_NAME

List of the views:
-----------------
The below query all the list of the views that are there in particular database.

Query 2 :
--------
Select
'['+TABLE_SCHEMA+'].'+'['+TABLE_NAME+']' AS TableName,
TABLE_TYPE as TableType
from
INFORMATION_SCHEMA.TABLES
where
TABLE_TYPE='VIEW'

List of all columns in the base tables and views:
----------------------------------------------
The below query will give the list of the columns in the tables that are there in the database.

Query 3 :
--------
Select
'['+TABLE_SCHEMA+'].'+'['+TABLE_NAME+']' AS TableName,
COLUMN_NAME,
DATA_TYPE
from
INFORMATION_SCHEMA.COLUMNS
order by
TABLE_SCHEMA,TABLE_NAME

List all the constraints:
-------------------------------------------------------

To list all the constraints that are there for all the tables in particular database.

Query 4 :
--------

Select
'['+TABLE_SCHEMA+'].'+'['+TABLE_NAME+']' AS TableName,
CONSTRAINT_NAME
from
INFORMATION_SCHEMA.TABLE_CONSTRAINTS
where
TABLE_SCHEMA is not null and
TABLE_NAME is not null
order by
TABLE_SCHEMA,TABLE_NAME


Hope this helps