List tables in all databases in SQL Server instance

Article for: Teradata MySQL MariaDB

Query below lists all tables from all databases on SQL Server instance


declare @sql nvarchar(max);

select @sql = 
    (select ' UNION ALL
        SELECT ' +  + quotename(name,'''') + ' as database_name,
                    AS schema_name,
      COLLATE DATABASE_DEFAULT as table_name 
               FROM '+ quotename(name) + '.sys.tables t
               JOIN '+ quotename(name) + '.sys.schemas s
                    on s.schema_id = t.schema_id'
    from sys.databases 
    where state=0
    order by [name] for xml path(''), type).value('.', 'nvarchar(max)');

set @sql = stuff(@sql, 1, 12, '') + ' order by database_name, 

execute (@sql);


  • database_name - name of the database within schema resides
  • schema_name - name of the schema
  • table_name - name of the table


  • One row represents one table in database
  • Scope of rows: all tables from all schemas and all databases on SQL Server instance
  • Ordered by database name, schema name, table name

Sample results

You could also get this

Get this interactive HTML data dictionary in minutes with Dataedo.

See live HTML data dictionary sample

Try for free

Comments are only visible when the visitor has consented to statistics cookies. To see and add comments please accept statistics cookies.
There are no comments. Click here to write the first comment.