Another post for me that is simple and hopefully serves as an example for people trying to get blogging as #SQLNewBloggers.
There was a post recently where a user asked about needing to update all the data in all their tables to upper case. A somewhat strange requirement, though one I could see in some environments where they want to ensure all data matches in searches.
There isn’t a good way to do this, since you have disparate table names and column names. The quick way to do this one time is with a cursor, though I’d worry about performance here if this ran at any scale, and more often than once in a very rare time.
In any case, the way to get schema, table and column names is to query the INFORMATION_SCHEMA.COLUMNS view. I’d limit this to character columns of the non- and Unicode types. In the code below, I get that data into the cursor.
DECLARE updatecurs CURSOR FOR SELECT c.TABLE_SCHEMA , c.TABLE_NAME , c.COLUMN_NAME FROM INFORMATION_SCHEMA.COLUMNS AS c WHERE c.DATA_TYPE IN ( 'varchar', 'char', 'nvarchar', 'nchar' ); DECLARE @schema VARCHAR(100) , @table VARCHAR(100) , @col VARCHAR(100) , @cmd VARCHAR(8000); OPEN updatecurs; FETCH NEXT FROM updatecurs INTO @schema , @table , @col; WHILE @@FETCH_STATUS = 0 BEGIN SELECT @cmd = 'update ' + QUOTENAME(@schema) + '.' + QUOTENAME(@table) SELECT @cmd = @cmd + ' set ' + @col + '= UPPER(' + @col + ')' SELECT @cmd FETCH NEXT FROM updatecurs INTO @schema , @table , @col; END; DEALLOCATE updatecurs;
The code then loops through the cursor and builds an update statement for each column. This could be enhanced to get a single statement for all columns in a table, but it’s a quick and dirty piece of code.
There is a SELECT @cmd statement in there that shows what command is executed. To make this work, that would be changed to EXEC(@cmd), but make sure the code is what you want.
Not pretty, but effective.
Quick, effective code. This has caveats, but works.
If you want to write about this, change the cursor to make one update per table.
Pingback: Uppercase All Characters (and in a Single Query!) – Sql Quantum Leap
Hi Steve. Interesting challenge. I do realize that you said, “Quick, effective code. This has caveats…”, but even though it was not intended to be perfect, I thought I should mention a few things that will prevent it from working in various cases so that readers can better determine if this will or will not work for them:
1) The variables for the dynamic SQL are all VARCHAR instead of NVARCHAR. This will cause problems if any identifiers (schema, table, columns, etc) contain characters that can’t be mapped directly to the code page of the default collation for the current database.
2) While probably not a problem in most cases, most identifiers do allow for up to 128 characters (well, 256 bytes worth of stuff), so 100 could lead to silent truncation.
3) Single quotes in any identifier will cause an error.
4) TEXT and NTEXT columns are excluded.
5) QUOTENAME() isn’t being used on the column name (on either side of the “=”), so any non-regular identifier will cause an error.
6) For Unicode columns, depending on the collation being used for a column and the characters in that column, if you don’t ensure a version 100 (preferably version 140, but those started in SQL Server 2017) collation, then there is potential for some characters to not uppercase. The problem is that certain uppercase and lowercase mappings are missing, especially from the version 80 and 90 collations.
Now, I did take your advice and wrote about this, even getting it to be one update per table. I experimented with using CTEs instead of a cursor. I think it holds up pretty well, though admittedly it does add a little complexity. I posted the whole thing here, including screenshots of the results to show the potential consequence of not ensuring that the highest version collation is used for the UPPER() operation:
Regarding cursors, you mention, “I’d worry about performance here if this ran at any scale, and more often than once in a very rare time.” One thing you can do to improve that is add the “STATIC” option for the cursor. This copies the results of the query used by the cursor into a temp table, eliminating locks on the tables in the query, and removing any need for the cursor to rerun the query to ensure that the rows it initially found still exist. This also means that there is no need to use the “STATIC” option if the query is against temp tables or table variables. I also add 3 other options, so my cursors look like:
DECLARE updatecurs CURSOR STATIC LOCAL READ_ONLY FORWARD_ONLY FOR
Finally, I never knew that you could DEALLOCATE a cursor without first calling CLOSE. Learned something new 🙂
Take care, Solomon…