CODEWITHSUNDEEP
t-sqlcollation
user426326
user426326

Reputation: 11

T-SQL - Set Nvarchar columns to DB collation

Is it possible to create a script that sets the collation of all nvarchar columns in a database to the database collation? I have accidentally overridden the collation of all columns to the wrong collation (a tool ruined things for me) and need to create a script that fixes this.

I know that I can get the DB collation by running 'SELECT DATABASEPROPERTYEX('[DBName]', 'Collation');', and that I can run alter column commands, but there must be a simpler way?

I'm thankful for any help!

Upvotes: 1

Views: 562

Answers (1)

Tomalak
Tomalak

Reputation: 338118

Generate the SQL you need with the help of information_schema.columns:

declare @collation varchar(50)

set @collation = 'YOUR_EXPECTED_COLLATION_NAME'

select 
    'ALTER TABLE [' + table_schema + '].[' + table_name + '] ALTER COLUMN [' + column_name + '] ' + data_type + '(' + convert(varchar, character_maximum_length) + ') COLLATE ' + @collation
from 
    information_schema.columns 
where 
    data_type in ('varchar', 'char', 'nvarchar', 'nchar')
    and collation_name <> @collation

Upvotes: 1

Related Questions