Using Metadata to query Tables and Columns views in SQL Server

The Information Schema views are part of the SQL-92 standard.  There are viewes like TABLES, COLUMNS that provides information about the tables and columns in a database.

You can write a query these viewes to get metadata of "all tables and columns in the database".
 Just open database SQL window and execute following query:

Select  TABLE_CATALOG as [DB],
        TABLE_NAME as [Table],
        COLUMN_NAME as [Column],
        DATA_TYPE as [Type],
        CHARACTER_MAXIMUM_LENGTH as [Length]
from information_schema.columns
where TABLE_NAME IN
(
    select  TABLE_NAME
    from information_schema.tables
    where table_type = 'base table'
)
order by TABLE_NAME
You will see following rows in result set: