sp_MSforeachtable Table Filtering August 5, 2020

SQL Server provides the handy sp_MSforeachtable stored procedure for executing the specified T-SQL for each table in a database. But what if you only want to run it for certain tables? I found myself in this boat but a bunch of the recommendations I came across didn’t work. The whereand parameter was clearly what I wanted. However, say we wanted to filter out all tables whos names didn’t start with “Foo”:

EXEC sp_MSforeachtable @command1='print ''?''', @whereand='AND name LIKE ''Foo%'''

resulted in:

Msg 209, Level 16, State 1, Line 1
Ambiguous column name 'name'.


EXEC sp_MSforeachtable @command1='print ''?''', @whereand='AND ? LIKE ''Foo%'''

resulted in:

Msg 102, Level 15, State 1, Line 1
Incorrect syntax near '?'.

What to do? After looking at the code for the procedure, I noticed that it uses sys.all_objects (aliased as syso), so we can use the object_name function and the object_id field of sys.all_objects to get at the table name:

EXEC sp_MSforeachtable @command1='print ''?''', @whereand='AND object_name(object_id) LIKE ''Foo%'''


EXEC sp_MSforeachtable @command1='print ''?''', @whereand='AND syso.name LIKE ''Foo%'''