Applying a rule dynamically to all columns in different tables I am trying to apply a rule to remove double quotes from any value in my DB.
I declared the table name as an Input parameter (This is required). Now I want the rule to run dynamically against all columns &amp; all rows in the table. I have this so far. The rule runs successfully when executed but it only runs against the first column that appears in the information schema, How to make it run against the rest of columns ?

IF  EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[usp_tf_RemoveDoubleQuotes]') AND type in (N'P', N'PC'))
    DROP PROCEDURE [dbo].usp_tf_RemoveDoubleQuotes
GO 
SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER ON
GO

CREATE PROCEDURE [dbo].[usp_tf_RemoveDoubleQuotes]
(
     @tablename VARCHAR(100)
)
AS
SET NOCOUNT ON;

-- Declare variables

DECLARE  @FieldName varchar(100)
        ,@sql varchar(1000)

BEGIN
    -- Get the table and field for validating

    SELECT   @FieldName = COLUMN_NAME
    FROM     INFORMATION_SCHEMA.COLUMNS
    WHERE    TABLE_NAME = @tablename 

    -- Update field

    SET @sql = '
    UPDATE  ' + @tablename + '
    SET     ' + @FieldName + ' = (SELECT REPLACE(' + @FieldName + ',''"'',''''))'


    EXECUTE(@sql)

END
GO
You can retrieve only one name the way you are doing it. One relatively easy change to your current script to account for all columns would be to define a cursor on INFORMATION_SCHEMA.COLUMNS to go through all columns in a loop and apply your current method, as Justin Cave suggested in a comment:

DECLARE CURSOR ColumnsCursor LOCAL FAST_FORWARD
FOR
SELECT
  COLUMN_NAME
FROM
  INFORMATION_SCHEMA.COLUMNS
WHERE
  TABLE_NAME = @tablename
;

OPEN ColumnsCursor;

FETCH NEXT FROM ColumnsCursor INTO @FieldName;

WHILE @@FETCH_STATUS = 0
BEGIN
  SET @sql = '
  UPDATE  ' + @tablename + '
  SET     ' + @FieldName + ' = (SELECT REPLACE(' + @FieldName + ',''"'',''''))';

  EXECUTE(@sql);

  FETCH NEXT FROM ColumnsCursor INTO @FieldName;
END;

CLOSE ColumnsCursor;

DEALLOCATE ColumnsCursor;


Another good point by Justin is that you may want to introduce an additional filter to your COLUMNS query to process only columns that are either varchar or nvarchar:

...
WHERE
  TABLE_NAME = @tablename
  AND DATA_TYPE IN ('varchar', 'nvarchar')


One other minor point is that (SELECT REPLACE(...)) is redundant, just REPLACE(...) is enough, so the @sql assignment would look like this:

  SET @sql = '
  UPDATE  ' + @tablename + '
  SET     ' + @FieldName + ' = REPLACE(' + @FieldName + ',''"'','''')';


However, while these changes are simple, the resulting script would be rather inefficient against tables with multiple string columns. That might not matter much if yours is a one-off task, but you might still be interested in avoiding multiple passes over a table where one is enough to complete the job.

And you can do that if you change the basic logic slightly – instead of these steps:


take one column name,
build an UPDATE statement,
execute,
repeat for the next column until done,


you implement it like this:


take all columns and build an assignment list for the SET clause of the UPDATE,
build the UPDATE statement around the list,
execute.


Here is what it can look like in Transact-SQL:

DECLARE @SetList nvarchar(max), @sql nvarchar(max);

SELECT
  @SetList = ISNULL(@SetList + N', ', N'')
           + QUOTENAME(COLUMN_NAME) + N' = REPLACE('
           + QUOTENAME(COLUMN_NAME) + N',''"'','''')'
FROM
  INFORMATION_SCHEMA.COLUMNS
WHERE
  TABLE_NAME = @tablename
  AND DATA_TYPE IN ('varchar', 'nvarchar')
;

SET @sql = N'UPDATE ' + @tablename + ' SET ' + @SetList + ';';

EXECUTE(@sql);