How to process all tables with MSSQL

Applies to:

  • MS SQL Server 2000
  • MS SQL Server 2005

There are several ways of processing all the table:

  1. Hard code all the table names, with cut and paste the table name in the FROM clause.
  2. Use dynamic SQL, by processing either the SYSNAMES, SYSINDEXES...
  3. Use the Microsoft SQL Server stored procedure: sp_msforeachtable

Data used

  1. USE sql911;
  2. go
  3.  
  4. SELECT SUBSTRING(sobj.name,1,25) AS 'Tables',
  5.        sidx.ROWS AS 'Rows'
  6. FROM sysobjects sobj
  7. join sysindexes sidx ON sidx.id = sobj.id
  8.      and sidx.indid in (0,1)
  9. WHERE sobj.xtype = 'U'
  10. go
Tables                    Rows
------------------------- -----------
EMP_WORK                  75
DEPARTMENT                9
ORG                       10
Nums                      1048577
sales02                   32
transactions              16
sales                     57
projects                  20
Emps                      32

(9 row(s) affected)
  • This is a list of all the tables in the SQL911 database.

Process all tables

  1. sp_msforeachtable ' select ''?'' as ''Table'', count(*) as ''Rows'' from ? '
Table            Rows
---------------- -----------
[dbo].[EMP_WORK] 75

Table              Rows
------------------ -----------
[dbo].[DEPARTMENT] 9

Table       Rows
----------- -----------
[dbo].[ORG] 10

Table        Rows
------------ -----------
[dbo].[Nums] 1048577

Table           Rows
--------------- -----------
[dbo].[sales02] 32

Table                Rows
-------------------- -----------
[dbo].[transactions] 16

Table         Rows
------------- -----------
[dbo].[sales] 57

Table            Rows
---------------- -----------
[dbo].[projects] 20

Table        Rows
------------ -----------
[dbo].[Emps] 32
  • The stored procedure: sp_msforeachtable will process all the tables in the database.
  • The command must be enclosed in single quotes '.
  • To use single quotes in the command, quote it, meaning: use 2 single quotes, ie: as ''Table''
  • The command issued with sp_msforeachtable must operable on all tables. This means that you restricted to either list everything, or preform some kind of summary operations such as count(*).