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)

SQL Scripts, SQL Stored Procedure, SQL System Table, undocumented SQL
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

  • LOL!

    It took me half of a dozen lines to come up with what your one liner achieved!

    Good one!

    -Marlon Ribunal

    Reply
  • Hi Pinal,

    Shouldn’t we be using the information schema views in Sql 2005 and onwards?

    sp_msforeachdb @command1=’USE ?;SELECT * FROM INFORMATION_SCHEMA.Tables’

    Reply
  • Jacob Sebastian
    April 27, 2009 9:01 am

    Nice post Pinal!

    You can avoid the “USE” statement by using three part object name. For example:

    sp_msforeachdb ‘select * from ?.sys.tables’

    Reply
  • Jacob Sebastian
    April 27, 2009 9:02 am

    And some times it is important to know which database is being processed and the following might be helpful.

    sp_msforeachdb ‘select ”?” AS db, * from ?.sys.tables’

    Reply
  • Is there a way to use this query for auditing DMl events

    Reply
  • Thank you ,

    Sometimes a database may contain the name space

    sp_msforeachdb ‘select “?” AS db, * from [?].sys.tables’

    Reply
  • Hi,
    Im new to SQL server, i was wondering if there was anyway, we could list all tables for a single database along with the column names.
    Im finding this really hard so any help would be appreciated

    Reply
  • Hi Jyotsna,

    You will get all Table’s columns by this query:

    select t.name As TableName, c.Column_Name
    from sys.tables t
    INNER JOIN information_schema.columns c ON c.Table_Name = t.Name
    ORDER BY t.name

    Thanks,

    Tejas
    SQLYoga.com

    Reply
  • where the statistics of query i.e cost ,time of exe. etc are stored??(in which table?)

    Reply
  • in which table query statistics are stored?

    Reply
  • Hello Chaitanya,

    Statistics is stored in statblob column in sys.indexes table.

    Regards,
    Pinal dave

    Reply
  • thnx a ton…

    Reply
  • How to write a script to truncate tables using sp_msForEachDB. Please help.
    I am using:

    DECLARE @Table1 NVARCHAR(100)
    DECLARE @Table2 NVARCHAR(100)
    DECLARE @Table3 NVARCHAR(100)

    SET @Table1=’fwkErrorLog’
    SET @Table2=’fwkUserAuthenticationLog’
    SET @Table3=’fwkUserAuthenticationLogArchive’
    DECLARE @dbname NVARCHAR(500)
    DECLARE @sqlStatement NVARCHAR(500)
    DECLARE @singlequote char(4)
    SET @singlequote=””

    SET @sqlStatement= ‘USE ?; IF EXISTS (SELECT ”x” FROM INFORMATION_SCHEMA.TABLES WHERE TABLE_NAME = ‘ +
    + @singlequote+@Table1+@singlequote
    +’) TRUNCATE table ‘+@Table1

    EXEC sp_msForEachDB @sqlStatement

    SET @sqlStatement= ‘USE ?; IF EXISTS (SELECT ”x” FROM INFORMATION_SCHEMA.TABLES WHERE TABLE_NAME = ‘ +
    + @singlequote+@Table2+@singlequote
    +’) TRUNCATE table ‘+@Table2
    EXEC sp_msForEachDB @sqlStatement

    SET @sqlStatement= ‘USE ?; IF EXISTS (SELECT ”x” FROM INFORMATION_SCHEMA.TABLES WHERE TABLE_NAME = ‘ +
    +@singlequote+@Table3+@singlequote
    +’) TRUNCATE table ‘+@Table3
    EXEC sp_msForEachDB @sqlStatement

    Can it be much better

    Reply
  • How can i search all procedures within a database for a specific table name?

    Reply
  • How i can find which table has Created from which User when a Data base is used by Different Login.plz guide me.

    Reply
  • How can i search all the tables in a particular database.

    Reply
  • How do I use the output of the above command as input into another query. For instance, I want to get all the tables with a specific column name then use that result to further query each table to get the data for that column. Something like (DB2 syntax):

    select * from (select tabname from syscat.columns where colname = ‘my_column_name’);

    Of course that only gives me the table names, and not what I want…

    Reply
  • Colin,
    if it’s still interesting for you :
    exec sp_MSforeachtable ‘if COLUMNPROPERTY(object_id(”?”),”XXXXX”,”ColumnID”) is not null select ”?” [Table],* from ?’

    Replace XXXXX with your column name.

    Reply
  • tboonleong@yahoo.com
    October 21, 2011 2:15 pm

    this is great tips. => sp_msforeachdb ‘select “?” AS db, * from [?].sys.tables’

    But How to capture those output and use it into query statement ?becasue i need information for all those database names and all table names to export those data to text files using BPC.

    Reply
    • Create a table with same strcuture of what sp_msforeachdb returns. Then use this statement

      insert into #t(column_list)
      EXEC sp_msforeachdb ‘select “?” AS db, * from [?].sys.tables’

      Reply
  • How can I find stored procedures that have never been executed. Not only in the time the log persists in memory? Any idea?

    Reply

Leave a Reply