Batch Change Database collation

There are many systems in the enterprise is traditional, due to various reasons, the company is currently in the implementation of the simplified, but the system also has the exchange of data, so the system can only one change, in case there are too many problems at the same time. Since the original database can only store traditional, and the previously existing data can not be converted to simplified, replicable. Our new database uses Chinese_prc_bin collation, so we back up the old system directly and then restore it to the new server while changing the collation of the database. But it's not enough to just change the collation of the database. It is only possible to change the tables in the database at the same time. But our database has more than 100 tables, and the rest of the system is far more complex than the present one. The number of tables is also much larger. So we need to find a solution that can be changed in bulk. After a turn of efforts and testing, finally found a solution, deliberately written down to see whether there is the same needs of friends. First, generate the code. Then put the resulting results in the SQL query execution.

Select 'ALTER TABLE' + QuoteName(table_name)+         'ALTER COLUMN' + QuoteName(column_name)+ ' ' + QuoteName(data_type)+   Case  whenCharacter_maximum_length= -1  Then '(max)'   whenData_typeinch('text','ntext') Then "'   whenCharacter_maximum_length is  not NULL       Then '('+(CONVERT(VARCHAR, Character_maximum_length)+')' )        ELSE IsNull(CONVERT(VARCHAR, Character_maximum_length),' ')END        +'COLLATE Chinese_prc_bin' +  Caseis_nullable when 'YES'  Then 'NULL'                  when 'NO'  Then 'Not NULL'                 END    frominformation_schema. Columns,sys.all_objects owhereCollation_name='Chinese_taiwan_stroke_bin'  andtable_name=O.name andO.type='U' Order  by QuoteName(table_name)

Old system: