Friday, March 27, 2009

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

4 comments:

Unknown said...

Wow man! You're a genius! I've been searching couple of ours for really "working" code for the system procedures and with minimum processing time - yours is just the best! Thx for the insight!

Kind regards,

muodyPL

Anonymous said...

Gr8 post, it helped me so much .. thank you :)

NaughtyZute said...

Loving the script, but I'm getting this error repeatedly:

Msg 207, Level 16, State 3, Line 30
Invalid column name 'Processed'.
Msg 207, Level 16, State 3, Line 34
Invalid column name 'Processed'.
Msg 207, Level 16, State 1, Line 49
Invalid column name 'Processed'.

Running one line at a time, everything is fine. My table has just the declared columns before the sp_executesql with the ALTER table in it... and the processed columns is there after the sproc runs.

Any insight on this error would be appreciated. Thanks

Bill Mitchell said...

Hello Zute...did you copy / paste the script from
http://www.wvmitchell.com/blogger/SQL_Loop_thru_tables.htm
...if you post your script I can take a look at it. Thx.