SHARE
TWEET

T-SQL Script to Uppercase All Columns

SqlQuantumLeap Mar 20th, 2019 (edited) 67 Never
Not a member of Pastebin yet? Sign Up, it unlocks many cool features!
  1. /*
  2.  ------- Uppercase All String Columns (and in a Single Query!) -------
  3.  
  4. Created By: Solomon Rutzky / Sql Quantum Leap
  5. Created On: 2019-03-20
  6. Updated On: n/a
  7.  
  8. Location:          https://pastebin.com/vbzz0LqN
  9. Related blog post: https://sqlquantumleap.com/2019/03/20/uppercase-all-characters-and-in-a-single-query/
  10. ----------------------------------------------------------------------
  11. */
  12.  
  13.  
  14. USE [tempdb];
  15. SET NOCOUNT ON;
  16.  
  17.  
  18. IF (OBJECT_ID(N'dbo.One''Column') IS NULL)
  19. BEGIN
  20.   CREATE TABLE dbo.[One'Column]
  21.  (
  22.    [One'ColumnID] INT NOT NULL IDENTITY(1, 1)
  23.       CONSTRAINT [PK_One'Column] PRIMARY KEY,
  24.    [String] VARCHAR(100) COLLATE SQL_Latin1_General_CP1_CI_AS,
  25.    [SomeDate] DATETIME
  26.  );
  27.  
  28.  CREATE TABLE dbo.TwoColumns
  29.  (
  30.    [TwoColumnsID] INT NOT NULL IDENTITY(1, 1)
  31.      CONSTRAINT [PK_TwoColumns] PRIMARY KEY,
  32.    [Stringy]]] NCHAR(50) COLLATE SQL_Latin1_General_CP1_CI_AS,
  33.    [SomeDate] DATETIME,
  34.    [LOBy] TEXT COLLATE SQL_Latin1_General_CP1_CI_AS
  35.  );
  36.  
  37.  CREATE TABLE dbo.NoColumns
  38.  (
  39.    [NoColumnsID] INT NOT NULL IDENTITY(1, 1)
  40.      CONSTRAINT [PK_NoColumns] PRIMARY KEY,
  41.    [SomeDate] DATETIME,
  42.    [SomeMoney] MONEY
  43.  );
  44.  
  45.  CREATE TABLE dbo.[Another]]One'Column]
  46.   (
  47.     [Another]]One'ColumnID] INT NOT NULL IDENTITY(1, 1)
  48.      CONSTRAINT [PK_Another]]One'Column] PRIMARY KEY,
  49.     [Stringy] CHAR(100) COLLATE Latin1_General_100_CI_AS,
  50.     [SomeDate] DATETIME,
  51.  
  52.   );
  53.  
  54.   CREATE TABLE dbo.ThreeColumns
  55.   (
  56.     [ThreeColumnsID] INT NOT NULL IDENTITY(1, 1)
  57.       CONSTRAINT [PK_ThreeColumns] PRIMARY KEY,
  58.     ['FarCar] VARCHAR(100) COLLATE Latin1_General_CI_AS,
  59.    [SomeDate] DATETIME,
  60.     [InTharKahr] NVARCHAR(50) COLLATE Latin1_General_100_CI_AS,
  61.    [💣EnTexxed💥] NTEXT COLLATE Latin1_General_CI_AS
  62.  );
  63.  
  64.  CREATE TABLE dbo.[Yet🙀Another]]One'Column]
  65.   (
  66.     [Yet🙀Another]]One'ColumnID] INT NOT NULL IDENTITY(1, 1)
  67.      CONSTRAINT [PK_Yet🙀Another]]One'Column] PRIMARY KEY,
  68.     [Stringy] NVARCHAR(50) COLLATE Syriac_90_CI_AS_SC,
  69.     [SomeDate] DATETIME,
  70.  
  71.   );
  72.  
  73.   CREATE TABLE dbo.[DatabaseCollation]
  74.   (
  75.     [DatabaseCollationID] INT NOT NULL IDENTITY(1, 1)
  76.       CONSTRAINT [PK_DatabaseCollation] PRIMARY KEY,
  77.     [VarChar] VARCHAR(100),
  78.     [NVarChar] NVARCHAR(50),
  79.     [SomeDate] DATETIME,
  80.  
  81.   );
  82.  
  83.  
  84.   DECLARE @TestValue NVARCHAR(100) = NCHAR(0xE0) + NCHAR(0xE1) + NCHAR(0xE2) + NCHAR(0xE3) + NCHAR(0xE4) +
  85.                        NCHAR(0xE4) + NCHAR(0xE5) + NCHAR(0xE6) + NCHAR(0xE7) + NCHAR(0xE8) + NCHAR(0xE9) +
  86.                        NCHAR(0xEA) + NCHAR(0xEB) + NCHAR(0xEC) + NCHAR(0xED) + NCHAR(0xEE) + NCHAR(0xEF) +
  87.                        NCHAR(0xF1) + NCHAR(0xF2) + NCHAR(0xF3) + NCHAR(0xF4) + NCHAR(0xF5) + NCHAR(0xF6) +
  88.                        NCHAR(0xF8) + NCHAR(0xF9) + NCHAR(0xFA) + NCHAR(0xFB) + NCHAR(0xFC) + NCHAR(0xFD) +
  89.                        NCHAR(0xFE) + N' ' + NCHAR(0x01F9) + N' ' + NCHAR(0x0219) + N' ' + NCHAR(0x03E1) +
  90.                        N' ' + NCHAR(0x1F90);
  91.  
  92.   SELECT @TestValue AS [NVarCharValue],
  93.          DATALENGTH(@TestValue) AS [NVarCharBytes],
  94.          CONVERT(VARCHAR(100), @TestValue) AS [VarCharValue],
  95.          DATALENGTH(CONVERT(VARCHAR(100), @TestValue)) AS [VarCharBytes];
  96.  
  97.  
  98.   INSERT INTO dbo.[One'Column] ([SomeDate], [String])
  99.    VALUES (GETDATE(), @TestValue); -- VC (SQL)
  100.  INSERT INTO dbo.TwoColumns ([Stringy]]], [SomeDate], [LOBy])
  101.    VALUES (@TestValue, GETDATE(), @TestValue); -- NC (SQL), TX (SQL)
  102.  INSERT INTO dbo.NoColumns ([SomeDate], [SomeMoney])
  103.    VALUES (GETDATE(), $123.45);
  104.  INSERT INTO dbo.[Another]]One'Column] ([Stringy], [SomeDate])
  105.     VALUES (@TestValue, GETDATE()); -- C (100)
  106.   INSERT INTO dbo.ThreeColumns (['FarCar], [SomeDate], [InTharKahr], [💣EnTexxed💥])
  107.    VALUES (@TestValue, GETDATE(), @TestValue, @TestValue); -- VC (80), NVC (100), NTX (80)
  108.  INSERT INTO dbo.[Yet🙀Another]]One'Column] ([Stringy], [SomeDate])
  109.     VALUES (@TestValue, GETDATE()); -- NVC (90)
  110.   INSERT INTO dbo.[DatabaseCollation] ([VarChar], [NVarChar], [SomeDate])
  111.     VALUES (@TestValue, @TestValue, GETDATE()); -- VC (DATABASE_DEFAULT), NVC (DATABASE_DEFAULT)
  112.  
  113. END;
  114.  
  115.  
  116. SELECT 'BEFORE' AS [WHEN], @@TRANCOUNT AS [@@TRANCOUNT];
  117. SELECT * FROM dbo.[One'Column];
  118. SELECT * FROM dbo.TwoColumns;
  119. SELECT * FROM dbo.NoColumns;
  120. SELECT * FROM dbo.[Another]]One'Column];
  121. SELECT * FROM dbo.ThreeColumns;
  122. SELECT * FROM dbo.[Yet🙀Another]]One'Column]
  123. SELECT * FROM dbo.[DatabaseCollation];
  124.  
  125. -------------------------------------------------------------------------------
  126. --===========================================================================--
  127. -------------------------------------------------------------------------------
  128. DECLARE @TablesPerTransaction TINYINT = 2,
  129.        @FixNVarCharCollation BIT = 1;
  130.  
  131. DECLARE @SQL NVARCHAR(MAX) = N'',
  132.        @CurrentTableName NVARCHAR(MAX) = N'',
  133.        @PreviousTableName NVARCHAR(MAX) = N'',
  134.        @FullScript NVARCHAR(MAX) = N'';
  135.  
  136. ;WITH pieces AS
  137. (
  138.  SELECT QUOTENAME(sch.[name]) AS [SchemaName],
  139.         QUOTENAME(obj.[name]) AS [TableName],
  140.         QUOTENAME(col.[name]) AS [ColumnName],
  141.         typ.[name] AS [DataType],
  142.         IIF(    COLLATIONPROPERTY(col.[collation_name], 'version') < 2
  143.             AND (   typ.[name] IN (N'nchar', N'nvarchar', N'ntext')
  144.                  OR col.[collation_name] LIKE N'%UTF8%'),
  145.             1, 0) AS [CollationNeedsFixin],
  146.         ROW_NUMBER() OVER (PARTITION BY sch.[name], obj.[name]
  147.                            ORDER BY sch.[name], obj.[name], col.[name]) AS [ColumnNum],
  148.         DENSE_RANK() OVER (ORDER BY sch.[name], obj.[name]) AS [ObjectNum]
  149.  FROM   sys.objects obj
  150.  INNER JOIN sys.schemas sch
  151.          ON sch.[schema_id] = obj.[schema_id]
  152.  INNER JOIN sys.columns col
  153.          ON col.[object_id] = obj.[object_id]
  154.  INNER JOIN sys.types typ
  155.          ON typ.[user_type_id] = col.[system_type_id]
  156.  WHERE  obj.[is_ms_shipped] = 0
  157.  AND    obj.[type] = N'U'
  158.  AND    typ.[name] IN (N'char', N'varchar', N'text', N'nchar', N'nvarchar', N'ntext')
  159.  --ORDER BY sch.[name], obj.[name], col.[name]
  160. ), core AS
  161. (
  162.  SELECT NCHAR(0x000D) + NCHAR(0x000A) AS [CRLF],
  163.         NCHAR(0x0009) AS [TAB],
  164.         NCHAR(0x0027) AS [APOS], -- single-quote
  165.         NCHAR(0x0027) + NCHAR(0x0027) AS [APOSx2], -- double single-quote
  166.         CONVERT(NVARCHAR(128), SERVERPROPERTY('ProductVersion')) AS [ProdVer]
  167. ), util AS
  168. (
  169.  SELECT core.[CRLF], -- pass-through
  170.         core.[TAB], -- pass-through
  171.         core.[APOS], -- pass-through
  172.         core.[APOSx2], -- pass-through
  173.         CONVERT(INT, SUBSTRING(core.[ProdVer], 1, CHARINDEX(N'.', core.[ProdVer]) - 1)) AS [Version],
  174.         N'DECLARE @ErrorMessage NVARCHAR(MAX),' + core.[CRLF]
  175.           + N'        @CurrentTime NVARCHAR(50);' + core.[CRLF] AS [ScriptStart],
  176.         N'BEGIN TRAN;' + core.[CRLF] AS [TranStart],
  177.         N'BEGIN TRY' + core.[CRLF]
  178.           + core.[TAB] + N'SET @CurrentTime = CONVERT(VARCHAR(50), SYSDATETIME(), 121);' + core.[CRLF]
  179.           + core.[TAB] + N'RAISERROR(N''%s -- %s ...'', 10, 1, @CurrentTime, N''{{TABLE_NAME}}'') WITH NOWAIT;'
  180.            + core.[CRLF] AS [TryCatchStart],
  181.          core.[TAB] + N'UPDATE {{TABLE_NAME}} SET' + core.[CRLF] AS [TableStart],
  182.          N',' + core.[CRLF] AS [ColumnEnd],
  183.          N';' + core.[CRLF] AS [TableEnd],
  184.          N'END TRY' + core.[CRLF]
  185.            + N'BEGIN CATCH' + core.[CRLF]
  186.            + core.[TAB] + N'IF (@@TRANCOUNT > 0) ROLLBACK TRAN;' + core.[CRLF]
  187.            + core.[TAB] + N'SET @ErrorMessage = N''( {{TABLE_NAME}} ): '' + ERROR_MESSAGE();' + core.[CRLF]
  188.            + core.[TAB] + N'RAISERROR(@ErrorMessage, 16, 1);' + core.[CRLF]
  189.            + core.[TAB] + N'RETURN;' + core.[CRLF]
  190.            + N'END CATCH;' + core.[CRLF] + core.[CRLF] AS [TryCatchEnd],
  191.          N'IF (@@TRANCOUNT > 0) COMMIT TRAN;' + core.[CRLF] AS [TranEnd]
  192.   FROM   core
  193. )
  194. SELECT -- { store value to use multiple times in remaining variable concatenations }
  195.        @CurrentTableName = p.[SchemaName] + N'.' + p.[TableName],
  196.  
  197.        -- { main script generation }
  198.        @SQL += -- { end previous line }
  199.                IIF(DATALENGTH(@SQL) = 0,
  200.                    u.[ScriptStart] + u.[CRLF] -- no previous line
  201.                      + IIF((@TablesPerTransaction > 1) AND (p.[ObjectNum] % @TablesPerTransaction = 1),
  202.                            N'',
  203.                            N'--') + u.[TranStart] + u.[CRLF],
  204.                    IIF(p.[ColumnNum] = 1, -- previous line was final column of a table
  205.                        u.[TableEnd]
  206.                          + REPLACE(u.[TryCatchEnd], N'{{TABLE_NAME}}', @PreviousTableName)
  207.                          + IIF((@TablesPerTransaction > 1) AND (p.[ObjectNum] % @TablesPerTransaction = 1),
  208.                                u.[TranEnd] + u.[TranStart],
  209.                                N'--' + u.[TranEnd] + N'--' + u.[TranStart]) + u.[CRLF],
  210.                        u.[ColumnEnd]))
  211.                -- { start new table }
  212.              + IIF(p.[ColumnNum] = 1,
  213.                    REPLACE(u.[TryCatchStart], N'{{TABLE_NAME}}', REPLACE(@CurrentTableName, u.[APOS], u.[APOSx2]))
  214.                      + REPLACE(u.[TableStart], N'{{TABLE_NAME}}', @CurrentTableName),
  215.                    N'')
  216.                -- { start column }
  217.              + u.[TAB] + u.[TAB] + p.[ColumnName] + N' = UPPER('
  218.                -- { TEXT and NTEXT aren't valid for UPPER() ; UTF8 needs special handling }
  219.              + CASE
  220.                  WHEN (p.[DataType] IN (N'char', N'varchar')) AND (p.[CollationNeedsFixin] = 1)
  221.                    THEN N'CONVERT(NVARCHAR(MAX), ' + p.[ColumnName] + N')'
  222.                  WHEN p.[DataType] = N'text' THEN N'CONVERT(VARCHAR(MAX), ' + p.[ColumnName] + N')'
  223.                  WHEN p.[DataType] = N'ntext' THEN N'CONVERT(NVARCHAR(MAX), ' + p.[ColumnName] + N')'
  224.                  ELSE p.[ColumnName]
  225.                END
  226.                -- { For SQL Server 2008 or newer, NVARCHAR data must use a version 100 (or newer) collation }
  227.              + IIF((@FixNVarCharCollation = 1) AND (p.[CollationNeedsFixin] = 1),
  228.                    N' COLLATE '
  229.                      + CASE
  230.                          WHEN u.[Version] = 9 THEN N'Latin1_General_BIN2' -- 2005
  231.                          WHEN u.[Version] < 14 THEN N'Latin1_General_100_BIN2' -- 2008, 2008 R2, 2012, 2014, 2016
  232.                          ELSE N'Japanese_XJIS_140_BIN2' -- 2017 and newer
  233.                        END,
  234.                    N'')
  235.                -- { finish column }
  236.              + N')',
  237.  
  238.        -- { store value to use at beginning of next row }
  239.        @PreviousTableName = REPLACE(@CurrentTableName, u.[APOS], u.[APOSx2]), -- store TableName for CATCH BLOCK
  240.  
  241.        -- { store value to use after query finishes }
  242.        @FullScript = @SQL + u.[TableEnd]
  243.                     + REPLACE(u.[TryCatchEnd], N'{{TABLE_NAME}}', @PreviousTableName)
  244.                     + u.[TranEnd]
  245.                     + u.[CRLF] + u.[CRLF]
  246. FROM   pieces p
  247. CROSS JOIN util u
  248. ORDER BY p.[ObjectNum], p.[ColumnNum];
  249.  
  250.  
  251.  
  252. PRINT '-----------------------';
  253. --PRINT @PreviousTableName;
  254. --PRINT @SQL;
  255.  
  256. PRINT @FullScript; -- DEBUG (this is usually commented-out)
  257. PRINT '-----------------------';
  258.  
  259. EXEC (@FullScript);
  260. -------------------------------------------------------------------------------
  261. --===========================================================================--
  262. -------------------------------------------------------------------------------
  263.  
  264. SELECT 'AFTER' AS [WHEN], @@TRANCOUNT AS [@@TRANCOUNT];
  265. SELECT * FROM dbo.[One'Column];
  266. SELECT * FROM dbo.TwoColumns;
  267. SELECT * FROM dbo.NoColumns;
  268. SELECT * FROM dbo.[Another]]One'Column];
  269. SELECT * FROM dbo.ThreeColumns;
  270. SELECT * FROM dbo.[Yet🙀Another]]One'Column]
  271. SELECT * FROM dbo.[DatabaseCollation];
  272. GO
  273.  
  274.  
  275. IF (OBJECT_ID(N'dbo.One''Column') IS NOT NULL)
  276. BEGIN
  277.  RAISERROR('Deleting test tables...', 10, 1) WITH NOWAIT;
  278.  DROP TABLE dbo.[One'Column];
  279.   DROP TABLE dbo.TwoColumns;
  280.   DROP TABLE dbo.NoColumns;
  281.   DROP TABLE dbo.[Another]]One'Column];
  282.  DROP TABLE dbo.ThreeColumns;
  283.  DROP TABLE dbo.[Yet🙀Another]]One'Column];
  284.   DROP TABLE dbo.[DatabaseCollation];
  285. END;
  286.  
  287. GO
  288. ----------------------------------------------------------------------------------------------------------
RAW Paste Data
We use cookies for various purposes including analytics. By continuing to use Pastebin, you agree to our use of cookies as described in the Cookies Policy. OK, I Understand
Not a member of Pastebin yet?
Sign Up, it unlocks many cool features!
 
Top