Sql-server – Displaying the list of all tables in all database

sql serversql-server-2008-r2

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

Best Answer

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)
Related Question