How to list all tables in a database using a query

The simplest way to get all tables in a specific database it’s to see their list in SQL Server Management Studio (SSMS) Object explorer.

However, once in a while we need more than just see table names. As we already know, almost everything can be done in a few different ways, but today I will show how actually to list all tables in a database (or databases) using a query!

Before we diving, a little refinement about examples: I want to show only tables names from one particular database, so every example will be with WHERE clause that filter for me only user-defined tables names (for more of that, you can use references links). So, lets start!

I. For old versions, such a SQL Server 2000, we can query system table sys.sysobjects that contains one row for each object that is created within a database.

USE
	[AdventureWorks2017]
GO


SELECT
	[name]
FROM
	sys.sysobjects
WHERE
	[xtype] = 'U'

II. SQL Server 2005&2008 gave us a system view INFORMATION_SCHEMA.TABLES These allow us easily view a wide variety of data for this particular database and returns one row for each table (for which the current user has permissions).

USE
	[AdventureWorks2017]
GO


SELECT
	TABLE_NAME AS [Name]
FROM
	INFORMATION_SCHEMA.TABLES
WHERE
	TABLE_TYPE = 'BASE TABLE'

III. Starting SQL Server 2008 we receive a system view sys.objects that contains a row for each user-defined, schema-scoped object that is created within a database, including natively compiled scalar user-defined function. That view actually is a "new generation" of sysobjects that was included as a view for backward compatibility.

USE
	[AdventureWorks2017]
GO


SELECT
	[Name]
FROM
	sys.objects
WHERE
	[type] = 'U'

IV. Guess what, again 2008 version and again view ?? . Another system view sys.tables that returns a row for each user table in a database.

USE
	[AdventureWorks2017]
GO


SELECT
	[Name]
FROM
	sys.tables
WHERE
	[type] = 'U'

V. View sys.all_objects shows the UNION of all schema-scoped user-defined objects and system objects, and not "created within a database" like sys.objects. That one is basically sys.objects expanded to contain Microsoft System objects, as well. The sys.all_objects view contains a UNION between sys.objects and sys.system_objects.

If you want to search for objects that are created by the user only, recommended using the sys.objects view instead of this one. Because the result set will contain all tables that not created with database. For example, extended events tables trace_xe_action_map and trace_xe_event_map .

SELECT
	[name]
FROM
	sys.all_objects
WHERE
	[type] =  'U'

VI. Another way, is to use undocumented stored procedure sp_MSforeachtable that is mostly used to apply a T-SQL command to every table, that exists in the current database. Something like that:

USE
	[AdventureWorks2017]
GO


IF OBJECT_ID('tempdb..#Tables', 'U') IS NOT NULL
DROP TABLE #Tables

CREATE TABLE #Tables
				(
				TableName NVARCHAR(256)
				)

EXEC sp_MSforeachtable 'INSERT INTO #Tables SELECT ''?'''

SELECT
	REPLACE(REPLACE((RIGHT(TableName, LEN(TableName) - CHARINDEX('.', TableName))), '[',''), ']','') AS [Name]
FROM
	#Tables

or just print all result set, without cleaning schema name

USE
	[AdventureWorks2017]
GO


EXEC sp_MSforeachtable 'print ''?'''

Are we there yet? Stay strong, The Last one! ??

Look like we have enough options to query a database tables list, but how can we list all user-defined table in all databases?

VII. It is an undocumented Stored Procedure sp_MSforeachdb that located in the "master" database and allows us to iterate same T-SQL statement against through all the databases in a SQL Server instance. This system procedure using a global cursor and perform loops on all result set of (SELECT name FROM master.dbo.sysdatabases). So be careful to use it on busy systems!

USE
	[master]
GO

EXEC sp_MSforeachdb 'USE [?]
						DECLARE
							@dbname NVARCHAR(128)

						SET @dbname = DB_NAME(DB_ID())

						
						SELECT
							@dbname as DB,
							[Name]
						FROM
							sys.tables
						WHERE
							[type] = ''U''
							AND
							DB_ID() > 4		-- not sys databases
					'

using local variable

USE
	[master]
GO


DECLARE
	@cmd NVARCHAR(256)

SET @cmd='USE [?]
				DECLARE
					@dbname NVARCHAR(128)

				SET @dbname = DB_NAME(DB_ID())

						
				SELECT
					@dbname as DB,
					[Name]
				FROM
					sys.tables
				WHERE
					[type] = ''U''
					AND
					DB_ID() > 4		-- not sys databases
			'

EXEC sp_MSforeachdb @cmd

Those examples will return us a number of result sets that equal to the number of databases in our instance. From here, it's can be a little bit not comfortable. I'd prefer another syntax...

For example, using table variable

USE
	[master]
GO

DECLARE @AllTables TABLE
						(
						DB		NVARCHAR(64),
						[Name]	NVARCHAR(256)
						)

INSERT INTO @AllTables (DB, [Name])
	EXEC sp_MSforeachdb 'USE [?]
							DECLARE
								@dbname NVARCHAR(128)

							SET @dbname = DB_NAME(DB_ID())

						
							SELECT
								@dbname	AS DB,
								[Name]	AS Name
							FROM
								sys.tables
							WHERE
								[type] = ''U''
								AND
								DB_ID() > 4		-- not system databases
						'

SELECT
	DB,
	[Name]
FROM
	@AllTables
ORDER BY
		DB ASC,
		[Name] ASC

or local temporary table (for better performance)

USE
	[master]
GO


IF OBJECT_ID('tempdb..#Tables', 'U') IS NOT NULL
DROP TABLE #Tables

CREATE TABLE #Tables
				(
				DB		NVARCHAR(64),
				[Name]	NVARCHAR(256)
				)

INSERT INTO #Tables
EXEC sp_MSforeachdb 'USE [?]
						DECLARE
							@dbname NVARCHAR(128)

						SET @dbname = DB_NAME(DB_ID())

						
						SELECT
							@dbname	AS DB,
							[Name]	AS Name
						FROM
							sys.tables
						WHERE
							[type] = ''U''
							AND
							DB_ID() > 4		-- not system databases
					'

SELECT
	DB,
	[Name]
FROM
	#Tables
ORDER BY
		DB ASC,
		[Name] ASC

For the summary:

I hope you will find my post useful! I got tried to put together all simple ways to query a list of all tables in a database and all tables in the instance! I'm pretty sure there are other ways and you're very welcome to add it in the comments ??