Loop thru SQL tables

"How can I loop through all the tables in a SQL Server database, and run T-SQL on each table where the name matches a pattern, without using cursors?"

This question was recently posted at a SQL forum. Here is the solution. . .

SQL Server has a system stored procedure sp_tables that displays all the tables in the database, and has an optional parameter that allows filtering the results. For example, to list all tables that have the prefix 'tbl' you would use:

sp_tables 'tbl%'

To use these results in subsequent processing, we need to save the output to a table. After looking up the sp_tables procedure in BOL, we can create a table to hold the results, and then execute the procedure to populate the table:

CREATE TABLE #temp
(
TABLE_QUALIFIER sysname,
TABLE_OWNER sysname,
TABLE_NAME sysname,
TABLE_TYPE varchar(32),
REMARKS varchar(254)
)

INSERT INTO #temp
EXEC sp_tables 'tbl%'

Now we need a strategy for looping thru these records & processing each table one-by-one - - We can add a flag to the temp table to mark when each record is processed:

EXEC sp_executesql N'ALTER TABLE #temp ADD Processed bit NOT NULL DEFAULT (0)'

Now, we select the first table name, run our T-SQL on that table, and mark the flag as being processed. The WHILE loop continues as long as there are any records that have not been processed. At the beginning of our code we need to insert the line:

DECLARE @TABLE_NAME sysname

and then we can write our loop:

WHILE (SELECT COUNT(*) FROM #temp WHERE Processed = 0) > 0
BEGIN
SET @TABLE_NAME = (SELECT TOP 1 TABLE_NAME FROM #temp WHERE Processed = 0)
PRINT @TABLE_NAME
UPDATE #temp SET Processed = 1 WHERE TABLE_NAME = @TABLE_NAME
END

...which simply prints the name of each table, where the table name matched our filter.

To execute a real SQL statement against each table, we need to add another variable at the top of our code:

DECLARE @sql nvarchar(4000)

and then the loop is written like this:

WHILE (SELECT COUNT(*) FROM #temp WHERE Processed = 0) > 0
BEGIN
SET @TABLE_NAME = (SELECT TOP 1 TABLE_NAME FROM #temp WHERE Processed = 0)
-- replace next line with your SQL statement
SET @sql = 'SELECT COUNT(*) FROM ' + @TABLE_NAME
EXEC sp_executesql @sql
UPDATE #temp SET Processed = 1 WHERE TABLE_NAME = @TABLE_NAME
END

...which prints the number of rows in the table, for each table where the name matched our filter.

You can view the complete code here:

SQL_Loop_thru_tables