Get Tables & Columns using Information Schema views (Sql 2005)

The Information Schema is an ANSI standard set of read-only views which
provide access to metadata in a relational database. Metadata is data about data and it contains information about all of the tables, views, columns, constraints, procedures, routines, and more.

According to MSDN, Information Schema Views (Transact-SQL)
"Information schema views provide an internal, system table-independent view of the SQL Server metadata. Information schema views enable applications to work correctly although significant changes have been made to the underlying system tables."



Each information schema view contains metadata for all data objects stored in that particular database.

Show all Tables in a Database:

SELECT * FROM AdventureWorksLT.information_schema.tables
WHERE table_type = 'Base Table'



The following T-SQL give us back the list of tables and related columns:

Because we needed to collect info about user generated tables, 'U' for 'xtype'
has been used in the code:

* xtype has two values - ‘U’ or ‘S’
* U = User generated tables
* S = System generated tables

USE AdventureWorksLT

SELECT table_name,
    column_name,
    data_type,
    character_maximum_length
FROM Information_Schema.columns
WHERE table_name IN (Select name FROM sysobjects WHERE xtype = 'U')
ORDER BY table_name



If you need to have access to other columns about the tables, please look at the available columns in Information_Schema.columns.

SQL Server 2005 System Tables and Views
SQL Server 2005 System Tables and Views
Using the Information Schema Views

Get Tables & Columns using Information Schema views (Sql 2005)