sp_msforeachtable performing actions on variables

Question

I am trying to figure out how to use sp_msforeachtable to perform an action on all tables and variables that match variable/table names stored in another table

IE

I have a table that has 3 columns : table, variable, action

and I am trying to use sp_MSforeachtable to see which tables and variables match, and if match, perform that action on the table.

1. How do you call variable names in the sp_MSforeachtable statement? I know to use ? for the table name, but not sure how I would say if variable name=variable name then do X

2. Is there another way to do this without using this undocumented SP?

Ill try to explain better:

I am trying to clean personal info from a bunch of tables... I have a table that looks like this (not sure how to format a table, so imagine each entry is a seperate row, so the first row is Name, A, and set to '')

Variable
Name
Phone Number
Name

Table
A
A
B

Action
Set to ''
Set to '555-555-5555'
Set to ''

etc.

I then have a database full of tables....on table A, I would want my code to set all rows of variable 'Name'
to '' (blank)
, and Phone Number to '555-555-5555'
etc.and then move on to table B and do the same and so on

Answer 1

I would use a cursor and dynamic SQL:

--Set up for test:
CREATE TABLE #DataTable (column1 nvarchar(128) NOT NULL, column2 int NOT NULL);     --Create global temp table so it can be accessed from dynamic SQL.
CREATE TABLE ##ActionTable ([table] nvarchar(128) NOT NULL, variable nvarchar(MAX) NOT NULL, [action] nvarchar(MAX) NOT NULL);
INSERT INTO ##ActionTable ([table], variable, [action])
VALUES
    ('#DataTable', '1', 'INSERT INTO @table (column1, column2) VALUES (''@variable_1'', @variable);'),
    ('#DataTable', '2', 'INSERT INTO @table (column1, column2) VALUES (''@variable_1'', @variable);'),
    ('#DataTable', '3', 'INSERT INTO @table (column1, column2) VALUES (''@variable_1'', @variable);'),
    ('#DataTable', '4', 'INSERT INTO @table (column1, column2) VALUES (''@variable_1'', @variable);');

--Code:
DECLARE @action nvarchar(MAX);
DECLARE @table nvarchar(128);
DECLARE @variable nvarchar(MAX);

DECLARE rowCurser CURSOR FOR SELECT [table], variable, [action] FROM ##ActionTable;
OPEN rowCurser;
FETCH rowCurser INTO @table, @variable, @action

WHILE @@FETCH_STATUS = 0
BEGIN
    --Execute the code (pick one of the two.  Option 2 is safer and can be cached (faster), but it does not work with my example because the parameters are left as variables).
    --  Option 1:
    SET @action = REPLACE(REPLACE(@action, '@table', @Table), '@variable', @variable);
    EXECUTE(@action);
    --  Option 2:
    EXECUTE sp_executesql @stmt = N'INSERT INTO #DataTable (column1, column2) VALUES (CAST(@variable as nvarchar(128)) + N''_2'', @variable);', @params = N'@variable nvarchar(MAX)', @variable = @variable;

    --Setup for next iteration
    FETCH rowCurser INTO @table, @variable, @action
END
CLOSE rowCurser;
DEALLOCATE rowCurser;

--Check and cleanup from test
SELECT * FROM #DataTable;
DROP TABLE #DataTable;
DROP TABLE ##ActionTable;

Note: There are security concerns with what you are trying to do, since anyone who can add to your table will have the same access as the account which runs the script. You could reduce these concerns by defining the actions in another table which can only be edited by the administrator, then referencing the action in your existing table.

Note: It is best to have the data types of @action, @table, and @variable match their source columns. The variables an be any data type in your database (as long as it is not a local temp type). You will notice that there are two places in the code above where the types are defined, first where the variables are declared at the top, and second where the arguments for sp_executesql are defined in the string near the bottom.

Note: if @stmt and @params are assigned with a constant instead of a variable, make sure to prefix the constant with N so it will be read as a Unicode string.