​

Preface:

Due to the design defect of the project before, that is, SqlServer database stores the data type of string format is varchar, leading to the storage of Chinese and English are no problem, but now do the Spanish version of the software, need to store Spanish characters, such as these special characters o, n, I, a, ¿ Stored into?

So you need to change vARCHAR to nvARCHAR to store Unicode characters

Because there are too many tables in the database, it is inefficient to change them one by one, so you can only use script to change them

implementation

SQL statement to modify the data type of a field

Alter table name ALTER column name nvarcharCopy the code

However, the field may have constraints that cannot be modified successfully, so the constraints must be deleted first

If a constraint exists, the constraint is deleted

If exists(select * from sysobjects where name=' constraint name ') ALTER table name DROP constraint nameCopy the code

Next, we need to get all the user tables for the database

 select name from sysobjects where xtype = 'U'
Copy the code

Get a table field name, field data type, length, only query vARCHAR field

select syscolumns.name as fieldName ,systypes.name as fieldType,syscolumns.length as leng from syscolumns inner join Xtype =syscolumns. Xtype WHERE id=(select id from sysobjects where name=' tablename ') and systypes ='varchar'Copy the code

When the queried length is -1, the maximum value MAX is deleted

Code in C#

string sqlTables = "select name from sysobjects where xtype = 'U'"; / / get all the users table DataTable tables. = SqlHelper ExecuteDataTable (the ConnectionString, sqlTables); for (int i = 0; i < tables.Rows.Count; I ++) {// tableName string tableName = tables.rows [I][0].tostring (); string sqlFields = @"select syscolumns.name as fieldName ,systypes.name as fieldType,syscolumns.length as leng from syscolumns inner join systypes on systypes.xtype=syscolumns.xtype where id=(select id from sysobjects where name='" + tableName + "') and systypes.name ='varchar' "; / / get a list of the names of all fields, field data type, the length of the DataTable fieldInfos = SqlHelper. ExecuteDataTable (the ConnectionString, sqlFields); try { for (int j = 0; j < fieldInfos.Rows.Count; j++) { string fieldName = fieldInfos.Rows[j][0].ToString(); // Name String fieldType = fieldinfos.rows [j]. Parse(fieldinfos.rows [J][2].toString ()); SbFields = new StringBuilder(); string constraintName = "DF_" + tableName + "_" + fieldName; Append(" if exists(select * from sysobjects where name='" + constraintName + "') "); sbFields.Append(" alter table " + tableName + " drop constraint " + constraintName); sbFields.Append(" alter table " + tableName + " alter column " + fieldName + " nvarchar(" + (leng == -1 ? "MAX" : (leng * 2).ToString()) + ") "); SqlHelper.ExecuteNonQuery(ConnectionString, sbFields.ToString()); } } catch { } }Copy the code

Among them, MY SqlHelper

complete

​