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

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 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)

Result:

I hope you enjoyed this post and see you next time!
PS: Remember to back up and test ENOUGH if you plan to apply this script in Production.

If you have questions or problems, leave it here in the comments.

tags: collation sql sql server

Virgil said:

1 April 2021 20 gies: 34

Error executing the penultimate script:

Msg 7738, Level 16, State 2, Line 1
Cannot enable compression for object 'xyz'. Only SQL Server Enterprise Edition supports compression.

Reply
Sergio Moreno said:

25 January 2021 to 13: 07

Hello friend, congratulations on your class... fantastic. Now I question: and when you can't change the collate but need to use, for example, queries in two different databases (tables with JOIN), the collates are different... you can just set the collate, make the query, and leave it without changing ???

Reply
Edgar Mauricio SL said:

22 October 2020 to 11: 04

Dirceu,
If in the section “Run the scripts” I comment on the EXEC lines -> “IF (@Fl_Debug = 0) EXEC (@CmdDropFK)” can I see all the ALTER TABLE, DROP etc commands without being executed? This is because I have a database with many nonstandard columns in the base but I would like to arrange it by groups of related tables, and not all at once. So copy and paste the generated commands only the ones I need.

Reply
EVANDRO SIMENES said:

25 from 2020 from September to 14: 34

Good afternoon
Thank you very much for the script, it helped a lot.
Here are some notes to help those in need:

If your database has many foreign keys and you try to run this script for only a few tables, the changes may not be made, since there may be a foreign key related to the executed table, but belonging to a table from which it was not related to script processing.
- Always run the script for the entire database.

If you are running this script on a sqlserver express, the parameters below must be removed from the commands for creating the indexes / constraints:
If it is not removed, the indexes will not be recreated because these parameters are only available for the enterprise version of sqlserver.
- DATA_COMPRESSION = PAGE
- SORT_IN_TEMPDB = OFF

If your database is very large, the varchar (max) variables will not support concatenating so much information and the string will be cut when it reaches 8000 characters… .To solve this question, I created a table with a field called command and another called order, and I changed the script to instead of concatenating the variables, insert command by command in that table according to their order of execution and then after entering all the commands, I created another script to scan command by command of that table and execute it.

Reply
Mark said:

26 January 2020 to 18: 00

Hi Dirceu.

I thank you for your time dedicated to disseminating knowledge.

I performed this procedure but unfortunately it didn't work.

The script even changed the SQL SERVER collation but DATABASE I want to change the tables did not work.

SQL version

Microsoft SQL Server 2012 (SP4) (KB4018073)

Do you have any tips?

Reply
Aldemir Fernandes said:

25 April 2019 01 gies: 37

Hi Dirceu.

Thanks so much for this post. But I have a problem. When I run your script, it runs right, but does not change the collation of the columns.

In the result, it points out how the script executed successfully.

Have an idea what it could be?

Thanks so much for the help.

Reply
- Dirceu Resende said:
  
  19 from 2019 from September to 01: 04
  
  Hello Almedir. All right?
  Still having this problem? Anything warns me.
  
  Reply
Kelvin Faria said:

24 from 2018 from September to 15: 22

Hello Dirceu, good afternoon!

The post is really cool, but I have a doubt, in my case there are more than 43 thousand columns that need to be changed, so when running the script the result of the script exceeds the maximum allowed by the “message” field of the management studio and I end up not seeing all the commands.

Have a solution?
Thanks in advance!

Reply
- Dirceu Resende said:
  
  24 from 2018 from September to 19: 33
  
  Hey Kelvin, okay?
  I edited the post to answer your question ..
  
  If you have questions just say it!
  
  Reply
  - Kelvin Faria said:
    
    27 from 2018 from September to 09: 12
    
    Thank you so much Dirceu!
    
    Reply

Other Languages

Subscribe to a blog by email

Blog Views

Categories

Microsoft MVP Data Platform

My Certifications

SQL Server Security Course

Azure Banking Admin Course

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?

You may also like ...

Comments

Leave a Comment Cancel reply

What is a COLLATION?

How to identify the default collation?

Test base generation

How to standardize the collation of all columns?

You may also like ...

How to identify the port used by the instance of SQL Server

SQL Server - Why Not Use SET ANSI_WARNINGS OFF

SQL Server - Error using OPENROWSET: Access to the remote server is denied because no login-mapping exists

Comments

Leave a Comment Cancel reply