SQL Tip: How to Select All Table Names in a Specific Database

The method provided below will give a listing all tables in a specific database and works in Microsoft SQL Server 2005, 2008 and up. Try it by creating a new query in Query Designer or SQL Server Management Studio (SSMS).

USE [YourDbName]

GO

SELECT TABLE_NAME FROM INFORMATION_SCHEMA.TABLES WHERE TABLE_TYPE = 'BASE TABLE'

GO

The other method most often suggested has been done away with but still works in SQL version 2000 and earlier:

USE YourDBName

GO

SELECT *

FROM sys.Tables

GO