Monday, February 1, 2010

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],
from information_schema.columns
    select  TABLE_NAME

    from information_schema.tables
    where table_type = 'base table'
order by TABLE_NAME

You will see following rows in result set:

No comments:

Post a Comment