sp_MSforeachtable to execute procedure on each table
10,665
Solution 1
You need to add a bunch of '
.
PRINT ''DROPCONSTANT @TBLNAME=''''''+PARSENAME("?",1)+'''''', @FLDNAME=''''EMAIL_S'''' ''
Solution 2
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;
Comments
-
Soham Dasgupta almost 2 years
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'