Find and Change (N)VARCHAR(MAX) to (N)VARCHAR(XXX)

I know, I know, everything I do is rushed and not properly explained, apologies for that. Anyway.
During some investigation, I came across some badly designed tables where most of the columns meant to hold character data were NVARCHAR(MAX), all this while the maximum length of the strings were something in the area of 30-50 characters. It has been widely documented why this is a bad idea in terms of performance, so I will only put a script in here that will attempt to categorize them from 1 to 255, 255 to 512, 512 to 1024, etc.

			
SET NOCOUNT ON;
IF OBJECT_ID('tempdb..#MaxVarChar') IS NOT NULL
    DROP TABLE #MaxVarChar;
CREATE TABLE #MaxVarChar
(
    SchemaName NVARCHAR(128) COLLATE DATABASE_DEFAULT,
    TableName  NVARCHAR(128) COLLATE DATABASE_DEFAULT,
    ColumnName NVARCHAR(128) COLLATE DATABASE_DEFAULT,
    TypeName   SYSNAME COLLATE DATABASE_DEFAULT,
    MaxLen     INT NOT NULL
);
DECLARE @sql NVARCHAR(MAX) = N'';
;WITH VarMaxCols AS
(
    SELECT
        s.name  AS SchemaName,
        t.name  AS TableName,
        c.name  AS ColumnName,
        ty.name AS TypeName
    FROM sys.columns c
    JOIN sys.types ty
        ON c.user_type_id = ty.user_type_id
    JOIN sys.tables t
        ON c.object_id = t.object_id
    JOIN sys.schemas s
        ON t.schema_id = s.schema_id
    WHERE ty.name IN (N'varchar', N'nvarchar')
      AND c.max_length = -1
      AND t.is_ms_shipped = 0
)
SELECT
    @sql = @sql + N'
INSERT INTO #MaxVarChar
(
    SchemaName,
    TableName,
    ColumnName,
    TypeName,
    MaxLen
)
SELECT
    ' + QUOTENAME(SchemaName, '''') + N',
    ' + QUOTENAME(TableName, '''') + N',
    ' + QUOTENAME(ColumnName, '''') + N',
    ' + QUOTENAME(TypeName, '''') + N',
    ISNULL(
        MAX(
            ISNULL(' +
                CASE
                    WHEN TypeName = N'nvarchar' THEN
                        N'DATALENGTH(' + QUOTENAME(ColumnName) + N') / 2'
                    ELSE
                        N'DATALENGTH(' + QUOTENAME(ColumnName) + N')'
                END
            + N', 0)
        ),
        0
    )
FROM ' + QUOTENAME(SchemaName) + N'.' + QUOTENAME(TableName) + N';'
FROM VarMaxCols
ORDER BY
    SchemaName,
    TableName,
    ColumnName;
IF LEN(@sql) > 0
    EXEC sys.sp_executesql @sql;
;WITH VarMaxCols AS
(
    SELECT
        s.name  AS SchemaName,
        t.name  AS TableName,
        c.name  AS ColumnName,
        ty.name AS TypeName,
        c.is_nullable,
        c.collation_name
    FROM sys.columns c
    JOIN sys.types ty
        ON c.user_type_id = ty.user_type_id
    JOIN sys.tables t
        ON c.object_id = t.object_id
    JOIN sys.schemas s
        ON t.schema_id = s.schema_id
    WHERE ty.name IN (N'varchar', N'nvarchar')
      AND c.max_length = -1
      AND t.is_ms_shipped = 0
)
SELECT
    N'ALTER TABLE '
    + QUOTENAME(vc.SchemaName)
    + N'.'
    + QUOTENAME(vc.TableName)
    + N' ALTER COLUMN '
    + QUOTENAME(vc.ColumnName)
    + N' '
    + UPPER(vc.TypeName)
    + N'('
    + CASE
          WHEN vc.TypeName = N'nvarchar' THEN
              CASE
                  WHEN m.MaxLen <= 255  THEN N'255'
                  WHEN m.MaxLen <= 512  THEN N'512'
                  WHEN m.MaxLen <= 1024 THEN N'1024'
                  WHEN m.MaxLen <= 2048 THEN N'2048'
                  ELSE N'4000'
              END
          WHEN vc.TypeName = N'varchar' THEN
              CASE
                  WHEN m.MaxLen <= 255  THEN N'255'
                  WHEN m.MaxLen <= 512  THEN N'512'
                  WHEN m.MaxLen <= 1024 THEN N'1024'
                  WHEN m.MaxLen <= 2048 THEN N'2048'
                  WHEN m.MaxLen <= 4000 THEN N'4000'
                  ELSE N'8000'
              END
      END
    + N')'
    + CASE
          WHEN vc.collation_name IS NOT NULL THEN
              N' COLLATE ' + vc.collation_name
          ELSE
              N''
      END
    + N' '
    + CASE
          WHEN vc.is_nullable = 1 THEN N'NULL'
          ELSE N'NOT NULL'
      END
    + N';' AS AlterStatement,
    m.MaxLen
FROM VarMaxCols vc
JOIN #MaxVarChar m
    ON m.SchemaName = vc.SchemaName COLLATE DATABASE_DEFAULT
   AND m.TableName  = vc.TableName  COLLATE DATABASE_DEFAULT
   AND m.ColumnName = vc.ColumnName COLLATE DATABASE_DEFAULT
   AND m.TypeName   = vc.TypeName   COLLATE DATABASE_DEFAULT
WHERE
    (
        vc.TypeName = N'nvarchar'
        AND m.MaxLen <= 4000
    )
    OR
    (
        vc.TypeName = N'varchar'
        AND m.MaxLen <= 8000
    )
ORDER BY
    vc.SchemaName,
    vc.TableName,
    vc.ColumnName;

		

It basically checks the base tables for columns that have VARCHAR(MAX) or NVARCHAR(MAX) as definition and then it checks for the maximum length of the strings you have stored. Just make sure you set Results To Text (CTRL + T) and not grid and you should have the scripts generated for you.
Of course, you must remember that the ALTER TABLE ... ALTER COLUMN will fail if the column is part of an index, constraint, etc.
You also must take into account that if you have a ton of columns that qualify, a ton of ALTER statements will be generated, which is not ideal, to put it mildly.

One caveat here: my original script transformed all the columns into NVARCHAR(XXX) so I could align them with the rest of the project. I ran it through ChatGPT and it modified it to leave the columns in their original data type (VARCHAR or NVARCHAR). So make sure you test it before even thinking to use it on prod databases.
Just remember some of your columns might have data that already goes beyond the limits you want to set, so they will not be found in the final set of ALTER statements.
DO NOT DO ANY CHANGES UNTIL YOU TALK TO YOUR DEV TEAM!

Find and Change (N)VARCHAR(MAX) to (N)VARCHAR(XXX)

Published by Calin OPREA

Leave a comment Cancel reply

Share this:

Published by Calin OPREA

Leave a comment Cancel reply