Category: Blog

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.

SQLNewBlogger

Quick, effective code. This has caveats, but works.

If you want to write about this, change the cursor to make one update per table.