Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

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

share|improve this question

4 Answers 4

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)
share|improve this answer
1  
+1 but I typically add filters to leave out system databases (the list is usually not useful) and databases that are currently offline (those will error). –  Aaron Bertrand Mar 27 at 12:53
    
@AaronBertrand D.state = 0 will take care of offline databases but how can you tell if it is a system database? –  Mikael Eriksson Mar 27 at 13:03
    
Right, there isn't really a system flag for databases, but I use database_id BETWEEN 5 and 32766. This won't leave out things like ASPState, SSISDB, ReportServer etc. but you could add additional filtering for that if you wanted. :-) –  Aaron Bertrand Mar 27 at 13:05
    
@AaronBertrand Just checked what SSMS does when listing the system database. when dtb.name in (''master'',''model'',''msdb'',''tempdb''). So in this case adopting the same as SSMS it would be D.name not in ('master','model','msdb','tempdb') –  Mikael Eriksson Mar 27 at 13:07

I think it's also worth noting the PowerShell/SMO way to do this. This is one of those requirements that could be quite painless with PowerShell, and definitely the easiest approach if you need to gather this data across multiple servers:

$SqlServerName = "YourInstanceName"

$SqlServer = New-Object Microsoft.SqlServer.Management.Smo.Server($SqlServerName)

foreach ($Database in (
    $SqlServer.Databases | 
        Where-Object {$_.Status -eq [Microsoft.SqlServer.Management.Smo.DatabaseStatus]::Normal })) {

    $Database.Tables | 
        Select-Object @{Name = "DatabaseName"; Expression = { $Database.Name }},
            @{Name = "SchemaName"; Expression = { $_.Schema }},
            @{Name = "TableName"; Expression = { $_.Name }}
}
share|improve this answer

You can use the sp_MSforeachdb procedure to do this

create table #tablist(db sysname, tab sysname);

exec sp_msforeachdb '
 use [?];
 insert into #tablist 
 select db_name(),name from sys.tables;'

select * from #tablist
drop table #tablist
share|improve this answer
3  
Beware of sp_MSforeachdb : sqlblog.com/blogs/aaron_bertrand/archive/2010/02/08/… by @AaronBertrand. –  Thomas Stringer Mar 27 at 12:50
3  
Agreed, please do not encourage people to use this horribly crippled, problematic, undocumented and unsupported procedure. –  Aaron Bertrand Mar 27 at 13:12

Just enter:

SELECT db_name(), name FROM sys.tables;

Copy and paste the list.

share|improve this answer

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.