SqlQuantumLeap

T-SQL Script to Uppercase All Columns

Mar 20th, 2019
167
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

Adblocker detected! Please consider disabling it...

We've detected AdBlock Plus or some other adblocking software preventing Pastebin.com from fully loading.

We don't have any obnoxious sound, or popup ads, we actively block these annoying types of ads!

Please add Pastebin.com to your ad blocker whitelist or disable your adblocking software.

×