So, you have a database with a few hundred char and varchar (ASCII) columns and you want to take the plunge and convert them all to their nchar and nvarchar unicode equivalents. Start with the following SQL script in SQL Server Management Studio:
SELECT t.name AS TableName ,c.name AS ColumnName ,y.name AS TypeName ,CASE c.max_length WHEN -1 THEN 'MAX' ELSE CONVERT(varchar(5), c.max_length) END AS MaxLength ,CASE c.is_nullable WHEN 0 THEN 'NOT NULL' ELSE 'NULL' END AS Nullable FROM sys.tables t JOIN sys.columns c ON t.object_id = c.object_id JOIN sys.types y ON c.system_type_id = y.system_type_id WHERE y.name LIKE '%char' AND y.name NOT LIKE 'n%' ORDER BY t.name ,c.name
Save the results to a CSV file.
Use the following C# string format:
IF EXISTS (SELECT *
FROM sys.tables t
JOIN sys.columns c ON t.object_id = c.object_id
JOIN sys.types y ON c.system_type_id = y.system_type_id
WHERE t.name = '{0}'
AND c.name = '{1}'
AND y.name = '{2}')
ALTER TABLE dbo.{0} ALTER COLUMN {1} n{2}({3}) {4}
GO
Run the string format and we have a neatly formatted update for each column:
IF EXISTS (SELECT * FROM sys.tables t JOIN sys.columns c ON t.object_id = c.object_id JOIN sys.types y ON c.system_type_id = y.system_type_id WHERE t.name = 'Address' AND c.name = 'CountryCode' AND y.name = 'char') ALTER TABLE dbo.Address ALTER COLUMN CountryCode nchar(2) NOT NULL GO
If any SQL objects are dependent on these columns (primary keys, foreign keys, default constraints, indexes, etc) you will need to drop them and recreate them after altering the column.
Recent Comments