Today at work I need to update every column in a table to remove all double quotes (“). It doesn’t matter so much what action needed to be undertaken on each column, but how would we to go about updating each column. Will we need to write out the update statement by hand. What if the table has hundreds of columns. What if this is but one table which will need to be updated in this way? The following script will produce update statements which update each column in a table to be that of the original column with the ” removed.
This statement can easily be edited to perform any action on the update column or it could be edited to differentiate the required actions based on the type of the column. Extend it how you see fit, anyway hope it helps someone.
SELECT 'UPDATE '+ QUOTENAME(SCHEMA_NAME(t.schema_id)) + '.' + QUOTENAME(t.name) + CHAR(10)
+ pk.ColumnList + ';'
FROM sys.tables t
CROSS APPLY (SELECT DISTINCT STUFF((SELECT CHAR(10) + CHAR(9) + ', ' + QUOTENAME(c.name) + ' = ' + 'REPLACE(' + QUOTENAME(c.name) + ', ''"'', '''')'
FROM sys.columns c
WHERE c.object_id = t.object_id
FOR XML PATH(''),TYPE).value('(./text())','VARCHAR(MAX)'),1,3,'') AS ColumnList