Tuesday, September 1, 2015

Identify Unicode columns to convert to Non-Unicode

In my yesterday's post I reviewed the situation when Unicode data type is not necessary for string values in SQL Server database.
In today's post I want to present the script, to identify all Unicode columns in your database, which do not contained ANY unicode characters and might be eligible to be converted to Non-Unicode.

SELECT o.object_id
    , s.name AS SchemaName
    , o.name AS TableName
    , c.name AS ColumnName
    , t.name AS ColumnType
    , 0 AS CanNotConvert
    , CAST(0 AS FLOAT) AS CanSaveMB
INTO #ColumnList
FROM sys.schemas AS s
INNER JOIN sys.tables AS o
    ON o.schema_id = s.schema_id
INNER JOIN sys.columns AS c
    ON c.object_id = o.object_id
INNER JOIN sys.types AS t
    ON t.system_type_id = c.system_type_id
    AND t.name in ('nchar','nvarchar')
GO
DECLARE @Schema SYSNAME, @Table SYSNAME, @Column SYSNAME, @Type SYSNAME;
DECLARE @SQL NVARCHAR(1000), @Count INT, @BUsed INT, @object_id INT;

SELECT @object_id = MIN(object_id) FROM #ColumnList;

WHILE @object_id is NOT NULL
BEGIN
    SELECT @SQL = N'SELECT @Count = SUM(CASE WHEN ['
        + ColumnName + N'] != CAST(CAST(['
        + ColumnName + N'] AS ' + SUBSTRING(ColumnType,2,7)
        + N') AS ' + ColumnType + N') THEN 1 ELSE 0 END), @BUsed = SUM(LEN(['
        + ColumnName + N'])) FROM ['
        + SchemaName + N'].[' + TableName + N'];'
    FROM #ColumnList WHERE object_id = @object_id;

    PRINT @SQL;
    EXEC sp_executesql @SQL,
        N'@Count INT OUTPUT, @BUsed INT OUTPUT',
        @Count=@Count OUTPUT, @BUsed=@BUsed OUTPUT;
   
    UPDATE #ColumnList
    SET CanNotConvert = IsNull(@Count,0), CanSaveMB = IsNull(@BUsed/1048576.,0)
    WHERE object_id = @object_id;
   
    ;WITH NextID AS (
        SELECT MIN(object_id) AS NextID
        FROM #ColumnList
        WHERE object_id > @object_id
    )
    SELECT @object_id = NextID
    FROM NextID;
       
END
GO
SELECT *
FROM #ColumnList
WHERE CanNotConvert = 0 and CanSaveMB > 0
ORDER BY CanSaveMB DESC;
GO
DROP TABLE #ColumnList;
GO

Column "CanSaveMB" shows the number of Megabytes, which could be saved by using Non-Unicode data type.


No comments:

Post a Comment