SQL Server - How to Standardize Collation of All Database Columns

In this article

Reading Time: 15 minutes

Hello everybody!
All quiet?

In this article, I would like to share with you a problem I had recently, which several columns from various tables in a given database used a different collation from the DB standard, causing joins and WHERE conditions between VARCHAR / CHAR / columns. NVARCHAR with different collations, the bank returns us the following error message:

Cannot resolve the collation conflict between “SQL_Latin1_General_CP1_CI_AI” and “SQL_Latin1_General_CP1_CI_AS” in the equal to operation.

What is a COLLATION?

Collation is nothing more than the form of character encoding that a database uses to interpret them.

A Collation is a grouping of these characters in a certain order (each Collation has a different order), where the “A” is a different character than the “a”, if the collation is case-sensitive (case sensitive) and the “A” is different from “á”, if the collation is Accent-Sensitive (accent differentiation).

COLLATION has three levels of hierarchy:
- Server
- Data base
- Column

If the database is created without specifying which collation to use, it will be created with the server's collation (operating system language). When a table is created without specifying the collation of text columns (VARCHAR, NVARCHAR, CHAR, etc.), the database collate will be used as the table collation.

In SQL Server, the name of the Collation follows the following naming pattern:
SQL_CollationDesignator_CaseSensitivity_AccentSensitivity_KanatypeSensitive_WidthSensitivity

Collation Example:
SQL_Latin1_General_CP1_CS_AS

Where:

CollationDesignator: Specifies the basic grouping rules used by Windows grouping, where sorting rules are based on the alphabet or language.
CaseSensitivity: CI specifies case-insensitive, CS specifies case-insensitive.
AccentSensitivity: AI specifies that it does not differentiate accents, AS specifies that it differentiates accent.
KanatypeSensitive: Omitted specifies not distinguishing kana characters, KS specifies distinguishing kana characters.
WidthSensitivity: Omitted specifies that it does not distinguish width, WS specifies that it distinguishes width.

If a column is using a COLLATION case sensitive (CS), a query like SELECT * FROM Table WHERE Column LIKE '% Oracle%' will return the “Oracle” record, but it will not return the “oracle” record.

The same thing happens with a column using a COLLATION accent sensitive (AS). A query like SELECT * FROM Table WHERE Column LIKE '% JOÃO%' will return the record "João", but will not return the record "Joao".

For a complete list of collations by region and language, visit this link, remembering that the most used in Portuguese (Brazil) is SQL_Latin1_General_CP1_CI_AI (or SQL_Latin1_General_CP1_CS_AS).

To learn more about what Collation is, take a look at the article. SQL Server - Cannot resolve the collation conflict between… in the same as operation..

How to identify the default collation?

To identify the default collation of a database, simply use the query below:

USE [dirceuresende]
GO

SELECT collation_name
FROM sys.databases
WHERE database_id = DB_ID()

USE [dirceuresende]

SELECT collation_name

FROM sys.databases

WHERE database_id = DB_ID()

Result:

To identify your instance's default collation, just use the command below:

SELECT SERVERPROPERTY('Collation')

1	SELECT SERVERPROPERTY('Collation')

Result:

Test base generation

To standardize the examples and help with the practical understanding of this article, I will use a simple script to generate sample data (tests):

------------------------------------------------------------------------
-- Geração dos dados para teste
------------------------------------------------------------------------

CREATE TABLE dbo.Cliente (
    CPF VARCHAR(11) COLLATE Latin1_General_CS_AS NOT NULL PRIMARY KEY CLUSTERED,
    Nome VARCHAR(50)
)

INSERT INTO dbo.Cliente ( CPF, Nome )
VALUES ( '12345678909', 'Joãozinho' )

CREATE NONCLUSTERED INDEX SK01 ON dbo.Cliente(CPF) INCLUDE(Nome)


CREATE TABLE dbo.Pedido (
    Id INT IDENTITY(1,1) NOT NULL PRIMARY KEY CLUSTERED,
    Dt_Pedido DATETIME DEFAULT GETDATE(),
    Vl_Pedido MONEY,
    CPF_Cliente VARCHAR(11) COLLATE Latin1_General_CS_AS CONSTRAINT [FK_Cliente] FOREIGN KEY(CPF_Cliente) REFERENCES dbo.Cliente(CPF) NOT NULL
)

INSERT INTO dbo.Pedido ( Vl_Pedido, CPF_Cliente )
VALUES
(
    9.99, -- Vl_Pedido - money
    '12345678909' -- Id_Cliente - int
)

CREATE NONCLUSTERED INDEX SK02 ON dbo.Pedido(Id, Dt_Pedido, CPF_Cliente)

------------------------------------------------------------------------

-- Geração dos dados para teste

------------------------------------------------------------------------

CREATE TABLE dbo.Cliente (

CPF VARCHAR(11) COLLATE Latin1_General_CS_AS NOT NULL PRIMARY KEY CLUSTERED,

Nome VARCHAR(50)

)

INSERT INTO dbo.Cliente ( CPF, Nome )

VALUES ( '12345678909', 'Joãozinho' )

CREATE NONCLUSTERED INDEX SK01 ON dbo.Cliente(CPF) INCLUDE(Nome)

CREATE TABLE dbo.Pedido (

Id INT IDENTITY(1,1) NOT NULL PRIMARY KEY CLUSTERED,

Dt_Pedido DATETIME DEFAULT GETDATE(),

Vl_Pedido MONEY,

CPF_Cliente VARCHAR(11) COLLATE Latin1_General_CS_AS CONSTRAINT [FK_Cliente] FOREIGN KEY(CPF_Cliente) REFERENCES dbo.Cliente(CPF) NOT NULL

)

INSERT INTO dbo.Pedido ( Vl_Pedido, CPF_Cliente )

VALUES

(

9.99, -- Vl_Pedido - money

'12345678909' -- Id_Cliente - int

)

CREATE NONCLUSTERED INDEX SK02 ON dbo.Pedido(Id, Dt_Pedido, CPF_Cliente)

How to standardize the collation of all columns?

If you want to standardize the collation of all columns in a database so that they use a specific collation or database standard, I developed a script that will identify all non-standard columns and execute an ALTER TABLE command. to apply the change.

Since there are dependencies (constraints, indexes, and foreign keys), the script will drop the identified dependent objects, apply the changes, and recreate those objects. Because of this, I strongly recommend that you test well before running this script in production. Preferably make a structure backup first.

Script to change the collation of all columns (and dependencies) that have a different collation than database
View source

SET NOCOUNT ON

-------------------------------------------------------------------------
-- Identifica as colunas com collation diferente do padrão do database conectado
-------------------------------------------------------------------------

IF (OBJECT_ID('tempdb..#Colunas') IS NOT NULL) DROP TABLE #Colunas
SELECT
    B.[object_id] AS table_object_id,
    B.[name] AS table_name,
    B.[schema_id] AS table_schema_id,
    C.[name] AS [type_name],
    D.[name] AS [schema_name],
    D.[schema_id],
    A.*,
    C.system_type_id AS type_system_type_id,
    C.user_type_id AS type_user_type_id,
    C.collation_name AS type_collation_name,
    E.collation_name AS [database_default_collation]
INTO
    #Colunas
FROM
    sys.columns		            AS A
    JOIN sys.tables	            AS B ON A.[object_id] = B.[object_id]
    JOIN sys.types		    AS C ON A.user_type_id = C.user_type_id
    JOIN sys.schemas	            AS D ON B.[schema_id] = D.[schema_id]
    JOIN sys.databases	            AS E ON E.database_id = DB_ID()
WHERE
    B.is_ms_shipped = 0
    AND A.collation_name <> E.collation_name COLLATE DATABASE_DEFAULT
   

IF (OBJECT_ID('tempdb..#Tabelas') IS NOT NULL) DROP table #Tabelas
SELECT DISTINCT
    table_object_id,
    table_name,
    table_schema_id,
    [schema_name]
INTO
    #Tabelas
FROM
    #Colunas


-------------------------------------------------------------------------
-- Identifica os índices que utilizam essas colunas
-------------------------------------------------------------------------
    
IF (OBJECT_ID('tempdb..#Indices') IS NOT NULL) DROP TABLE #Indices
SELECT 
    A.*,
    B.[name] AS index_name,
    B.[type],
    B.[type_desc],
    B.is_unique,
    B.is_primary_key,
    B.is_unique_constraint,
    B.is_disabled
INTO 
    #Indices
FROM
    #Colunas A
    JOIN sys.indexes B ON A.table_object_id = B.[object_id]
WHERE
    EXISTS(SELECT NULL FROM sys.index_columns X WHERE X.index_id = B.index_id AND X.column_id = A.column_id)
    

IF (OBJECT_ID('tempdb..#Indices_Alteracao') IS NOT NULL) DROP TABLE #Indices_Alteracao
SELECT DISTINCT
    table_object_id,
    table_schema_id,
    table_name,
    [schema_name],
    type_collation_name,
    database_default_collation,
    index_name,
    is_unique_constraint,
    is_primary_key
INTO
    #Indices_Alteracao
FROM
    #Indices


-------------------------------------------------------------------------
-- Gera os scripts
-------------------------------------------------------------------------

DECLARE 
    @CmdAlterTable VARCHAR(MAX) = '',

    @CmdDropIndex VARCHAR(MAX) = '',
    @CmdCreateIndex VARCHAR(MAX) = '',

    @CmdDropFK VARCHAR(MAX) = '',
    @CmdCreateFK VARCHAR(MAX) = '',

    @CmdDropIndexConstraint VARCHAR(MAX) = '',
    @CmdCreateIndexConstraint VARCHAR(MAX) = '',

    @CmdDisableCK VARCHAR(MAX) = '',
    @CmdEnableCK VARCHAR(MAX) = '',

    @Fl_Debug BIT = 1


SELECT 
    @CmdDropIndex += (CASE WHEN is_unique_constraint = 0 AND is_primary_key = 0 THEN 'DROP INDEX [' + index_name + '] ON [' + [schema_name] + '].[' + table_name + ']; ' ELSE '' END),
    @CmdDropIndexConstraint += (CASE WHEN is_unique_constraint = 1 OR is_primary_key = 1 THEN 'ALTER TABLE [' + [schema_name] + '].[' + table_name + '] DROP CONSTRAINT [' + index_name + ']; ' ELSE '' END),
    @CmdDisableCK += 'ALTER TABLE [' + [schema_name] + '].[' + table_name + '] NOCHECK CONSTRAINT ALL; ',
    @CmdEnableCK += 'ALTER TABLE [' + [schema_name] + '].[' + table_name + '] CHECK CONSTRAINT ALL; '
FROM
    #Indices_Alteracao

    

-------------------------------------------------------------------------
-- Identifica AS FK's que utilizam essas colunas
-------------------------------------------------------------------------


SELECT
    @CmdDropFK += N'ALTER TABLE ' + QUOTENAME(C.[name]) + '.' + QUOTENAME(B.[name]) + ' DROP CONSTRAINT ' + QUOTENAME(A.[name]) + ';'
FROM
    sys.foreign_keys AS A
    JOIN sys.tables AS B ON A.parent_object_id = B.[object_id]
    JOIN sys.schemas AS C ON B.[schema_id] = C.[schema_id]
    JOIN #Tabelas AS D ON B.[object_id] = D.table_object_id


SELECT
    @CmdCreateFK += N'ALTER TABLE ' + QUOTENAME(H.[name]) + '.' + QUOTENAME(G.[name]) + ' ADD CONSTRAINT ' + QUOTENAME(D.[name]) + ' FOREIGN KEY (' + 
    STUFF((
        SELECT
            ',' + QUOTENAME(A.[name])
        FROM
            sys.columns AS A
            INNER JOIN sys.foreign_key_columns AS B ON B.parent_column_id = A.column_id AND B.parent_object_id = A.[object_id]
        WHERE
            B.constraint_object_id = D.[object_id]
        ORDER BY
            B.constraint_column_id
        FOR XML PATH(N''), TYPE).value(N'.[1]', N'nvarchar(max)'), 1, 1, N'') + ') REFERENCES ' + QUOTENAME(F.[name]) + '.' + QUOTENAME(E.[name]) + '(' + 
    STUFF((
        SELECT
            ',' + QUOTENAME(C.[name])
        FROM
            sys.columns AS C
            INNER JOIN sys.foreign_key_columns AS fkc ON fkc.referenced_column_id = C.column_id AND fkc.referenced_object_id = C.[object_id]
        WHERE
            fkc.constraint_object_id = D.[object_id]
        ORDER BY
            fkc.constraint_column_id
        FOR XML PATH(N''), TYPE
    ).value(N'.[1]', N'nvarchar(max)'), 1, 1, N''
                                                                                                                                                                                                                   ) + ');'
FROM
    sys.foreign_keys AS D
    JOIN sys.tables AS E ON D.referenced_object_id = E.[object_id]
    JOIN sys.schemas AS F ON E.[schema_id] = F.[schema_id]
    JOIN sys.tables AS G ON D.parent_object_id = G.[object_id]
    JOIN sys.schemas AS H ON G.[schema_id] = H.[schema_id]
    JOIN #Tabelas AS I ON G.[object_id] = I.table_object_id
WHERE
    E.is_ms_shipped = 0
    AND G.is_ms_shipped = 0


SELECT
    @CmdCreateIndex += (CASE WHEN B.is_unique_constraint = 0 AND B.is_primary_key = 0 THEN 'CREATE ' + (CASE WHEN B.is_unique = 1 THEN 'UNIQUE ' ELSE '' END) + B.[type_desc] COLLATE DATABASE_DEFAULT + ' INDEX [' + B.[name] + '] ON [' + SCHEMA_NAME(C.[schema_id]) + '].[' + C.[name] + '] (' + E.KeyColumns + ')' + ISNULL(' INCLUDE (' + I.IncludedColumns + ')', '') + ISNULL(' WHERE ' + B.filter_definition, '') + ' WITH (' + CASE WHEN B.is_padded = 1 THEN 'PAD_INDEX = ON' ELSE 'PAD_INDEX = OFF' END + ', ' + 'FILLFACTOR = ' + CONVERT(VARCHAR(5), CASE WHEN B.fill_factor = 0 THEN 100 ELSE B.fill_factor END) + ', ' +
    'SORT_IN_TEMPDB = OFF, ' + (CASE WHEN B.[ignore_dup_key] = 1 THEN 'IGNORE_DUP_KEY = ON' ELSE 'IGNORE_DUP_KEY = OFF' END) + ', ' + (CASE WHEN F.no_recompute = 0 THEN 'STATISTICS_NORECOMPUTE = OFF' ELSE 'STATISTICS_NORECOMPUTE = ON' END) + ', ' +
    'ONLINE = OFF, DATA_COMPRESSION = PAGE, ' + (CASE WHEN B.[allow_row_locks] = 1 THEN 'ALLOW_ROW_LOCKS = ON' ELSE 'ALLOW_ROW_LOCKS = OFF' END) + ', ' + (CASE WHEN B.[allow_page_locks] = 1 THEN 'ALLOW_PAGE_LOCKS = ON' ELSE 'ALLOW_PAGE_LOCKS = OFF' END) + ') ON [' + G.[name] + ']; ' ELSE '' END),

    @CmdCreateIndexConstraint += (CASE WHEN B.is_unique_constraint = 1 OR B.is_primary_key = 1 THEN 'ALTER TABLE [' + A.[schema_name] + '].[' + A.table_name + '] ADD CONSTRAINT [' + A.index_name + '] ' + (CASE WHEN B.is_primary_key = 1 THEN 'PRIMARY KEY ' ELSE '' END) + (CASE WHEN B.is_unique = 1 AND B.is_primary_key = 0 THEN 'UNIQUE ' ELSE '' END) + B.[type_desc] COLLATE DATABASE_DEFAULT + ' (' + E.KeyColumns + ')' + ISNULL(' INCLUDE (' + I.IncludedColumns + ')', '') + ISNULL(' WHERE ' + B.filter_definition, '') + ' WITH (' + CASE WHEN B.is_padded = 1 THEN 'PAD_INDEX = ON' ELSE 'PAD_INDEX = OFF' END + ', ' + 'FILLFACTOR = ' + CONVERT(VARCHAR(5), CASE WHEN B.fill_factor = 0 THEN 100 ELSE B.fill_factor END) + ', ' +
    'SORT_IN_TEMPDB = OFF, ' + (CASE WHEN B.[ignore_dup_key] = 1 THEN 'IGNORE_DUP_KEY = ON' ELSE 'IGNORE_DUP_KEY = OFF' END) + ', ' + (CASE WHEN F.no_recompute = 0 THEN 'STATISTICS_NORECOMPUTE = OFF' ELSE 'STATISTICS_NORECOMPUTE = ON' END) + ', ' +
    'ONLINE = OFF, DATA_COMPRESSION = PAGE, ' + (CASE WHEN B.[allow_row_locks] = 1 THEN 'ALLOW_ROW_LOCKS = ON' ELSE 'ALLOW_ROW_LOCKS = OFF' END) + ', ' + (CASE WHEN B.[allow_page_locks] = 1 THEN 'ALLOW_PAGE_LOCKS = ON' ELSE 'ALLOW_PAGE_LOCKS = OFF' END) + ') ON [' + G.[name] + ']; ' ELSE '' END)
FROM
    #Indices_Alteracao	AS A
    JOIN sys.indexes    AS B ON A.table_object_id = B.[object_id] AND A.index_name = B.[name] COLLATE DATABASE_DEFAULT
    JOIN sys.tables     AS C ON C.[object_id] = B.[object_id]
    JOIN sys.indexes	AS D ON B.[object_id] = D.[object_id] AND B.index_id = D.index_id
    JOIN
    (
        SELECT
            *
        FROM
        (
            SELECT
                X3.[object_id],
                X3.index_id,
                STUFF((
                    SELECT
                        ', [' + X2.[name] + ']' + CASE WHEN MAX(CONVERT(INT, X1.is_descending_key)) = 1 THEN ' DESC' ELSE '' END
                    FROM
                        sys.index_columns AS X1
                        JOIN sys.columns  AS X2 ON X2.[object_id] = X1.[object_id] AND X2.column_id = X1.column_id AND X1.is_included_column = 0
                    WHERE
                        X1.[object_id] = X3.[object_id]
                        AND X1.index_id = X3.index_id
                    GROUP BY
                        X1.[object_id],
                        X2.[name],
                        X1.index_id
                    ORDER BY
                        MAX(X1.key_ordinal)
                    FOR XML PATH('')
                    ),   1, 2, ''
                ) AS KeyColumns
            FROM
                sys.index_columns AS X3
            GROUP BY
                X3.[object_id],
                X3.index_id
        ) AS X4
    )                    AS E ON B.[object_id] = E.[object_id] AND B.index_id = E.index_id
    JOIN sys.stats       AS F ON F.[object_id] = B.[object_id] AND F.stats_id = B.index_id
    JOIN sys.data_spaces AS G ON B.data_space_id = G.data_space_id
    JOIN sys.filegroups  AS H ON B.data_space_id = H.data_space_id
    LEFT JOIN
    (
        SELECT
            *
        FROM
        (
            SELECT
                X3.[object_id],
                X3.index_id,
                STUFF((
                        SELECT
                            ', [' + X2.[name] + ']'
                        FROM
                            sys.index_columns AS X1
                            JOIN sys.columns  AS X2 ON X2.[object_id] = X1.[object_id] AND X2.column_id = X1.column_id AND X1.is_included_column = 1
                        WHERE
                            X1.[object_id] = X3.[object_id]
                            AND X1.index_id = X3.index_id
                        GROUP BY
                            X1.[object_id],
                            X2.[name],
                            X1.index_id
                        FOR XML PATH('')
                    ),   1, 2, ''
                ) AS IncludedColumns
            FROM
                sys.index_columns AS X3  
            GROUP BY
                X3.[object_id],
                X3.index_id
        ) AS tmp1
        WHERE
            IncludedColumns IS NOT NULL
    ) AS I ON I.[object_id] = B.[object_id] AND I.index_id = B.index_id


SELECT
    @CmdAlterTable += 'ALTER TABLE [' + [schema_name] + '].[' + table_name + '] ALTER COLUMN [' + [name] + '] ' + UPPER([type_name]) + (CASE WHEN UPPER([type_name]) <> 'SYSNAME' THEN '(' + (CASE WHEN A.max_length > 0 THEN CAST(A.max_length AS VARCHAR(10)) ELSE 'MAX' END) + ')' ELSE '' END) + ' COLLATE ' + [database_default_collation] + ' ' + (CASE WHEN A.is_sparse = 1 THEN 'SPARSE ' ELSE '' END) + (CASE WHEN A.is_nullable = 1 THEN 'NULL' ELSE 'NOT NULL' END) + ';'
FROM
    #Colunas A

    


-------------------------------------------------------------------------
-- Executa os scripts
-------------------------------------------------------------------------

PRINT '---------- Dropando as contraints FK'
IF (NULLIF(LTRIM(RTRIM(@CmdDropFK)), '') IS NOT NULL) PRINT @CmdDropFK
IF (@Fl_Debug = 0) EXEC(@CmdDropFK)

PRINT '---------- Desativando as Check Contraints'
IF (NULLIF(LTRIM(RTRIM(@CmdDisableCK)), '') IS NOT NULL) PRINT @CmdDisableCK
IF (@Fl_Debug = 0) EXEC(@CmdDisableCK)

PRINT '---------- Dropando os índices'
IF (NULLIF(LTRIM(RTRIM(@CmdDropIndex)), '') IS NOT NULL) PRINT @CmdDropIndex
IF (@Fl_Debug = 0) EXEC(@CmdDropIndex)

PRINT '---------- Dropando os índices em constraints'
IF (NULLIF(LTRIM(RTRIM(@CmdDropIndexConstraint)), '') IS NOT NULL) PRINT @CmdDropIndexConstraint
IF (@Fl_Debug = 0) EXEC(@CmdDropIndexConstraint)

PRINT '---------- Alterando a tabela'
IF (NULLIF(LTRIM(RTRIM(@CmdAlterTable)), '') IS NOT NULL) PRINT @CmdAlterTable
IF (@Fl_Debug = 0) EXEC(@CmdAlterTable)

PRINT '---------- Reativando as constraints'
IF (NULLIF(LTRIM(RTRIM(@CmdEnableCK)), '') IS NOT NULL) PRINT @CmdEnableCK
IF (@Fl_Debug = 0) EXEC(@CmdEnableCK)

PRINT '---------- Recriando os índices'
IF (NULLIF(LTRIM(RTRIM(@CmdCreateIndex)), '') IS NOT NULL) PRINT @CmdCreateIndex
IF (@Fl_Debug = 0) EXEC(@CmdCreateIndex)

PRINT '---------- Recriando os índices em constraints'
IF (NULLIF(LTRIM(RTRIM(@CmdCreateIndexConstraint)), '') IS NOT NULL) PRINT @CmdCreateIndexConstraint
IF (@Fl_Debug = 0) EXEC(@CmdCreateIndexConstraint)

PRINT '---------- Recriando as FKs'
IF (NULLIF(LTRIM(RTRIM(@CmdCreateFK)), '') IS NOT NULL) PRINT @CmdCreateFK
IF (@Fl_Debug = 0) EXEC(@CmdCreateFK)

100

101

102

103

104

105

106

107

108

109

110

111

112

113

114

115

116

117

118

119

120

121

122

123

124

125

126

127

128

129

130

131

132

133

134

135

136

137

138

139

140

141

142

143

144

145

146

147

148

149

150

151

152

153

154

155

156

157

158

159

160

161

162

163

164

165

166

167

168

169

170

171

172

173

174

175

176

177

178

179

180

181

182

183

184

185

186

187

188

189

190

191

192

193

194

195

196

197

198

199

200

201

202

203

204

205

206

207

208

209

210

211

212

213

214

215

216

217

218

219

220

221

222

223

224

225

226

227

228

229

230

231

232

233

234

235

236

237

238

239

240

241

242

243

244

245

246

247

248

249

250

251

252

253

254

255

256

257

258

259

260

261

262

263

264

265

266

267

268

269

270

271

272

273

274

275

276

277

278

279

280

281

282

283

284

285

286

287

288

289

290

291

292

293

294

295

296

297

298

299

300

301

SET NOCOUNT ON

-------------------------------------------------------------------------

-- Identifica as colunas com collation diferente do padrão do database conectado

-------------------------------------------------------------------------

IF (OBJECT_ID('tempdb..#Colunas') IS NOT NULL) DROP TABLE #Colunas

SELECT

B.[object_id] AS table_object_id,

B.[name] AS table_name,

B.[schema_id] AS table_schema_id,

C.[name] AS [type_name],

D.[name] AS [schema_name],

D.[schema_id],

A.*,

C.system_type_id AS type_system_type_id,

C.user_type_id AS type_user_type_id,

C.collation_name AS type_collation_name,

E.collation_name AS [database_default_collation]

INTO

#Colunas

FROM

sys.columns AS A

JOIN sys.tables AS B ON A.[object_id] = B.[object_id]

JOIN sys.types AS C ON A.user_type_id = C.user_type_id

JOIN sys.schemas AS D ON B.[schema_id] = D.[schema_id]

JOIN sys.databases AS E ON E.database_id = DB_ID()

WHERE

B.is_ms_shipped = 0

AND A.collation_name <> E.collation_name COLLATE DATABASE_DEFAULT

IF (OBJECT_ID('tempdb..#Tabelas') IS NOT NULL) DROP table #Tabelas

SELECT DISTINCT

table_object_id,

table_name,

table_schema_id,

[schema_name]

INTO

#Tabelas

FROM

#Colunas

-------------------------------------------------------------------------

-- Identifica os índices que utilizam essas colunas

-------------------------------------------------------------------------

IF (OBJECT_ID('tempdb..#Indices') IS NOT NULL) DROP TABLE #Indices

SELECT

A.*,

B.[name] AS index_name,

B.[type],

B.[type_desc],

B.is_unique,

B.is_primary_key,

B.is_unique_constraint,

B.is_disabled

INTO

#Indices

FROM

#Colunas A

JOIN sys.indexes B ON A.table_object_id = B.[object_id]

WHERE

EXISTS(SELECT NULL FROM sys.index_columns X WHERE X.index_id = B.index_id AND X.column_id = A.column_id)

IF (OBJECT_ID('tempdb..#Indices_Alteracao') IS NOT NULL) DROP TABLE #Indices_Alteracao

SELECT DISTINCT

table_object_id,

table_schema_id,

table_name,

[schema_name],

type_collation_name,

database_default_collation,

index_name,

is_unique_constraint,

is_primary_key

INTO

#Indices_Alteracao

FROM

#Indices

-------------------------------------------------------------------------

-- Gera os scripts

-------------------------------------------------------------------------

DECLARE

@CmdAlterTable VARCHAR(MAX) = '',

@CmdDropIndex VARCHAR(MAX) = '',

@CmdCreateIndex VARCHAR(MAX) = '',

@CmdDropFK VARCHAR(MAX) = '',

@CmdCreateFK VARCHAR(MAX) = '',

@CmdDropIndexConstraint VARCHAR(MAX) = '',

@CmdCreateIndexConstraint VARCHAR(MAX) = '',

@CmdDisableCK VARCHAR(MAX) = '',

@CmdEnableCK VARCHAR(MAX) = '',

@Fl_Debug BIT = 1

SELECT

@CmdDropIndex += (CASE WHEN is_unique_constraint = 0 AND is_primary_key = 0 THEN 'DROP INDEX [' + index_name + '] ON [' + [schema_name] + '].[' + table_name + ']; ' ELSE '' END),

@CmdDropIndexConstraint += (CASE WHEN is_unique_constraint = 1 OR is_primary_key = 1 THEN 'ALTER TABLE [' + [schema_name] + '].[' + table_name + '] DROP CONSTRAINT [' + index_name + ']; ' ELSE '' END),

@CmdDisableCK += 'ALTER TABLE [' + [schema_name] + '].[' + table_name + '] NOCHECK CONSTRAINT ALL; ',

@CmdEnableCK += 'ALTER TABLE [' + [schema_name] + '].[' + table_name + '] CHECK CONSTRAINT ALL; '

FROM

#Indices_Alteracao

-------------------------------------------------------------------------

-- Identifica AS FK's que utilizam essas colunas

-------------------------------------------------------------------------

SELECT

@CmdDropFK += N'ALTER TABLE ' + QUOTENAME(C.[name]) + '.' + QUOTENAME(B.[name]) + ' DROP CONSTRAINT ' + QUOTENAME(A.[name]) + ';'

FROM

sys.foreign_keys AS A

JOIN sys.tables AS B ON A.parent_object_id = B.[object_id]

JOIN sys.schemas AS C ON B.[schema_id] = C.[schema_id]

JOIN #Tabelas AS D ON B.[object_id] = D.table_object_id

SELECT

@CmdCreateFK += N'ALTER TABLE ' + QUOTENAME(H.[name]) + '.' + QUOTENAME(G.[name]) + ' ADD CONSTRAINT ' + QUOTENAME(D.[name]) + ' FOREIGN KEY (' +

STUFF((

SELECT

',' + QUOTENAME(A.[name])

FROM

sys.columns AS A

INNER JOIN sys.foreign_key_columns AS B ON B.parent_column_id = A.column_id AND B.parent_object_id = A.[object_id]

WHERE

B.constraint_object_id = D.[object_id]

ORDER BY

B.constraint_column_id

FOR XML PATH(N''), TYPE).value(N'.[1]', N'nvarchar(max)'), 1, 1, N'') + ') REFERENCES ' + QUOTENAME(F.[name]) + '.' + QUOTENAME(E.[name]) + '(' +

STUFF((

SELECT

',' + QUOTENAME(C.[name])

FROM

sys.columns AS C

INNER JOIN sys.foreign_key_columns AS fkc ON fkc.referenced_column_id = C.column_id AND fkc.referenced_object_id = C.[object_id]

WHERE

fkc.constraint_object_id = D.[object_id]

ORDER BY

fkc.constraint_column_id

FOR XML PATH(N''), TYPE

).value(N'.[1]', N'nvarchar(max)'), 1, 1, N''

) + ');'

FROM

sys.foreign_keys AS D

JOIN sys.tables AS E ON D.referenced_object_id = E.[object_id]

JOIN sys.schemas AS F ON E.[schema_id] = F.[schema_id]

JOIN sys.tables AS G ON D.parent_object_id = G.[object_id]

JOIN sys.schemas AS H ON G.[schema_id] = H.[schema_id]

JOIN #Tabelas AS I ON G.[object_id] = I.table_object_id

WHERE

E.is_ms_shipped = 0

AND G.is_ms_shipped = 0

SELECT

@CmdCreateIndex += (CASE WHEN B.is_unique_constraint = 0 AND B.is_primary_key = 0 THEN 'CREATE ' + (CASE WHEN B.is_unique = 1 THEN 'UNIQUE ' ELSE '' END) + B.[type_desc] COLLATE DATABASE_DEFAULT + ' INDEX [' + B.[name] + '] ON [' + SCHEMA_NAME(C.[schema_id]) + '].[' + C.[name] + '] (' + E.KeyColumns + ')' + ISNULL(' INCLUDE (' + I.IncludedColumns + ')', '') + ISNULL(' WHERE ' + B.filter_definition, '') + ' WITH (' + CASE WHEN B.is_padded = 1 THEN 'PAD_INDEX = ON' ELSE 'PAD_INDEX = OFF' END + ', ' + 'FILLFACTOR = ' + CONVERT(VARCHAR(5), CASE WHEN B.fill_factor = 0 THEN 100 ELSE B.fill_factor END) + ', ' +

'SORT_IN_TEMPDB = OFF, ' + (CASE WHEN B.[ignore_dup_key] = 1 THEN 'IGNORE_DUP_KEY = ON' ELSE 'IGNORE_DUP_KEY = OFF' END) + ', ' + (CASE WHEN F.no_recompute = 0 THEN 'STATISTICS_NORECOMPUTE = OFF' ELSE 'STATISTICS_NORECOMPUTE = ON' END) + ', ' +

'ONLINE = OFF, DATA_COMPRESSION = PAGE, ' + (CASE WHEN B.[allow_row_locks] = 1 THEN 'ALLOW_ROW_LOCKS = ON' ELSE 'ALLOW_ROW_LOCKS = OFF' END) + ', ' + (CASE WHEN B.[allow_page_locks] = 1 THEN 'ALLOW_PAGE_LOCKS = ON' ELSE 'ALLOW_PAGE_LOCKS = OFF' END) + ') ON [' + G.[name] + ']; ' ELSE '' END),

@CmdCreateIndexConstraint += (CASE WHEN B.is_unique_constraint = 1 OR B.is_primary_key = 1 THEN 'ALTER TABLE [' + A.[schema_name] + '].[' + A.table_name + '] ADD CONSTRAINT [' + A.index_name + '] ' + (CASE WHEN B.is_primary_key = 1 THEN 'PRIMARY KEY ' ELSE '' END) + (CASE WHEN B.is_unique = 1 AND B.is_primary_key = 0 THEN 'UNIQUE ' ELSE '' END) + B.[type_desc] COLLATE DATABASE_DEFAULT + ' (' + E.KeyColumns + ')' + ISNULL(' INCLUDE (' + I.IncludedColumns + ')', '') + ISNULL(' WHERE ' + B.filter_definition, '') + ' WITH (' + CASE WHEN B.is_padded = 1 THEN 'PAD_INDEX = ON' ELSE 'PAD_INDEX = OFF' END + ', ' + 'FILLFACTOR = ' + CONVERT(VARCHAR(5), CASE WHEN B.fill_factor = 0 THEN 100 ELSE B.fill_factor END) + ', ' +

FROM

#Indices_Alteracao AS A

JOIN sys.indexes AS B ON A.table_object_id = B.[object_id] AND A.index_name = B.[name] COLLATE DATABASE_DEFAULT

JOIN sys.tables AS C ON C.[object_id] = B.[object_id]

JOIN sys.indexes AS D ON B.[object_id] = D.[object_id] AND B.index_id = D.index_id

JOIN

(

SELECT

FROM

(

SELECT

X3.[object_id],

X3.index_id,

STUFF((

SELECT

', [' + X2.[name] + ']' + CASE WHEN MAX(CONVERT(INT, X1.is_descending_key)) = 1 THEN ' DESC' ELSE '' END

FROM

sys.index_columns AS X1

JOIN sys.columns AS X2 ON X2.[object_id] = X1.[object_id] AND X2.column_id = X1.column_id AND X1.is_included_column = 0

WHERE

X1.[object_id] = X3.[object_id]

AND X1.index_id = X3.index_id

GROUP BY

X1.[object_id],

X2.[name],

X1.index_id

ORDER BY

MAX(X1.key_ordinal)

FOR XML PATH('')

), 1, 2, ''

) AS KeyColumns

FROM

sys.index_columns AS X3

GROUP BY

X3.[object_id],

X3.index_id

) AS X4

) AS E ON B.[object_id] = E.[object_id] AND B.index_id = E.index_id

JOIN sys.stats AS F ON F.[object_id] = B.[object_id] AND F.stats_id = B.index_id

JOIN sys.data_spaces AS G ON B.data_space_id = G.data_space_id

JOIN sys.filegroups AS H ON B.data_space_id = H.data_space_id

LEFT JOIN

(

SELECT

FROM

(

SELECT

X3.[object_id],

X3.index_id,

STUFF((

SELECT

', [' + X2.[name] + ']'

FROM

sys.index_columns AS X1

JOIN sys.columns AS X2 ON X2.[object_id] = X1.[object_id] AND X2.column_id = X1.column_id AND X1.is_included_column = 1

WHERE

X1.[object_id] = X3.[object_id]

AND X1.index_id = X3.index_id

GROUP BY

X1.[object_id],

X2.[name],

X1.index_id

FOR XML PATH('')

), 1, 2, ''

) AS IncludedColumns

FROM

sys.index_columns AS X3

GROUP BY

X3.[object_id],

X3.index_id

) AS tmp1

WHERE

IncludedColumns IS NOT NULL

) AS I ON I.[object_id] = B.[object_id] AND I.index_id = B.index_id

SELECT

@CmdAlterTable += 'ALTER TABLE [' + [schema_name] + '].[' + table_name + '] ALTER COLUMN [' + [name] + '] ' + UPPER([type_name]) + (CASE WHEN UPPER([type_name]) <> 'SYSNAME' THEN '(' + (CASE WHEN A.max_length > 0 THEN CAST(A.max_length AS VARCHAR(10)) ELSE 'MAX' END) + ')' ELSE '' END) + ' COLLATE ' + [database_default_collation] + ' ' + (CASE WHEN A.is_sparse = 1 THEN 'SPARSE ' ELSE '' END) + (CASE WHEN A.is_nullable = 1 THEN 'NULL' ELSE 'NOT NULL' END) + ';'

FROM

#Colunas A

-------------------------------------------------------------------------

-- Executa os scripts

-------------------------------------------------------------------------

PRINT '---------- Dropando as contraints FK'

IF (NULLIF(LTRIM(RTRIM(@CmdDropFK)), '') IS NOT NULL) PRINT @CmdDropFK

IF (@Fl_Debug = 0) EXEC(@CmdDropFK)

PRINT '---------- Desativando as Check Contraints'

IF (NULLIF(LTRIM(RTRIM(@CmdDisableCK)), '') IS NOT NULL) PRINT @CmdDisableCK

IF (@Fl_Debug = 0) EXEC(@CmdDisableCK)

PRINT '---------- Dropando os índices'

IF (NULLIF(LTRIM(RTRIM(@CmdDropIndex)), '') IS NOT NULL) PRINT @CmdDropIndex

IF (@Fl_Debug = 0) EXEC(@CmdDropIndex)

PRINT '---------- Dropando os índices em constraints'

IF (NULLIF(LTRIM(RTRIM(@CmdDropIndexConstraint)), '') IS NOT NULL) PRINT @CmdDropIndexConstraint

IF (@Fl_Debug = 0) EXEC(@CmdDropIndexConstraint)

PRINT '---------- Alterando a tabela'

IF (NULLIF(LTRIM(RTRIM(@CmdAlterTable)), '') IS NOT NULL) PRINT @CmdAlterTable

IF (@Fl_Debug = 0) EXEC(@CmdAlterTable)

PRINT '---------- Reativando as constraints'

IF (NULLIF(LTRIM(RTRIM(@CmdEnableCK)), '') IS NOT NULL) PRINT @CmdEnableCK

IF (@Fl_Debug = 0) EXEC(@CmdEnableCK)

PRINT '---------- Recriando os índices'

IF (NULLIF(LTRIM(RTRIM(@CmdCreateIndex)), '') IS NOT NULL) PRINT @CmdCreateIndex

IF (@Fl_Debug = 0) EXEC(@CmdCreateIndex)

PRINT '---------- Recriando os índices em constraints'

IF (NULLIF(LTRIM(RTRIM(@CmdCreateIndexConstraint)), '') IS NOT NULL) PRINT @CmdCreateIndexConstraint

IF (@Fl_Debug = 0) EXEC(@CmdCreateIndexConstraint)

PRINT '---------- Recriando as FKs'

IF (NULLIF(LTRIM(RTRIM(@CmdCreateFK)), '') IS NOT NULL) PRINT @CmdCreateFK

IF (@Fl_Debug = 0) EXEC(@CmdCreateFK)

Script to change the collation of all columns (and dependencies) to the desired collation:
View source

SET NOCOUNT ON

-------------------------------------------------------------------------
-- Identifica as colunas com collation diferente do desejado
-------------------------------------------------------------------------

DECLARE
    @Collation_Desejado VARCHAR(100) = 'SQL_Latin1_General_CP1_CI_AI' -- SELECT * FROM sys.fn_helpcollations()


IF (OBJECT_ID('tempdb..#Colunas') IS NOT NULL) DROP TABLE #Colunas
SELECT
    B.[object_id] AS table_object_id,
    B.[name] AS table_name,
    B.[schema_id] AS table_schema_id,
    C.[name] AS [type_name],
    D.[name] AS [schema_name],
    D.[schema_id],
    A.*,
    C.system_type_id AS type_system_type_id,
    C.user_type_id AS type_user_type_id,
    C.collation_name AS type_collation_name,
    E.collation_name AS [database_default_collation]
INTO
    #Colunas
FROM
    sys.columns		            AS A
    JOIN sys.tables	            AS B ON A.[object_id] = B.[object_id]
    JOIN sys.types		        AS C ON A.user_type_id = C.user_type_id
    JOIN sys.schemas	        AS D ON B.[schema_id] = D.[schema_id]
    JOIN sys.databases	        AS E ON E.database_id = DB_ID()
WHERE
    B.is_ms_shipped = 0
    AND A.collation_name <> @Collation_Desejado
   

IF (OBJECT_ID('tempdb..#Tabelas') IS NOT NULL) DROP table #Tabelas
SELECT DISTINCT
    table_object_id,
    table_name,
    table_schema_id,
    [schema_name]
INTO
    #Tabelas
FROM
    #Colunas


-------------------------------------------------------------------------
-- Identifica os índices que utilizam essas colunas
-------------------------------------------------------------------------
    
IF (OBJECT_ID('tempdb..#Indices') IS NOT NULL) DROP TABLE #Indices
SELECT 
    A.*,
    B.[name] AS index_name,
    B.[type],
    B.[type_desc],
    B.is_unique,
    B.is_primary_key,
    B.is_unique_constraint,
    B.is_disabled
INTO 
    #Indices
FROM
    #Colunas A
    JOIN sys.indexes B ON A.table_object_id = B.[object_id]
WHERE
    EXISTS(SELECT NULL FROM sys.index_columns X WHERE X.index_id = B.index_id AND X.column_id = A.column_id)
    

IF (OBJECT_ID('tempdb..#Indices_Alteracao') IS NOT NULL) DROP TABLE #Indices_Alteracao
SELECT DISTINCT
    table_object_id,
    table_schema_id,
    table_name,
    [schema_name],
    type_collation_name,
    database_default_collation,
    index_name,
    is_unique_constraint,
    is_primary_key
INTO
    #Indices_Alteracao
FROM
    #Indices


-------------------------------------------------------------------------
-- Gera os scripts
-------------------------------------------------------------------------

DECLARE 
    @CmdAlterTable VARCHAR(MAX) = '',

    @CmdDropIndex VARCHAR(MAX) = '',
    @CmdCreateIndex VARCHAR(MAX) = '',

    @CmdDropFK VARCHAR(MAX) = '',
    @CmdCreateFK VARCHAR(MAX) = '',

    @CmdDropIndexConstraint VARCHAR(MAX) = '',
    @CmdCreateIndexConstraint VARCHAR(MAX) = '',

    @CmdDisableCK VARCHAR(MAX) = '',
    @CmdEnableCK VARCHAR(MAX) = '',

    @Fl_Debug BIT = 1


SELECT 
    @CmdDropIndex += (CASE WHEN is_unique_constraint = 0 AND is_primary_key = 0 THEN 'DROP INDEX [' + index_name + '] ON [' + [schema_name] + '].[' + table_name + ']; ' ELSE '' END),
    @CmdDropIndexConstraint += (CASE WHEN is_unique_constraint = 1 OR is_primary_key = 1 THEN 'ALTER TABLE [' + [schema_name] + '].[' + table_name + '] DROP CONSTRAINT [' + index_name + ']; ' ELSE '' END),
    @CmdDisableCK += 'ALTER TABLE [' + [schema_name] + '].[' + table_name + '] NOCHECK CONSTRAINT ALL; ',
    @CmdEnableCK += 'ALTER TABLE [' + [schema_name] + '].[' + table_name + '] CHECK CONSTRAINT ALL; '
FROM
    #Indices_Alteracao

    

-------------------------------------------------------------------------
-- Identifica AS FK's que utilizam essas colunas
-------------------------------------------------------------------------


SELECT
    @CmdDropFK += N'ALTER TABLE ' + QUOTENAME(C.[name]) + '.' + QUOTENAME(B.[name]) + ' DROP CONSTRAINT ' + QUOTENAME(A.[name]) + ';'
FROM
    sys.foreign_keys AS A
    JOIN sys.tables AS B ON A.parent_object_id = B.[object_id]
    JOIN sys.schemas AS C ON B.[schema_id] = C.[schema_id]
    JOIN #Tabelas AS D ON B.[object_id] = D.table_object_id


SELECT
    @CmdCreateFK += N'ALTER TABLE ' + QUOTENAME(H.[name]) + '.' + QUOTENAME(G.[name]) + ' ADD CONSTRAINT ' + QUOTENAME(D.[name]) + ' FOREIGN KEY (' + 
    STUFF((
        SELECT
            ',' + QUOTENAME(A.[name])
        FROM
            sys.columns AS A
            INNER JOIN sys.foreign_key_columns AS B ON B.parent_column_id = A.column_id AND B.parent_object_id = A.[object_id]
        WHERE
            B.constraint_object_id = D.[object_id]
        ORDER BY
            B.constraint_column_id
        FOR XML PATH(N''), TYPE).value(N'.[1]', N'nvarchar(max)'), 1, 1, N'') + ') REFERENCES ' + QUOTENAME(F.[name]) + '.' + QUOTENAME(E.[name]) + '(' + 
    STUFF((
        SELECT
            ',' + QUOTENAME(C.[name])
        FROM
            sys.columns AS C
            INNER JOIN sys.foreign_key_columns AS fkc ON fkc.referenced_column_id = C.column_id AND fkc.referenced_object_id = C.[object_id]
        WHERE
            fkc.constraint_object_id = D.[object_id]
        ORDER BY
            fkc.constraint_column_id
        FOR XML PATH(N''), TYPE
    ).value(N'.[1]', N'nvarchar(max)'), 1, 1, N''
                                                                                                                                                                                                                   ) + ');'
FROM
    sys.foreign_keys AS D
    JOIN sys.tables AS E ON D.referenced_object_id = E.[object_id]
    JOIN sys.schemas AS F ON E.[schema_id] = F.[schema_id]
    JOIN sys.tables AS G ON D.parent_object_id = G.[object_id]
    JOIN sys.schemas AS H ON G.[schema_id] = H.[schema_id]
    JOIN #Tabelas AS I ON G.[object_id] = I.table_object_id
WHERE
    E.is_ms_shipped = 0
    AND G.is_ms_shipped = 0


SELECT
    @CmdCreateIndex += (CASE WHEN B.is_unique_constraint = 0 AND B.is_primary_key = 0 THEN 'CREATE ' + (CASE WHEN B.is_unique = 1 THEN 'UNIQUE ' ELSE '' END) + B.[type_desc] COLLATE DATABASE_DEFAULT + ' INDEX [' + B.[name] + '] ON [' + SCHEMA_NAME(C.[schema_id]) + '].[' + C.[name] + '] (' + E.KeyColumns + ')' + ISNULL(' INCLUDE (' + I.IncludedColumns + ')', '') + ISNULL(' WHERE ' + B.filter_definition, '') + ' WITH (' + CASE WHEN B.is_padded = 1 THEN 'PAD_INDEX = ON' ELSE 'PAD_INDEX = OFF' END + ', ' + 'FILLFACTOR = ' + CONVERT(VARCHAR(5), CASE WHEN B.fill_factor = 0 THEN 100 ELSE B.fill_factor END) + ', ' +
    'SORT_IN_TEMPDB = OFF, ' + (CASE WHEN B.[ignore_dup_key] = 1 THEN 'IGNORE_DUP_KEY = ON' ELSE 'IGNORE_DUP_KEY = OFF' END) + ', ' + (CASE WHEN F.no_recompute = 0 THEN 'STATISTICS_NORECOMPUTE = OFF' ELSE 'STATISTICS_NORECOMPUTE = ON' END) + ', ' +
    'ONLINE = OFF, DATA_COMPRESSION = PAGE, ' + (CASE WHEN B.[allow_row_locks] = 1 THEN 'ALLOW_ROW_LOCKS = ON' ELSE 'ALLOW_ROW_LOCKS = OFF' END) + ', ' + (CASE WHEN B.[allow_page_locks] = 1 THEN 'ALLOW_PAGE_LOCKS = ON' ELSE 'ALLOW_PAGE_LOCKS = OFF' END) + ') ON [' + G.[name] + ']; ' ELSE '' END),

    @CmdCreateIndexConstraint += (CASE WHEN B.is_unique_constraint = 1 OR B.is_primary_key = 1 THEN 'ALTER TABLE [' + A.[schema_name] + '].[' + A.table_name + '] ADD CONSTRAINT [' + A.index_name + '] ' + (CASE WHEN B.is_primary_key = 1 THEN 'PRIMARY KEY ' ELSE '' END) + (CASE WHEN B.is_unique = 1 AND B.is_primary_key = 0 THEN 'UNIQUE ' ELSE '' END) + B.[type_desc] COLLATE DATABASE_DEFAULT + ' (' + E.KeyColumns + ')' + ISNULL(' INCLUDE (' + I.IncludedColumns + ')', '') + ISNULL(' WHERE ' + B.filter_definition, '') + ' WITH (' + CASE WHEN B.is_padded = 1 THEN 'PAD_INDEX = ON' ELSE 'PAD_INDEX = OFF' END + ', ' + 'FILLFACTOR = ' + CONVERT(VARCHAR(5), CASE WHEN B.fill_factor = 0 THEN 100 ELSE B.fill_factor END) + ', ' +
    'SORT_IN_TEMPDB = OFF, ' + (CASE WHEN B.[ignore_dup_key] = 1 THEN 'IGNORE_DUP_KEY = ON' ELSE 'IGNORE_DUP_KEY = OFF' END) + ', ' + (CASE WHEN F.no_recompute = 0 THEN 'STATISTICS_NORECOMPUTE = OFF' ELSE 'STATISTICS_NORECOMPUTE = ON' END) + ', ' +
    'ONLINE = OFF, DATA_COMPRESSION = PAGE, ' + (CASE WHEN B.[allow_row_locks] = 1 THEN 'ALLOW_ROW_LOCKS = ON' ELSE 'ALLOW_ROW_LOCKS = OFF' END) + ', ' + (CASE WHEN B.[allow_page_locks] = 1 THEN 'ALLOW_PAGE_LOCKS = ON' ELSE 'ALLOW_PAGE_LOCKS = OFF' END) + ') ON [' + G.[name] + ']; ' ELSE '' END)
FROM
    #Indices_Alteracao	AS A
    JOIN sys.indexes    AS B ON A.table_object_id = B.[object_id] AND A.index_name = B.[name] COLLATE DATABASE_DEFAULT
    JOIN sys.tables     AS C ON C.[object_id] = B.[object_id]
    JOIN sys.indexes	AS D ON B.[object_id] = D.[object_id] AND B.index_id = D.index_id
    JOIN
    (
        SELECT
            *
        FROM
        (
            SELECT
                X3.[object_id],
                X3.index_id,
                STUFF((
                    SELECT
                        ', [' + X2.[name] + ']' + CASE WHEN MAX(CONVERT(INT, X1.is_descending_key)) = 1 THEN ' DESC' ELSE '' END
                    FROM
                        sys.index_columns AS X1
                        JOIN sys.columns  AS X2 ON X2.[object_id] = X1.[object_id] AND X2.column_id = X1.column_id AND X1.is_included_column = 0
                    WHERE
                        X1.[object_id] = X3.[object_id]
                        AND X1.index_id = X3.index_id
                    GROUP BY
                        X1.[object_id],
                        X2.[name],
                        X1.index_id
                    ORDER BY
                        MAX(X1.key_ordinal)
                    FOR XML PATH('')
                    ),   1, 2, ''
                ) AS KeyColumns
            FROM
                sys.index_columns AS X3
            GROUP BY
                X3.[object_id],
                X3.index_id
        ) AS X4
    )                    AS E ON B.[object_id] = E.[object_id] AND B.index_id = E.index_id
    JOIN sys.stats       AS F ON F.[object_id] = B.[object_id] AND F.stats_id = B.index_id
    JOIN sys.data_spaces AS G ON B.data_space_id = G.data_space_id
    JOIN sys.filegroups  AS H ON B.data_space_id = H.data_space_id
    LEFT JOIN
    (
        SELECT
            *
        FROM
        (
            SELECT
                X3.[object_id],
                X3.index_id,
                STUFF((
                        SELECT
                            ', [' + X2.[name] + ']'
                        FROM
                            sys.index_columns AS X1
                            JOIN sys.columns  AS X2 ON X2.[object_id] = X1.[object_id] AND X2.column_id = X1.column_id AND X1.is_included_column = 1
                        WHERE
                            X1.[object_id] = X3.[object_id]
                            AND X1.index_id = X3.index_id
                        GROUP BY
                            X1.[object_id],
                            X2.[name],
                            X1.index_id
                        FOR XML PATH('')
                    ),   1, 2, ''
                ) AS IncludedColumns
            FROM
                sys.index_columns AS X3  
            GROUP BY
                X3.[object_id],
                X3.index_id
        ) AS tmp1
        WHERE
            IncludedColumns IS NOT NULL
    ) AS I ON I.[object_id] = B.[object_id] AND I.index_id = B.index_id


SELECT
    @CmdAlterTable += 'ALTER TABLE [' + [schema_name] + '].[' + table_name + '] ALTER COLUMN [' + [name] + '] ' + UPPER([type_name]) + (CASE WHEN UPPER([type_name]) <> 'SYSNAME' THEN '(' + (CASE WHEN A.max_length > 0 THEN CAST(A.max_length AS VARCHAR(10)) ELSE 'MAX' END) + ')' ELSE '' END) + ' COLLATE ' + @Collation_Desejado + ' ' + (CASE WHEN A.is_sparse = 1 THEN 'SPARSE ' ELSE '' END) + (CASE WHEN A.is_nullable = 1 THEN 'NULL' ELSE 'NOT NULL' END) + ';'
FROM
    #Colunas A

    


-------------------------------------------------------------------------
-- Executa os scripts
-------------------------------------------------------------------------

PRINT '---------- Dropando as contraints FK'
IF (NULLIF(LTRIM(RTRIM(@CmdDropFK)), '') IS NOT NULL) PRINT @CmdDropFK
IF (@Fl_Debug = 0) EXEC(@CmdDropFK)

PRINT '---------- Desativando as Check Contraints'
IF (NULLIF(LTRIM(RTRIM(@CmdDisableCK)), '') IS NOT NULL) PRINT @CmdDisableCK
IF (@Fl_Debug = 0) EXEC(@CmdDisableCK)

PRINT '---------- Dropando os índices'
IF (NULLIF(LTRIM(RTRIM(@CmdDropIndex)), '') IS NOT NULL) PRINT @CmdDropIndex
IF (@Fl_Debug = 0) EXEC(@CmdDropIndex)

PRINT '---------- Dropando os índices em constraints'
IF (NULLIF(LTRIM(RTRIM(@CmdDropIndexConstraint)), '') IS NOT NULL) PRINT @CmdDropIndexConstraint
IF (@Fl_Debug = 0) EXEC(@CmdDropIndexConstraint)

PRINT '---------- Alterando a tabela'
IF (NULLIF(LTRIM(RTRIM(@CmdAlterTable)), '') IS NOT NULL) PRINT @CmdAlterTable
IF (@Fl_Debug = 0) EXEC(@CmdAlterTable)

PRINT '---------- Reativando as constraints'
IF (NULLIF(LTRIM(RTRIM(@CmdEnableCK)), '') IS NOT NULL) PRINT @CmdEnableCK
IF (@Fl_Debug = 0) EXEC(@CmdEnableCK)

PRINT '---------- Recriando os índices'
IF (NULLIF(LTRIM(RTRIM(@CmdCreateIndex)), '') IS NOT NULL) PRINT @CmdCreateIndex
IF (@Fl_Debug = 0) EXEC(@CmdCreateIndex)

PRINT '---------- Recriando os índices em constraints'
IF (NULLIF(LTRIM(RTRIM(@CmdCreateIndexConstraint)), '') IS NOT NULL) PRINT @CmdCreateIndexConstraint
IF (@Fl_Debug = 0) EXEC(@CmdCreateIndexConstraint)

PRINT '---------- Recriando as FKs'
IF (NULLIF(LTRIM(RTRIM(@CmdCreateFK)), '') IS NOT NULL) PRINT @CmdCreateFK
IF (@Fl_Debug = 0) EXEC(@CmdCreateFK)

100

101

102

103

104

105

106

107

108

109

110

111

112

113

114

115

116

117

118

119

120

121

122

123

124

125

126

127

128

129

130

131

132

133

134

135

136

137

138

139

140

141

142

143

144

145

146

147

148

149

150

151

152

153

154

155

156

157

158

159

160

161

162

163

164

165

166

167

168

169

170

171

172

173

174

175

176

177

178

179

180

181

182

183

184

185

186

187

188

189

190

191

192

193

194

195

196

197

198

199

200

201

202

203

204

205

206

207

208

209

210

211

212

213

214

215

216

217

218

219

220

221

222

223

224

225

226

227

228

229

230

231

232

233

234

235

236

237

238

239

240

241

242

243

244

245

246

247

248

249

250

251

252

253

254

255

256

257

258

259

260

261

262

263

264

265

266

267

268

269

270

271

272

273

274

275

276

277

278

279

280

281

282

283

284

285

286

287

288

289

290

291

292

293

294

295

296

297

298

299

300

301

302

303

304

305

SET NOCOUNT ON

-------------------------------------------------------------------------

-- Identifica as colunas com collation diferente do desejado

-------------------------------------------------------------------------

DECLARE

@Collation_Desejado VARCHAR(100) = 'SQL_Latin1_General_CP1_CI_AI' -- SELECT * FROM sys.fn_helpcollations()

IF (OBJECT_ID('tempdb..#Colunas') IS NOT NULL) DROP TABLE #Colunas

SELECT

B.[object_id] AS table_object_id,

B.[name] AS table_name,

B.[schema_id] AS table_schema_id,

C.[name] AS [type_name],

D.[name] AS [schema_name],

D.[schema_id],

A.*,

C.system_type_id AS type_system_type_id,

C.user_type_id AS type_user_type_id,

C.collation_name AS type_collation_name,

E.collation_name AS [database_default_collation]

INTO

#Colunas

FROM

sys.columns AS A

JOIN sys.tables AS B ON A.[object_id] = B.[object_id]

JOIN sys.types AS C ON A.user_type_id = C.user_type_id

JOIN sys.schemas AS D ON B.[schema_id] = D.[schema_id]

JOIN sys.databases AS E ON E.database_id = DB_ID()

WHERE

B.is_ms_shipped = 0

AND A.collation_name <> @Collation_Desejado

IF (OBJECT_ID('tempdb..#Tabelas') IS NOT NULL) DROP table #Tabelas

SELECT DISTINCT

table_object_id,

table_name,

table_schema_id,

[schema_name]

INTO

#Tabelas

FROM

#Colunas

-------------------------------------------------------------------------

-- Identifica os índices que utilizam essas colunas

-------------------------------------------------------------------------

IF (OBJECT_ID('tempdb..#Indices') IS NOT NULL) DROP TABLE #Indices

SELECT

A.*,

B.[name] AS index_name,

B.[type],

B.[type_desc],

B.is_unique,

B.is_primary_key,

B.is_unique_constraint,

B.is_disabled

INTO

#Indices

FROM

#Colunas A

JOIN sys.indexes B ON A.table_object_id = B.[object_id]

WHERE

EXISTS(SELECT NULL FROM sys.index_columns X WHERE X.index_id = B.index_id AND X.column_id = A.column_id)

IF (OBJECT_ID('tempdb..#Indices_Alteracao') IS NOT NULL) DROP TABLE #Indices_Alteracao

SELECT DISTINCT

table_object_id,

table_schema_id,

table_name,

[schema_name],

type_collation_name,

database_default_collation,

index_name,

is_unique_constraint,

is_primary_key

INTO

#Indices_Alteracao

FROM

#Indices

-------------------------------------------------------------------------

-- Gera os scripts

-------------------------------------------------------------------------

DECLARE

@CmdAlterTable VARCHAR(MAX) = '',

@CmdDropIndex VARCHAR(MAX) = '',

@CmdCreateIndex VARCHAR(MAX) = '',

@CmdDropFK VARCHAR(MAX) = '',

@CmdCreateFK VARCHAR(MAX) = '',

@CmdDropIndexConstraint VARCHAR(MAX) = '',

@CmdCreateIndexConstraint VARCHAR(MAX) = '',

@CmdDisableCK VARCHAR(MAX) = '',

@CmdEnableCK VARCHAR(MAX) = '',

@Fl_Debug BIT = 1

SELECT

@CmdDropIndex += (CASE WHEN is_unique_constraint = 0 AND is_primary_key = 0 THEN 'DROP INDEX [' + index_name + '] ON [' + [schema_name] + '].[' + table_name + ']; ' ELSE '' END),

@CmdDisableCK += 'ALTER TABLE [' + [schema_name] + '].[' + table_name + '] NOCHECK CONSTRAINT ALL; ',

@CmdEnableCK += 'ALTER TABLE [' + [schema_name] + '].[' + table_name + '] CHECK CONSTRAINT ALL; '

FROM

#Indices_Alteracao

-------------------------------------------------------------------------

-- Identifica AS FK's que utilizam essas colunas

-------------------------------------------------------------------------

SELECT

@CmdDropFK += N'ALTER TABLE ' + QUOTENAME(C.[name]) + '.' + QUOTENAME(B.[name]) + ' DROP CONSTRAINT ' + QUOTENAME(A.[name]) + ';'

FROM

sys.foreign_keys AS A

JOIN sys.tables AS B ON A.parent_object_id = B.[object_id]

JOIN sys.schemas AS C ON B.[schema_id] = C.[schema_id]

JOIN #Tabelas AS D ON B.[object_id] = D.table_object_id

SELECT

@CmdCreateFK += N'ALTER TABLE ' + QUOTENAME(H.[name]) + '.' + QUOTENAME(G.[name]) + ' ADD CONSTRAINT ' + QUOTENAME(D.[name]) + ' FOREIGN KEY (' +

STUFF((

SELECT

',' + QUOTENAME(A.[name])

FROM

sys.columns AS A

INNER JOIN sys.foreign_key_columns AS B ON B.parent_column_id = A.column_id AND B.parent_object_id = A.[object_id]

WHERE

B.constraint_object_id = D.[object_id]

ORDER BY

B.constraint_column_id

FOR XML PATH(N''), TYPE).value(N'.[1]', N'nvarchar(max)'), 1, 1, N'') + ') REFERENCES ' + QUOTENAME(F.[name]) + '.' + QUOTENAME(E.[name]) + '(' +

STUFF((

SELECT

',' + QUOTENAME(C.[name])

FROM

sys.columns AS C

INNER JOIN sys.foreign_key_columns AS fkc ON fkc.referenced_column_id = C.column_id AND fkc.referenced_object_id = C.[object_id]

WHERE

fkc.constraint_object_id = D.[object_id]

ORDER BY

fkc.constraint_column_id

FOR XML PATH(N''), TYPE

).value(N'.[1]', N'nvarchar(max)'), 1, 1, N''

) + ');'

FROM

sys.foreign_keys AS D

JOIN sys.tables AS E ON D.referenced_object_id = E.[object_id]

JOIN sys.schemas AS F ON E.[schema_id] = F.[schema_id]

JOIN sys.tables AS G ON D.parent_object_id = G.[object_id]

JOIN sys.schemas AS H ON G.[schema_id] = H.[schema_id]

JOIN #Tabelas AS I ON G.[object_id] = I.table_object_id

WHERE

E.is_ms_shipped = 0

AND G.is_ms_shipped = 0

SELECT

FROM

#Indices_Alteracao AS A

JOIN sys.indexes AS B ON A.table_object_id = B.[object_id] AND A.index_name = B.[name] COLLATE DATABASE_DEFAULT

JOIN sys.tables AS C ON C.[object_id] = B.[object_id]

JOIN sys.indexes AS D ON B.[object_id] = D.[object_id] AND B.index_id = D.index_id

JOIN

(

SELECT

FROM

(

SELECT

X3.[object_id],

X3.index_id,

STUFF((

SELECT

', [' + X2.[name] + ']' + CASE WHEN MAX(CONVERT(INT, X1.is_descending_key)) = 1 THEN ' DESC' ELSE '' END

FROM

sys.index_columns AS X1

JOIN sys.columns AS X2 ON X2.[object_id] = X1.[object_id] AND X2.column_id = X1.column_id AND X1.is_included_column = 0

WHERE

X1.[object_id] = X3.[object_id]

AND X1.index_id = X3.index_id

GROUP BY

X1.[object_id],

X2.[name],

X1.index_id

ORDER BY

MAX(X1.key_ordinal)

FOR XML PATH('')

), 1, 2, ''

) AS KeyColumns

FROM

sys.index_columns AS X3

GROUP BY

X3.[object_id],

X3.index_id

) AS X4

) AS E ON B.[object_id] = E.[object_id] AND B.index_id = E.index_id

JOIN sys.stats AS F ON F.[object_id] = B.[object_id] AND F.stats_id = B.index_id

JOIN sys.data_spaces AS G ON B.data_space_id = G.data_space_id

JOIN sys.filegroups AS H ON B.data_space_id = H.data_space_id

LEFT JOIN

(

SELECT

FROM

(

SELECT

X3.[object_id],

X3.index_id,

STUFF((

SELECT

', [' + X2.[name] + ']'

FROM

sys.index_columns AS X1

JOIN sys.columns AS X2 ON X2.[object_id] = X1.[object_id] AND X2.column_id = X1.column_id AND X1.is_included_column = 1

WHERE

X1.[object_id] = X3.[object_id]

AND X1.index_id = X3.index_id

GROUP BY

X1.[object_id],

X2.[name],

X1.index_id

FOR XML PATH('')

), 1, 2, ''

) AS IncludedColumns

FROM

sys.index_columns AS X3

GROUP BY

X3.[object_id],

X3.index_id

) AS tmp1

WHERE

IncludedColumns IS NOT NULL

) AS I ON I.[object_id] = B.[object_id] AND I.index_id = B.index_id

SELECT

@CmdAlterTable += 'ALTER TABLE [' + [schema_name] + '].[' + table_name + '] ALTER COLUMN [' + [name] + '] ' + UPPER([type_name]) + (CASE WHEN UPPER([type_name]) <> 'SYSNAME' THEN '(' + (CASE WHEN A.max_length > 0 THEN CAST(A.max_length AS VARCHAR(10)) ELSE 'MAX' END) + ')' ELSE '' END) + ' COLLATE ' + @Collation_Desejado + ' ' + (CASE WHEN A.is_sparse = 1 THEN 'SPARSE ' ELSE '' END) + (CASE WHEN A.is_nullable = 1 THEN 'NULL' ELSE 'NOT NULL' END) + ';'

FROM

#Colunas A

-------------------------------------------------------------------------

-- Executa os scripts

-------------------------------------------------------------------------

PRINT '---------- Dropando as contraints FK'

IF (NULLIF(LTRIM(RTRIM(@CmdDropFK)), '') IS NOT NULL) PRINT @CmdDropFK

IF (@Fl_Debug = 0) EXEC(@CmdDropFK)

PRINT '---------- Desativando as Check Contraints'

IF (NULLIF(LTRIM(RTRIM(@CmdDisableCK)), '') IS NOT NULL) PRINT @CmdDisableCK

IF (@Fl_Debug = 0) EXEC(@CmdDisableCK)

PRINT '---------- Dropando os índices'

IF (NULLIF(LTRIM(RTRIM(@CmdDropIndex)), '') IS NOT NULL) PRINT @CmdDropIndex

IF (@Fl_Debug = 0) EXEC(@CmdDropIndex)

PRINT '---------- Dropando os índices em constraints'

IF (NULLIF(LTRIM(RTRIM(@CmdDropIndexConstraint)), '') IS NOT NULL) PRINT @CmdDropIndexConstraint

IF (@Fl_Debug = 0) EXEC(@CmdDropIndexConstraint)

PRINT '---------- Alterando a tabela'

IF (NULLIF(LTRIM(RTRIM(@CmdAlterTable)), '') IS NOT NULL) PRINT @CmdAlterTable

IF (@Fl_Debug = 0) EXEC(@CmdAlterTable)

PRINT '---------- Reativando as constraints'

IF (NULLIF(LTRIM(RTRIM(@CmdEnableCK)), '') IS NOT NULL) PRINT @CmdEnableCK

IF (@Fl_Debug = 0) EXEC(@CmdEnableCK)

PRINT '---------- Recriando os índices'

IF (NULLIF(LTRIM(RTRIM(@CmdCreateIndex)), '') IS NOT NULL) PRINT @CmdCreateIndex

IF (@Fl_Debug = 0) EXEC(@CmdCreateIndex)

PRINT '---------- Recriando os índices em constraints'

IF (NULLIF(LTRIM(RTRIM(@CmdCreateIndexConstraint)), '') IS NOT NULL) PRINT @CmdCreateIndexConstraint

IF (@Fl_Debug = 0) EXEC(@CmdCreateIndexConstraint)

PRINT '---------- Recriando as FKs'

IF (NULLIF(LTRIM(RTRIM(@CmdCreateFK)), '') IS NOT NULL) PRINT @CmdCreateFK

IF (@Fl_Debug = 0) EXEC(@CmdCreateFK)

Result:

If the script identifies many columns to change and PRINT output is getting cut off because of the size of the generated strings, you can use the fncSplitTexto function, which I made available in the post. How to wrap a string in a substring table using a delimiter in SQL Server and replace the end of the script with this snippet here:

-------------------------------------------------------------------------
-- Executa os scripts
-------------------------------------------------------------------------

PRINT '---------- Dropando as contraints FK'
IF (NULLIF(LTRIM(RTRIM(@CmdDropFK)), '') IS NOT NULL) SELECT * FROM dirceuresende.dbo.fncSplitTexto(@CmdDropFK, ';')
IF (@Fl_Debug = 0) EXEC(@CmdDropFK)

PRINT '---------- Desativando as Check Contraints'
IF (NULLIF(LTRIM(RTRIM(@CmdDisableCK)), '') IS NOT NULL) SELECT * FROM dirceuresende.dbo.fncSplitTexto(@CmdDisableCK, ';')
IF (@Fl_Debug = 0) EXEC(@CmdDisableCK)

PRINT '---------- Dropando os índices'
IF (NULLIF(LTRIM(RTRIM(@CmdDropIndex)), '') IS NOT NULL) SELECT * FROM dirceuresende.dbo.fncSplitTexto(@CmdDropIndex, ';')
IF (@Fl_Debug = 0) EXEC(@CmdDropIndex)

PRINT '---------- Dropando os índices em constraints'
IF (NULLIF(LTRIM(RTRIM(@CmdDropIndexConstraint)), '') IS NOT NULL) SELECT * FROM dirceuresende.dbo.fncSplitTexto(@CmdDropIndexConstraint, ';')
IF (@Fl_Debug = 0) EXEC(@CmdDropIndexConstraint)

PRINT '---------- Alterando a tabela'
IF (NULLIF(LTRIM(RTRIM(@CmdAlterTable)), '') IS NOT NULL) SELECT * FROM dirceuresende.dbo.fncSplitTexto(@CmdAlterTable, ';')
IF (@Fl_Debug = 0) EXEC(@CmdAlterTable)

PRINT '---------- Reativando as constraints'
IF (NULLIF(LTRIM(RTRIM(@CmdEnableCK)), '') IS NOT NULL) SELECT * FROM dirceuresende.dbo.fncSplitTexto(@CmdEnableCK, ';')
IF (@Fl_Debug = 0) EXEC(@CmdEnableCK)

PRINT '---------- Recriando os índices'
IF (NULLIF(LTRIM(RTRIM(@CmdCreateIndex)), '') IS NOT NULL) SELECT * FROM dirceuresende.dbo.fncSplitTexto(@CmdCreateIndex, ';')
IF (@Fl_Debug = 0) EXEC(@CmdCreateIndex)

PRINT '---------- Recriando os índices em constraints'
IF (NULLIF(LTRIM(RTRIM(@CmdCreateIndexConstraint)), '') IS NOT NULL) SELECT * FROM dirceuresende.dbo.fncSplitTexto(@CmdCreateIndexConstraint, ';')
IF (@Fl_Debug = 0) EXEC(@CmdCreateIndexConstraint)

PRINT '---------- Recriando as FKs'
IF (NULLIF(LTRIM(RTRIM(@CmdCreateFK)), '') IS NOT NULL) SELECT * FROM dirceuresende.dbo.fncSplitTexto(@CmdCreateFK, ';')
IF (@Fl_Debug = 0) EXEC(@CmdCreateFK)

-------------------------------------------------------------------------

-- Executa os scripts

-------------------------------------------------------------------------

PRINT '---------- Dropando as contraints FK'

IF (NULLIF(LTRIM(RTRIM(@CmdDropFK)), '') IS NOT NULL) SELECT * FROM dirceuresende.dbo.fncSplitTexto(@CmdDropFK, ';')

IF (@Fl_Debug = 0) EXEC(@CmdDropFK)

PRINT '---------- Desativando as Check Contraints'

IF (NULLIF(LTRIM(RTRIM(@CmdDisableCK)), '') IS NOT NULL) SELECT * FROM dirceuresende.dbo.fncSplitTexto(@CmdDisableCK, ';')

IF (@Fl_Debug = 0) EXEC(@CmdDisableCK)

PRINT '---------- Dropando os índices'

IF (NULLIF(LTRIM(RTRIM(@CmdDropIndex)), '') IS NOT

Other Languages

Subscribe to a blog by email

Blog Views

Categories

Azure Banking Admin Course

SQL Server Security Course

My Certifications

FREE check-up of your BD!

Posts Archive

Recent Posts

SQL Server - How to standardize the Collation of all columns in the database

What is a COLLATION?

How to identify the default collation?

Test base generation

How to standardize the collation of all columns?