When working with SQL Server databases, you'll eventually face the need to standardize collations across your environment. While changing the database collation with ALTER DATABASE is straightforward, it only affects new objects - existing columns remain with their original collation. This creates potential comparison issues and collation conflicts.

The fundamental issue is that column-level collation is a property baked into each column's definition. SQL Server doesn't provide a single command to cascade collation changes through an entire database. You must:

1. Handle all string-type columns (varchar, nvarchar, char, nchar, text, ntext)
2. Manage associated indexes and constraints
3. Preserve data during the conversion

Here's a dynamic SQL approach that generates the necessary ALTER statements:

DECLARE @sql NVARCHAR(MAX) = N'';

SELECT @sql = @sql + 
    N'DROP INDEX ' + QUOTENAME(i.name) + ' ON ' + QUOTENAME(s.name) + '.' + QUOTENAME(t.name) + ';
    ALTER TABLE ' + QUOTENAME(s.name) + '.' + QUOTENAME(t.name) + 
    ' ALTER COLUMN ' + QUOTENAME(c.name) + ' ' + tp.name + 
    CASE WHEN tp.name LIKE '%char%' THEN '(' + 
        CASE WHEN c.max_length = -1 THEN 'MAX' 
        ELSE CAST(c.max_length AS VARCHAR(10)) END + ')' 
    ELSE '' END + 
    ' COLLATE Latin1_General_CI_AS' + 
    CASE WHEN c.is_nullable = 1 THEN ' NULL' ELSE ' NOT NULL' END + ';
    CREATE INDEX ' + QUOTENAME(i.name) + ' ON ' + QUOTENAME(s.name) + '.' + QUOTENAME(t.name) + 
    ' (' + QUOTENAME(c.name) + ');'
FROM sys.columns c
INNER JOIN sys.types tp ON c.user_type_id = tp.user_type_id
INNER JOIN sys.tables t ON c.object_id = t.object_id
INNER JOIN sys.schemas s ON t.schema_id = s.schema_id
INNER JOIN sys.index_columns ic ON c.object_id = ic.object_id AND c.column_id = ic.column_id
INNER JOIN sys.indexes i ON ic.object_id = i.object_id AND ic.index_id = i.index_id
WHERE tp.name IN ('varchar', 'nvarchar', 'char', 'nchar', 'text', 'ntext')
AND c.collation_name = 'SQL_Latin1_General_CP1_CI_AS';

EXEC sp_executesql @sql;

• Dependencies: Handle foreign keys, computed columns, and other dependencies first
• Downtime: This operation requires exclusive access to tables
• Testing: Always test on a backup first
• Transaction Management: Consider wrapping in transactions for safety

For complex databases, sometimes it's easier to:

1. Script out the entire database schema
2. Modify the collation in the script
3. Create a new database with the correct collation
4. Import the data

When working with SQL Server, changing database collation is more complex than it first appears. While ALTER DATABASE modifies the default collation for new objects, existing columns retain their original collation settings. This creates a hybrid environment that can lead to subtle comparison and sorting issues.

The fundamental issue is that collation is a property tied to each character-based column (varchar, nvarchar, char, nchar, text, ntext). To standardize across an entire database, you must:

1. Identify all character columns in all tables
2. Drop dependent constraints and indexes
3. Alter each column's collation
4. Recreate indexes and constraints

Here's a comprehensive script that generates the necessary ALTER statements:

DECLARE @sql NVARCHAR(MAX) = N'';
SELECT @sql = @sql + 
    'PRINT ''Dropping indexes and constraints on [' + SCHEMA_NAME(t.schema_id) + '].[' + t.name + '].[' + c.name + ']''' + CHAR(13) + CHAR(10) +
    (SELECT 
        'DROP ' + 
        CASE WHEN i.is_primary_key = 1 THEN 'PRIMARY KEY CONSTRAINT' 
             WHEN i.is_unique_constraint = 1 THEN 'UNIQUE CONSTRAINT'
             ELSE 'INDEX' END + 
        ' [' + i.name + '] ON [' + SCHEMA_NAME(t.schema_id) + '].[' + t.name + '];' + CHAR(13) + CHAR(10)
     FROM sys.indexes i
     JOIN sys.index_columns ic ON i.object_id = ic.object_id AND i.index_id = ic.index_id
     WHERE ic.column_id = c.column_id AND i.object_id = t.object_id
     FOR XML PATH('')) +
    'ALTER TABLE [' + SCHEMA_NAME(t.schema_id) + '].[' + t.name + '] ALTER COLUMN [' + c.name + '] ' + 
    ty.name + 
    CASE WHEN ty.name IN ('varchar', 'nvarchar', 'char', 'nchar') THEN 
        '(' + CASE WHEN c.max_length = -1 THEN 'MAX' ELSE CAST(c.max_length AS VARCHAR) END + ')' 
        ELSE '' END + 
    ' COLLATE Latin1_General_CI_AS' + 
    CASE WHEN c.is_nullable = 1 THEN ' NULL' ELSE ' NOT NULL' END + ';' + CHAR(13) + CHAR(10) +
    'PRINT ''Column [' + c.name + '] altered successfully'';' + CHAR(13) + CHAR(10) + CHAR(13) + CHAR(10)
FROM sys.tables t
JOIN sys.columns c ON t.object_id = c.object_id
JOIN sys.types ty ON c.user_type_id = ty.user_type_id
WHERE ty.name IN ('varchar', 'nvarchar', 'char', 'nchar', 'text', 'ntext')
AND c.collation_name = 'SQL_Latin1_General_CP1_CI_AS';

EXEC sp_executesql @sql;

Key considerations when running this process:

• Schedule during maintenance windows as large tables may lock
• Consider using WITH (ONLINE = ON) for Enterprise Edition
• Test thoroughly in non-production first
• Backup the database before execution

For very large databases, consider this phased approach:

-- 1. Create new tables with correct collation
SELECT 
    'SELECT * INTO [' + SCHEMA_NAME(schema_id) + '].[' + name + '_New] FROM [' + 
    SCHEMA_NAME(schema_id) + '].[' + name + '] WHERE 1 = 0;' + CHAR(13) + CHAR(10) +
    'EXEC sp_rename ''' + SCHEMA_NAME(schema_id) + '.' + name + ''', ''' + name + '_Old'';' + CHAR(13) + CHAR(10) +
    'EXEC sp_rename ''' + SCHEMA_NAME(schema_id) + '.' + name + '_New'', ''' + name + ''';'
FROM sys.tables;

-- 2. Generate INSERT statements with proper collation conversion
-- 3. Recreate constraints and indexes
-- 4. Drop old tables after verification