Brett Kaiser (x002548) Blog

Ran across this thread

http://www.sqlteam.com/forums/topic.asp?TOPIC_ID=135575

And figured I'd post this.  The code below will generate update statement for all tables that have a char data type.  It will then replace a quote (") with an empty sting

Guess you could automate this, but this is simple enough.  Just cut and paste the results and execute that.  I guess you could go on and schedule it to run nightly to do a clean up.

Hope you have a backup BEFORE you run the result set

CREATE TABLE #myTemp99 (n int IDENTITY(1,1), TABLE_NAME varchar(256), COLUMN_NAME varchar(256))
GO

INSERT INTO #myTemp99(TABLE_NAME, COLUMN_NAME)
     SELECT TABLE_NAME, COLUMN_NAME 
       FROM INFORMATION_SCHEMA.Columns
      WHERE DATA_TYPE IN ('char','varchar', 'nchar','nvarchar')
   ORDER BY TABLE_NAME, COLUMN_NAME
GO

SELECT * FROM #myTemp99
GO

SELECT SQL FROM (
SELECT 'UPDATE ' + TABLE_NAME + CHAR(13) + CHAR(10)
        + '   SET ' + COLUMN_NAME + ' = REPLACE('
        +COLUMN_NAME+','+''''+'"'+''''+','+''''+''''+')' AS SQL
, TABLE_NAME, 1 AS SQLGroup, n
  FROM #myTemp99 o
 WHERE EXISTS (SELECT TABLE_NAME
                  FROM #myTemp99 i
                WHERE i.TABLE_NAME = o.TABLE_NAME
             GROUP BY i.TABLE_NAME
               HAVING MIN(i.n) = o.n)
UNION ALL
SELECT '     , ' + COLUMN_NAME + ' = REPLACE('
        +COLUMN_NAME+','+''''+'"'+''''+','+''''+''''+')' AS SQL
, TABLE_NAME,  2 AS SQLGroup, n
  FROM #myTemp99 o
 WHERE EXISTS (SELECT TABLE_NAME
                  FROM #myTemp99 i
                WHERE i.TABLE_NAME = o.TABLE_NAME
             GROUP BY i.TABLE_NAME
               HAVING MIN(i.n) <> o.n)
UNION ALL
SELECT 'GO' AS SQL
, TABLE_NAME, 3 AS SQLGroup, 9999999 AS n
  FROM #myTemp99 o
 WHERE EXISTS (SELECT TABLE_NAME
                  FROM #myTemp99 i
                WHERE i.TABLE_NAME = o.TABLE_NAME
             GROUP BY i.TABLE_NAME
               HAVING MIN(i.n) = o.n)
) AS XXX
ORDER BY TABLE_NAME, SQLGroup, n


DROP TABLE #myTemp99
GO