Example code executing TSQL for each Table in a database


Chris Skorlinski
Microsoft SQL Server Escalation Services

Not very fancy, but here are some examples of executing same statement for multiple tables in a database.  The first two techniques build a TSQL statement in the query output window.  You can then copy-n-paste that text into a new query and execute.  The last two examples just execute the statement by substituting the table name for the ‘?’.

–Build a TSQL Statement for all user tables in a database.

select ‘select count(*) from SalesLT.’ + name + CHAR(13) + CHAR(10) + ‘go’ + CHAR(13) + CHAR(10)
from sys.sysobjects
where type = ‘U’

–Sample Output:
–Copy-n-Paste output to new Query

select count(*) from SalesLT.Address
go
select count(*) from SalesLT.Customer
go
select count(*) from SalesLT.CustomerAddress
go
select count(*) from SalesLT.Product
go

–Build a TSQL Statement for all user tables in a database.
select ‘exec(‘ + ”’select count(*) from SalesLT.’ + name + ”’)’
from sys.sysobjects where type = ‘U’

–Sample Output:
–Copy-n-Paste output to new Query
exec(‘select count(*) from SalesLT.Address’)
exec(‘select count(*) from SalesLT.Customer’)
exec(‘select count(*) from SalesLT.CustomerAddress’)
exec(‘select count(*) from SalesLT.Product’)

–Executes statements below for each table in the database
EXEC sp_MSforeachtable ‘UPDATE STATISTICS ? WITH FULLSCAN’
EXEC sp_MSforeachtable "SELECT count(*) from ?"


Comments (1)

  1. Adrian says:

    Hi Chris,

     (maybe you have used this technique) in order to get the number of records for a set of tables could be used also a CLR scalar-valued function (see sql-troubles.blogspot.com/…/number-of-records-clr-version.html) to which is passed the schema/table name, the call to the function being as follows:

    SELECT s.name [schema_name]

    , T.name table_Name

    , dbo.NumberRecords('AdventureWorks.' + s.name + '.' + t.name) NumberRecords

    FROM AdventureWorks.sys.tables t

         JOIN AdventureWorks.sys.schemas s

           ON t.schema_id = S.schema_id

    ORDER BY s.name

    , T.name

    Best regards,

    Adrian