sp_MSforeachtable to execute procedure on each table

Question

I want to print some dynamic query to execute a procedure on all tables in the database. This is what I've written so far -

EXEC SP_MSFOREACHTABLE '
    IF EXISTS(SELECT * FROM INFORMATION_SCHEMA.COLUMNS 
       WHERE COLUMN_NAME="EMAIL_S" AND TABLE_NAME=PARSENAME("?",1))
    BEGIN
        PRINT ''EXEC DROPCONSTANT @TBLNAME=''+PARSENAME("?",1)
                  +'', @FLDNAME=''''EMAIL_S'''' ''
        PRINT CHAR(10)+CHAR(13)
    END
'

The output is not what I expect it to be -

EXEC DROPCONSTANT @TBLNAME=bill, @FLDNAME='EMAIL_S'

But what I really want it -

EXEC DROPCONSTANT @TBLNAME='bill', @FLDNAME='EMAIL_S'

Looks like @Aaron's answer covers this fairly well, but if you do need `sp_MSForeachtable` in the future, it has a [parameter](http://stackoverflow.com/a/9680217/15498) that lets you filter the tables it actually executes against. — Damien_The_Unbeliever, Mar 30 '12 at 12:15

score 7 · Answer 1 · answered Mar 30 '12 at 11:52

How about this instead:

DECLARE @output NVARCHAR(MAX) = N'';

SELECT @output += CHAR(13) + CHAR(10) 
  + 'EXEC DROPCONSTANT @TBLNAME=''' + t.name + ''','
  + '@FLDNAME=''EMAIL_S'';'
  FROM sys.tables AS t
  INNER JOIN sys.columns AS c
  ON t.[object_id] = c.[object_id]
  WHERE c.name = 'EMAIL_S';

SELECT @output;
-- EXEC sp_executesql @output;

score 3 · Accepted Answer · answered Mar 30 '12 at 11:46

3

You need to add a bunch of '.

PRINT ''DROPCONSTANT @TBLNAME=''''''+PARSENAME("?",1)+'''''', @FLDNAME=''''EMAIL_S'''' ''

answered Mar 30 '12 at 11:46

Mikael Eriksson

136,425
22
210
281

This works fine. Just couldn't figure out that we need 6 quotes. – Soham Dasgupta Mar 30 '12 at 11:48

sp_MSforeachtable to execute procedure on each table

2 Answers2

Linked