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