Information Schema Views in SQL Server

An information schema view is one of several methods SQL Server 2005 provides for obtaining metadata.
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. The information schema views included in SQL Server 2005 comply with the SQL-92 standard definition for the INFORMATION_SCHEMA.

To get table details on a database

SELECT * FROM INFORMATION_SCHEMA.TABLES 

To get table column details on a database

SELECT * FROM INFORMATION_SCHEMA.COLUMNS