MS SQL Server – Information schema database metadata

Information schema is part of the SQL-92 standard, holds the structure if your database.

Test Environment: MS Server 2008

SELECT * FROM INFORMATION_SCHEMA.TABLES

SELECT 
TABLE_CATALOG,/*'TABLE_CATALOG' IS SQL-92 STANDARD NAME FOR 'DATABASE'*/
TABLE_SCHEMA,/*'TABLE_SCHEMA' IS SQL-92 STANDARD NAME FOR 'OWNER'*/
TABLE_NAME,
TABLE_TYPE
FROM INFORMATION_SCHEMA.TABLES