use MyDatabase
exec sp_MSforeachtable
@command1 = N'PRINT ''?''',
@whereand = N'AND o.name LIKE ''T_TablesWeWant_%'''
Another example (WHICH WILL DROP TABLES!!!!)
use MyDatabase
exec sp_MSforeachtable
@command1 = N'DROP TABLE ?',
@whereand = N'AND o.name LIKE ''T_TablesWeWantToDrop_%'''
Actual context
The most well documented undocumented stored procedure in SQL Server would have to be the two foreach stored procedures sp_MSforeachdb and sp_MSforeachtable.
Being a lazy programmer who is currently working with a vended product that highly leverages SQL Server for it functionality, I did not want to physically go into each table in the tool to add the "hook" columns the tool needs to add to each table that it uses. Instead I would much rather script out adding in the "hook" columns. As one would assume the vendor did not have a script for this, much like learning that people in Columbus' time did not believe that the Earth was flat.
Luckily knowing that SQL Server had a few foreach stored procs for just this kind of scripting, Similar to von Bellingshausen, I hit the Google searching high and low. I came across a very good write up at Database Journal on how sp_MSforeachtable sings. It even included examples doing things similar to what I was looking to do.
I soon found that sp_MSforeachtable has an argument for commands (@command1) and another for filtering the tables that the commands would run against (@whereand).
I did the following quick test to see if I understood stored proc correctly (with some names changed and details left out):
use MyDatabase
exec sp_MSforeachtable
@command1 = N'PRINT ''?''',
@whereand = N'AND name LIKE ''T_TablesWeWant_%'''
This gave the following output:
Msg 209, Level 16, State 1, Line 1
Ambiguous column name 'name'.
Seeing that the write up was from 2004, I assumed that something most likely changed in SQL Server 2012. One of the difficulties related to using undocumented features, in fact that term is used as an euphemism for software bugs.
I figured I would use the force and read the source (note ... is code I do not think is important for the issue at hand, please who knows I maybe violating some term of contract if I show the whole thing).
exec sp_helptext 'sp_MSforeachtable'
create proc sys.sp_MSforeachtable
@command1 nvarchar(2000),
@replacechar nchar(1) = N'?',
@command2 nvarchar(2000) = null,
@command3 nvarchar(2000) = null,
@whereand nvarchar(2000) = null,
@precommand nvarchar(2000) = null,
@postcommand nvarchar(2000) = null
as
...
/* Create the select */
exec(N'declare hCForEachTable cursor global for select ''['' + REPLACE(schema_name(syso.schema_id), N'']'', N'']]'') + '']'' + ''.'' + ''['' + REPLACE(object_name(o.id), N'']'', N'']]'') + '']'' from dbo.sysobjects o join sys.all_objects syso on o.id = syso.object_id '
...
return @retval
Beholding the FROM clause I knew exactly what I need to do for the @whereand to work.
use MyDatabase
exec sp_MSforeachtable
@command1 = N'PRINT ''?''',
@whereand = N'AND o.name LIKE ''T_TablesWeWant_%'''
Giving the output I was looking for (with some names changed and details left out again):
[dbo].[T_TablesWeWant_ACCOUNTING_ACCOUNT]
[dbo].[T_TablesWeWant_ACCOUNTING_TRANSACTION]
[dbo].[T_TablesWeWant_ACCOUNTING_TRANSACTION_TYPE]
...
Yep, using the alias o for the name column is all that I need to do to get sp_MSforeachtable to work in SQL Server 2012.
