INTO = 'UPDATE ' + QUOTENAME( )+ '.' + )+ ' SET '
FIND ALL MY TABLES SQL TABS UPDATE
Basically the same code but with a small bit of better performance because of running one update for each table. Description: A kanon type of solution for a XML translation problem If you have any tweaks to offer, please submit them to the forum post mentionedīasically the same code but with a small bit of better performance because of running one update for each table.Hold locks on the UPDATEs until the rollback statement is issued. Just be careful on large databases and production databases since this will ROLLBACK statements to see what will be updated and than rollback the transactions. So be careful when you run this since it will be hitting every table and everyĬolumn that has one of these datatypes: char, nchar, nvarchar, varchar, text Depending on the size of your database this could take some time to run,.One thing to note is that if your replacement text is longer than the text yourĪre searching for you may run into issues of truncating data which is not handled Moving away from text and ntext to varchar(max) and nvarchar(max). In addition, this is another reason that Microsoft suggests Is that this only works for SQL 2005 and later where the nvarhcar(max) datatype Lot more work and therefore the approach we used is much simpler. UPDATETEXT to change the data in the text and ntext columns. If we did not use the CONVERT function we would have to use these two functions Logic, but this could be easily put in place. Although the CONVERT is not needed for char, nchar, varcharĪnd nvarchar it was easier to just convert everything instead of having different Use the REPLACE function against a text or ntext datatype, so we are doing a conversion The above shows the command that was run and how many rows were affected.Īs you can see we are using the CONVERT function to convert the datatypes to nvarchar(max) UPDATE Production.ProductReview SET = REPLACE(convert(nvarchar(max),),'Smith','Jones') WHERE LIKE '%Smith%' UPDATE Person.Contact SET = REPLACE(convert(nvarchar(max),),'Smith','Jones') WHERE LIKE '%Smith%' UPDATE Person.Address SET = REPLACE(convert(nvarchar(max),),'Smith','Jones') WHERE LIKE '%Smith%' If the above is run in the AdventureWorks database as is, these are the messages SET = 'UPDATE ' + + '.' + + ' SET = REPLACE(convert(nvarchar(max),),''' AND B.NAME IN ('char','nchar','nvarchar','varchar','text','ntext')
0 kommentar(er)
