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)

About these ads

32 thoughts on “SQL SERVER – List All the Tables for All Databases Using System Tables

  1. 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’

  2. Thank you ,

    Sometimes a database may contain the name space

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

  3. 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

  4. 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

  5. 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

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

  7. 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…

  8. 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.

  9. 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.

    • 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’

  10. 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

  11. 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.

  12. 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

    • SELECT * FROM SYS.schemas

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

  13. 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!)

  14. 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.

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s