Execute the following Microsoft T-SQL example scripts in SQL Server Management Studio Query Editor to list all tables in the AdventureWorks2008 sample database and in all databases on PRODSRV\SQL2008 SQL Server instance.

------------
-- Microsoft SQL Server database meta data - list of all tables in database
------------
USE AdventureWorks2008;
-- SQL list all tables using system view sys.objects
SELECT   SchemaName = SCHEMA_NAME(schema_id),
         TableName = name
FROM     sys.objects
WHERE    TYPE = 'U'
         AND is_ms_shipped = 0
ORDER BY SchemaName,
         TableName
GO
 
/* Partial results
 
SchemaName        TableName
dbo               AWBuildVersion
dbo               Category
dbo               DatabaseLog
dbo               ErrorLog
dbo               SubCategory
dbo               sysdiagrams
HumanResources    Department
HumanResources    Employee
HumanResources    EmployeeDepartmentHistory
HumanResources    EmployeePayHistory
*/

 
-- T-SQL alternative: using a different system view - sys.tables
SELECT   SchemaName = SCHEMA_NAME(schema_id),
         TableName = name
FROM     sys.tables
ORDER BY SchemaName,
         TableName

GO
------------

 
-- MSSQL information_schema views - ANSI 92 SQL compatible
SELECT *
FROM     INFORMATION_SCHEMA.TABLES
WHERE    table_type = 'BASE TABLE'
ORDER BY TABLE_SCHEMA,
         TABLE_NAME
/* Partial results
 
TABLE_CATALOG     TABLE_SCHEMA      TABLE_NAME                    TABLE_TYPE
AdventureWorks    Sales             SalesOrderHeader              BASE TABLE
AdventureWorks    Sales             SalesOrderHeaderSalesReason   BASE TABLE
AdventureWorks    Sales             SalesPerson                   BASE TABLE
AdventureWorks    Sales             SalesPersonQuotaHistory       BASE TABLE
AdventureWorks    Sales             SalesReason                   BASE TABLE
AdventureWorks    Sales             SalesTaxRate                  BASE TABLE
AdventureWorks    Sales             SalesTerritory                BASE TABLE
AdventureWorks    Sales             SalesTerritoryHistory         BASE TABLE

*/

------------
--- SQL list all table in all databases on PRODSRV\SQL2008 SQL Server instance
DECLARE  @CMD VARCHAR(1024)
 
CREATE TABLE #TableList (
  DatabaseName SYSNAME,
  SchemaName   SYSNAME,
  TableName    SYSNAME)
 
SET @CMD = 'USE [?]; SELECT DB_NAME()DATABASE_NAME, SCHEMA_NAME(schema_id),
             NAME FROM SYS.TABLES'
PRINT @CMD -- test & debug
 
-- T-SQL insert exec undocumented system procedure
INSERT INTO #TableList
EXEC SP_MSFOREACHDB  @CMD
 
DELETE FROM #TableList
WHERE  DatabaseName IN ('master','msdb','tempdb','model')
 
SELECT *
FROM   #TableList
ORDER BY DatabaseName, SchemaName, TableName
GO
/* Partial results
 
DatabaseName            SchemaName  TableName
AdventureWorks2008      Sales       SalesTerritoryHistory
AdventureWorks2008      Sales       ShoppingCartItem
AdventureWorks2008      Sales       SpecialOffer
*/
 
DROP TABLE #TableList
GO
------------