T-SQL Script to Uppercase All Columns

/*
 ------- Uppercase All String Columns (and in a Single Query!) -------

Created By: Solomon Rutzky / Sql Quantum Leap
Created On: 2019-03-20
Updated On: n/a

Location:          https://pastebin.com/vbzz0LqN
Related blog post: https://sqlquantumleap.com/2019/03/20/uppercase-all-characters-and-in-a-single-query/
----------------------------------------------------------------------
*/


USE [tempdb];
SET NOCOUNT ON;


IF (OBJECT_ID(N'dbo.One''Column') IS NULL)
BEGIN
  CREATE TABLE dbo.[One'Column]
  (
    [One'ColumnID] INT NOT NULL IDENTITY(1, 1)
      CONSTRAINT [PK_One'Column] PRIMARY KEY,
    [String] VARCHAR(100) COLLATE SQL_Latin1_General_CP1_CI_AS,
    [SomeDate] DATETIME
  );

  CREATE TABLE dbo.TwoColumns
  (
    [TwoColumnsID] INT NOT NULL IDENTITY(1, 1)
      CONSTRAINT [PK_TwoColumns] PRIMARY KEY,
    [Stringy]]] NCHAR(50) COLLATE SQL_Latin1_General_CP1_CI_AS,
    [SomeDate] DATETIME,
    [LOBy] TEXT COLLATE SQL_Latin1_General_CP1_CI_AS
  );

  CREATE TABLE dbo.NoColumns
  (
    [NoColumnsID] INT NOT NULL IDENTITY(1, 1)
      CONSTRAINT [PK_NoColumns] PRIMARY KEY,
    [SomeDate] DATETIME,
    [SomeMoney] MONEY
  );

  CREATE TABLE dbo.[Another]]One'Column]
  (
    [Another]]One'ColumnID] INT NOT NULL IDENTITY(1, 1)
      CONSTRAINT [PK_Another]]One'Column] PRIMARY KEY,
    [Stringy] CHAR(100) COLLATE Latin1_General_100_CI_AS,
    [SomeDate] DATETIME,

  );

  CREATE TABLE dbo.ThreeColumns
  (
    [ThreeColumnsID] INT NOT NULL IDENTITY(1, 1)
      CONSTRAINT [PK_ThreeColumns] PRIMARY KEY,
    ['FarCar] VARCHAR(100) COLLATE Latin1_General_CI_AS,
    [SomeDate] DATETIME,
    [InTharKahr] NVARCHAR(50) COLLATE Latin1_General_100_CI_AS,
    [💣EnTexxed💥] NTEXT COLLATE Latin1_General_CI_AS
  );

  CREATE TABLE dbo.[Yet🙀Another]]One'Column]
  (
    [Yet🙀Another]]One'ColumnID] INT NOT NULL IDENTITY(1, 1)
      CONSTRAINT [PK_Yet🙀Another]]One'Column] PRIMARY KEY,
    [Stringy] NVARCHAR(50) COLLATE Syriac_90_CI_AS_SC,
    [SomeDate] DATETIME,

  );

  CREATE TABLE dbo.[DatabaseCollation]
  (
    [DatabaseCollationID] INT NOT NULL IDENTITY(1, 1)
      CONSTRAINT [PK_DatabaseCollation] PRIMARY KEY,
    [VarChar] VARCHAR(100),
    [NVarChar] NVARCHAR(50),
    [SomeDate] DATETIME,

  );


  DECLARE @TestValue NVARCHAR(100) = NCHAR(0xE0) + NCHAR(0xE1) + NCHAR(0xE2) + NCHAR(0xE3) + NCHAR(0xE4) +
                       NCHAR(0xE4) + NCHAR(0xE5) + NCHAR(0xE6) + NCHAR(0xE7) + NCHAR(0xE8) + NCHAR(0xE9) +
                       NCHAR(0xEA) + NCHAR(0xEB) + NCHAR(0xEC) + NCHAR(0xED) + NCHAR(0xEE) + NCHAR(0xEF) +
                       NCHAR(0xF1) + NCHAR(0xF2) + NCHAR(0xF3) + NCHAR(0xF4) + NCHAR(0xF5) + NCHAR(0xF6) +
                       NCHAR(0xF8) + NCHAR(0xF9) + NCHAR(0xFA) + NCHAR(0xFB) + NCHAR(0xFC) + NCHAR(0xFD) +
                       NCHAR(0xFE) + N' ' + NCHAR(0x01F9) + N' ' + NCHAR(0x0219) + N' ' + NCHAR(0x03E1) +
                       N' ' + NCHAR(0x1F90);

  SELECT @TestValue AS [NVarCharValue],
         DATALENGTH(@TestValue) AS [NVarCharBytes],
         CONVERT(VARCHAR(100), @TestValue) AS [VarCharValue],
         DATALENGTH(CONVERT(VARCHAR(100), @TestValue)) AS [VarCharBytes];


  INSERT INTO dbo.[One'Column] ([SomeDate], [String])
    VALUES (GETDATE(), @TestValue); -- VC (SQL)
  INSERT INTO dbo.TwoColumns ([Stringy]]], [SomeDate], [LOBy])
    VALUES (@TestValue, GETDATE(), @TestValue); -- NC (SQL), TX (SQL)
  INSERT INTO dbo.NoColumns ([SomeDate], [SomeMoney])
    VALUES (GETDATE(), $123.45);
  INSERT INTO dbo.[Another]]One'Column] ([Stringy], [SomeDate])
    VALUES (@TestValue, GETDATE()); -- C (100)
  INSERT INTO dbo.ThreeColumns (['FarCar], [SomeDate], [InTharKahr], [💣EnTexxed💥])
    VALUES (@TestValue, GETDATE(), @TestValue, @TestValue); -- VC (80), NVC (100), NTX (80)
  INSERT INTO dbo.[Yet🙀Another]]One'Column] ([Stringy], [SomeDate])
    VALUES (@TestValue, GETDATE()); -- NVC (90)
  INSERT INTO dbo.[DatabaseCollation] ([VarChar], [NVarChar], [SomeDate])
    VALUES (@TestValue, @TestValue, GETDATE()); -- VC (DATABASE_DEFAULT), NVC (DATABASE_DEFAULT)

END;


SELECT 'BEFORE' AS [WHEN], @@TRANCOUNT AS [@@TRANCOUNT];
SELECT * FROM dbo.[One'Column];
SELECT * FROM dbo.TwoColumns;
SELECT * FROM dbo.NoColumns;
SELECT * FROM dbo.[Another]]One'Column];
SELECT * FROM dbo.ThreeColumns;
SELECT * FROM dbo.[Yet🙀Another]]One'Column]
SELECT * FROM dbo.[DatabaseCollation];

-------------------------------------------------------------------------------
--===========================================================================--
-------------------------------------------------------------------------------
DECLARE @TablesPerTransaction TINYINT = 2,
        @FixNVarCharCollation BIT = 1;

DECLARE @SQL NVARCHAR(MAX) = N'',
        @CurrentTableName NVARCHAR(MAX) = N'',
        @PreviousTableName NVARCHAR(MAX) = N'',
        @FullScript NVARCHAR(MAX) = N'';

;WITH pieces AS
(
  SELECT QUOTENAME(sch.[name]) AS [SchemaName],
         QUOTENAME(obj.[name]) AS [TableName],
         QUOTENAME(col.[name]) AS [ColumnName],
         typ.[name] AS [DataType],
         IIF(    COLLATIONPROPERTY(col.[collation_name], 'version') < 2
             AND (   typ.[name] IN (N'nchar', N'nvarchar', N'ntext')
                  OR col.[collation_name] LIKE N'%UTF8%'),
             1, 0) AS [CollationNeedsFixin],
         ROW_NUMBER() OVER (PARTITION BY sch.[name], obj.[name]
                            ORDER BY sch.[name], obj.[name], col.[name]) AS [ColumnNum],
         DENSE_RANK() OVER (ORDER BY sch.[name], obj.[name]) AS [ObjectNum]
  FROM   sys.objects obj
  INNER JOIN sys.schemas sch
          ON sch.[schema_id] = obj.[schema_id]
  INNER JOIN sys.columns col
          ON col.[object_id] = obj.[object_id]
  INNER JOIN sys.types typ
          ON typ.[user_type_id] = col.[system_type_id]
  WHERE  obj.[is_ms_shipped] = 0
  AND    obj.[type] = N'U'
  AND    typ.[name] IN (N'char', N'varchar', N'text', N'nchar', N'nvarchar', N'ntext')
  --ORDER BY sch.[name], obj.[name], col.[name]
), core AS
(
  SELECT NCHAR(0x000D) + NCHAR(0x000A) AS [CRLF],
         NCHAR(0x0009) AS [TAB],
         NCHAR(0x0027) AS [APOS], -- single-quote
         NCHAR(0x0027) + NCHAR(0x0027) AS [APOSx2], -- double single-quote
         CONVERT(NVARCHAR(128), SERVERPROPERTY('ProductVersion')) AS [ProdVer]
), util AS
(
  SELECT core.[CRLF], -- pass-through
         core.[TAB], -- pass-through
         core.[APOS], -- pass-through
         core.[APOSx2], -- pass-through
         CONVERT(INT, SUBSTRING(core.[ProdVer], 1, CHARINDEX(N'.', core.[ProdVer]) - 1)) AS [Version],
         N'DECLARE @ErrorMessage NVARCHAR(MAX),' + core.[CRLF]
           + N'        @CurrentTime NVARCHAR(50);' + core.[CRLF] AS [ScriptStart],
         N'BEGIN TRAN;' + core.[CRLF] AS [TranStart],
         N'BEGIN TRY' + core.[CRLF]
           + core.[TAB] + N'SET @CurrentTime = CONVERT(VARCHAR(50), SYSDATETIME(), 121);' + core.[CRLF]
           + core.[TAB] + N'RAISERROR(N''%s -- %s ...'', 10, 1, @CurrentTime, N''{{TABLE_NAME}}'') WITH NOWAIT;'
           + core.[CRLF] AS [TryCatchStart],
         core.[TAB] + N'UPDATE {{TABLE_NAME}} SET' + core.[CRLF] AS [TableStart],
         N',' + core.[CRLF] AS [ColumnEnd],
         N';' + core.[CRLF] AS [TableEnd],
         N'END TRY' + core.[CRLF]
           + N'BEGIN CATCH' + core.[CRLF]
           + core.[TAB] + N'IF (@@TRANCOUNT > 0) ROLLBACK TRAN;' + core.[CRLF]
           + core.[TAB] + N'SET @ErrorMessage = N''( {{TABLE_NAME}} ): '' + ERROR_MESSAGE();' + core.[CRLF]
           + core.[TAB] + N'RAISERROR(@ErrorMessage, 16, 1);' + core.[CRLF]
           + core.[TAB] + N'RETURN;' + core.[CRLF]
           + N'END CATCH;' + core.[CRLF] + core.[CRLF] AS [TryCatchEnd],
         N'IF (@@TRANCOUNT > 0) COMMIT TRAN;' + core.[CRLF] AS [TranEnd]
  FROM   core
)
SELECT -- { store value to use multiple times in remaining variable concatenations }
       @CurrentTableName = p.[SchemaName] + N'.' + p.[TableName],

       -- { main script generation }
       @SQL += -- { end previous line }
               IIF(DATALENGTH(@SQL) = 0,
                   u.[ScriptStart] + u.[CRLF] -- no previous line
                     + IIF((@TablesPerTransaction > 1) AND (p.[ObjectNum] % @TablesPerTransaction = 1),
                           N'',
                           N'--') + u.[TranStart] + u.[CRLF],
                   IIF(p.[ColumnNum] = 1, -- previous line was final column of a table
                       u.[TableEnd]
                         + REPLACE(u.[TryCatchEnd], N'{{TABLE_NAME}}', @PreviousTableName)
                         + IIF((@TablesPerTransaction > 1) AND (p.[ObjectNum] % @TablesPerTransaction = 1),
                               u.[TranEnd] + u.[TranStart],
                               N'--' + u.[TranEnd] + N'--' + u.[TranStart]) + u.[CRLF],
                       u.[ColumnEnd]))
               -- { start new table }
             + IIF(p.[ColumnNum] = 1,
                   REPLACE(u.[TryCatchStart], N'{{TABLE_NAME}}', REPLACE(@CurrentTableName, u.[APOS], u.[APOSx2]))
                     + REPLACE(u.[TableStart], N'{{TABLE_NAME}}', @CurrentTableName),
                   N'')
               -- { start column }
             + u.[TAB] + u.[TAB] + p.[ColumnName] + N' = UPPER('
               -- { TEXT and NTEXT aren't valid for UPPER() ; UTF8 needs special handling }
             + CASE
                 WHEN (p.[DataType] IN (N'char', N'varchar')) AND (p.[CollationNeedsFixin] = 1)
                   THEN N'CONVERT(NVARCHAR(MAX), ' + p.[ColumnName] + N')'
                 WHEN p.[DataType] = N'text' THEN N'CONVERT(VARCHAR(MAX), ' + p.[ColumnName] + N')'
                 WHEN p.[DataType] = N'ntext' THEN N'CONVERT(NVARCHAR(MAX), ' + p.[ColumnName] + N')'
                 ELSE p.[ColumnName]
               END
               -- { For SQL Server 2008 or newer, NVARCHAR data must use a version 100 (or newer) collation }
             + IIF((@FixNVarCharCollation = 1) AND (p.[CollationNeedsFixin] = 1),
                   N' COLLATE '
                     + CASE
                         WHEN u.[Version] = 9 THEN N'Latin1_General_BIN2' -- 2005
                         WHEN u.[Version] < 14 THEN N'Latin1_General_100_BIN2' -- 2008, 2008 R2, 2012, 2014, 2016
                         ELSE N'Japanese_XJIS_140_BIN2' -- 2017 and newer
                       END,
                   N'')
               -- { finish column }
             + N')',

       -- { store value to use at beginning of next row }
       @PreviousTableName = REPLACE(@CurrentTableName, u.[APOS], u.[APOSx2]), -- store TableName for CATCH BLOCK

       -- { store value to use after query finishes }
       @FullScript = @SQL + u.[TableEnd]
                    + REPLACE(u.[TryCatchEnd], N'{{TABLE_NAME}}', @PreviousTableName)
                    + u.[TranEnd]
                    + u.[CRLF] + u.[CRLF]
FROM   pieces p
CROSS JOIN util u
ORDER BY p.[ObjectNum], p.[ColumnNum];



PRINT '-----------------------';
--PRINT @PreviousTableName;
--PRINT @SQL;

PRINT @FullScript; -- DEBUG (this is usually commented-out)
PRINT '-----------------------';

EXEC (@FullScript);
-------------------------------------------------------------------------------
--===========================================================================--
-------------------------------------------------------------------------------

SELECT 'AFTER' AS [WHEN], @@TRANCOUNT AS [@@TRANCOUNT];
SELECT * FROM dbo.[One'Column];
SELECT * FROM dbo.TwoColumns;
SELECT * FROM dbo.NoColumns;
SELECT * FROM dbo.[Another]]One'Column];
SELECT * FROM dbo.ThreeColumns;
SELECT * FROM dbo.[Yet🙀Another]]One'Column]
SELECT * FROM dbo.[DatabaseCollation];
GO


IF (OBJECT_ID(N'dbo.One''Column') IS NOT NULL)
BEGIN
  RAISERROR('Deleting test tables...', 10, 1) WITH NOWAIT;
  DROP TABLE dbo.[One'Column];
  DROP TABLE dbo.TwoColumns;
  DROP TABLE dbo.NoColumns;
  DROP TABLE dbo.[Another]]One'Column];
  DROP TABLE dbo.ThreeColumns;
  DROP TABLE dbo.[Yet🙀Another]]One'Column];
  DROP TABLE dbo.[DatabaseCollation];
END;

GO
----------------------------------------------------------------------------------------------------------