SQL SERVER – List All the Tables for All Databases Using System Tables

Today we will go over very simple script which will list all the tables for all the database.

sp_msforeachdb 'select "?" AS db, * from [?].sys.tables'

Update: Based on comments received below I have updated this article. Thank you to all the readers. This is good example where something small like this have good participation from readers.

Reference : Pinal Dave (http://www.SQLAuthority.com)

, , ,
Previous Post
SQLAuthority News – Interview of Author on 60 Seconds with Pinal Dave
Next Post
SQLAuthority News – Starting the SQL Journey – How Did I Get Started With SQL?

Related Posts

44 Comments. Leave new

  • teri.perrell@ssa.gov
    March 29, 2012 2:14 am

    Hello!

    I have over 700 databases on my server and have a task to identify each database with a column named “Checked_Out”. This means I need to somehow query the system tables for database name then subquery for table.column = “Checked_Out”.

    Thanks, Teri

    Reply
  • As far as I can tell. None of these queries show both master relevent tables and sys.schema local DB tables. Is there a short and easy query to enumerate ALL databases and ALL tables in both the master and also local to each DB? Hope that sounded clear.

    Reply
  • The query doesnt tell in what database the field is belongs to.

    Reply
  • Dear all,

    i have one problem in ssrs.i need to display sales report customer wise [sales and budget and variance and variance percentage] from different databases but in single server.it will be there detailes in multiple databases.i need to display detailes in single report.could you please anyone help me.for example we are maintaining 2010,2011 databases so i need to display customer no,sales,budget,variance,variance percentage in single report …

    year from 2012 year to 2013 date filter 01-01-12..31-12-13

    Customer No. | Sales -2012 |Budget-2012 |Variance-2012 |Variance %-2012 Sales -2013 |Budget-2013| Variance-2013 |Variance %-2013

    i need details like this in one report using multiple databases.please anyone help me.

    navya krishna katta

    Reply
  • How can we identify a tables as a system table or a user-defined table?

    Reply
    • Hafiz Muhammad Saleh
      April 17, 2014 4:39 pm

      SELECT * FROM SYS.schemas

      SELECT * FROM SYS.TABLES WHERE [schema_id]=5 AND convert(Varchar,create_date,112)>=’20120701′
      ORDER BY 1

      Reply
  • can we add a where clause to find the databases with specific table .
    eg sp_msforeachdb ‘select “?” AS db, name from [?].sys.tables where name = ”mytableName”’
    (this didn’t work!)

    Reply
  • Brilliant Pinal.
    Allow me to modify it slightly to add the associated columns as well:
    sp_msforeachdb ‘select “?” AS db, t.*, c.* from [?].sys.tables t, [?].sys.columns c where t.object_id = c.object_id’

    This ought to help in compiling a Server specific Data Catalog of sorts.

    Reply
  • how to get all data base list with name,size(MB,GB) through script in text file.
    please reply………..

    Reply
  • Dear all,
    could you please anyone help me.how to get all data base list with name,size(MB,GB) through script in text file.
    please reply………

    Reply
  • hi can i get tables list of existed database in sql server according to the constraints (database model ) because it will not throw error when all scripts execute one by one or bulk .

    Reply
  • Can we write a procedure having 3 input parameters
    1)X Database Name
    2)Y Database Name
    3)Table Name
    what I want as a out put is the columns which are not matching in the two databases with same table name.

    Reply
  • I need syntax to search all tables for a company name.

    Reply
  • Hello Sir,

    sir can you identifying which table is temporary in database . I have fetching following issue get tables details

    generate Error like :

    The object ‘#A6AD16E2’ does not exist in database ‘tempdb’ or is invalid for this operation.

    I have get all Tables List query like:

    “use ” + databaseName + ” select name from sys.tables”

    When i get tables details then generate Error

    USE ” + databaseName + ” EXEC sp_spaceused #A6AD16E2″

    The object ‘#A6AD16E2’ does not exist in database ‘tempdb’ or is invalid for this operation.

    how can resolve this issue. any way to identifying temporary ?

    Reply
    • Pedro Oliveira
      May 18, 2018 6:56 pm

      Temporary tables in tempdb are allways temporary, if you can’t get names and sizes atomically, you won’t have the table waiting there to get its size. Imagine that this tables were used only for sorts.

      Reply
  • Janakiraman Ramaswamy
    January 27, 2017 1:00 am

    Hi Pinal,

    Could you please help me with query to find all the tables used by Stored Procedure across database?
    I got the query in single database. If SP use tables across multiple database then I need to report it to query output…

    Reply
  • The above script is does the job . i cannot copy the whole data at once as the results are seperated by databases . Kindly suggest the alternative .

    Reply
  • Dear all , how can i retrieve all table in db to my Reports. Example :
    Table 001
    Table 002
    Table 003
    Table 004

    *on screen*

    i click Table 001 and it will display all the data n column in my report.

    i click Table 004 and it will display all the data n column in my report.

    *all the Table have different column
    *i have 300 Table in my db

    Reply

Leave a Reply

Menu