Displaying the list of all tables in all database

How can I display a list containing all the tables of all the database in a SQL Server 2008 R2 server?

You need a query for each database against sys.tables.

select 'master' as DatabaseName,
T.name collate database_default as TableName
from master.sys.tables as T
union all
select 'tempdb' as DatabaseName,
T.name collate database_default as TableName
from tempdb.sys.tables as T
union all
select 'model' as DatabaseName,
T.name collate database_default as TableName
from model.sys.tables as T
union all
select 'msdb' as DatabaseName,
T.name collate database_default as TableName
from msdb.sys.tables as T


You can use sys.databases to build and execute the query dynamically.

declare @SQL nvarchar(max)

set @SQL = (select 'union all
select '''+D.name+''' as DatabaseName,
T.name collate database_default as TableName
from '+quotename(D.name)+'.sys.tables as T
'
from sys.databases as D
for xml path(''), type).value('substring((./text())[1], 13)', 'nvarchar(max)')

--print @SQL
exec (@SQL)


StackExchange Database Administrators Q#61848, answer score: 11