Olá pessoal…
Hoje vou mostrar como alterar a Collation do SQL Server para todas as tabelas via script.
Um amigo (um salve ai Rafinha) meu recentemente precisou alterar o collation do banco, e me passou um script interessante que ele encontrou através do link https://gallery.technet.microsoft.com/scriptcenter/Column-Collation-Changer-34321eaa#content.
Basicamente a ideia é criar uma procedure, que varre todos os campos de todas as tabelas e vai alterando o que for necessário. A procedure que será criada se chama ChangeCollation_SP, e como parâmetro basta você passar qual Collation que você quer.
Abaixo o código fonte da procedure.
IF OBJECT_ID('ChangeCollation_SP','P') IS NOT NULL
DROP PROC ChangeCollation_SP
GO
CREATE PROCEDURE ChangeCollation_SP
@ToCollation sysname = 'Latin1_General_BIN'
,@TableName sysname = ''
,@Columnname sysname = ''
,@SchemaName sysname = 'dbo'
,@FromCollation sysname = 'Latin1_General_100_BIN'
,@GenerateScriptsOnly BIT = 0
AS
/*
Parameters
@ToCollation - To which collation columns needs to be moved
@TableName - TableName for which the collation needs to be changed.
Default value is '' and all the tables will be considered for changing the collation.
@Columnname - Columnname for which the collation needs to be changed.
Default value is '' and all the columns will be considered for changing the collation.
@SchemaName - SchemaName for which the collation needs to be changed.
Default value is '' and all the columns will be considered for changing the collation.
@FromCollation - The columns with which collation needs to be changed to the To collation.
Default value is '' and all the columns with all collation will be considered for changing the collation.
@GenerateScriptsOnly - Generates the scripts only for changing the collation.
Default value is 1 and generates only script. When changed to 0 the collation change will be applied
*/
SET NOCOUNT ON
DECLARE @DBname sysname
DECLARE @SchemaID INT
DECLARE @TableID INT
DECLARE @IndexID INT
DECLARE @isPrimaryKey BIT
DECLARE @IndexType INT
DECLARE @CreateSQL VARCHAR(MAX)
DECLARE @IndexColsSQL VARCHAR(MAX)
DECLARE @WithSQL VARCHAR(MAX)
DECLARE @IncludeSQL VARCHAR(MAX)
DECLARE @WhereSQL VARCHAR(MAX)
DECLARE @sql VARCHAR(MAX)
DECLARE @DropSQL VARCHAR(MAX)
DECLARE @ExistsSQL VARCHAR(MAX)
DECLARE @Indexname sysname
DECLARE @TblSchemaName sysname
IF OBJECT_ID('#ChangeCollationTables','U') IS NOT NULL
BEGIN
DROP TABLE #ChangeCollationTables
END
CREATE TABLE #ChangeCollationTables
(
SchemaID INT
,SchemaName sysname
,TableID INT
,TableName sysname
,Processed BIT
,RunRank INT NULL
)
IF OBJECT_ID('#ChangeCollationColumns','U') IS NOT NULL
BEGIN
DROP TABLE #ChangeCollationColumns
END
CREATE TABLE #ChangeCollationColumns
(
SchemaID INT
,SchemaName sysname
,TableID INT
,TableName sysname
,ColumnID INT
,Col sysname
,AlterScript VARCHAR(MAX) NULL
)
IF OBJECT_ID('#ChangeCollationObjectsBackupTbl','U') IS NOT NULL
BEGIN
DROP TABLE #ChangeCollationObjectsBackupTbl
END
CREATE TABLE #ChangeCollationObjectsBackupTbl
(
BackupID INT IDENTITY(1,1)
,SchemaID INT
,TableID INT
,ObjectName sysname
,ObjectType VARCHAR(50)
,CreateScript VARCHAR(MAX) NULL
,DropScript VARCHAR(MAX) NULL
,ExistsScript VARCHAR(MAX) NULL
,Processed BIT NULL
)
---------------------------------------------------------------------------------------------------------------------------
-- Get List of columns needs the collation to be changed
---------------------------------------------------------------------------------------------------------------------------
INSERT INTO #ChangeCollationColumns
SELECT SCH.schema_id
,SCH.name
,ST.object_id
,ST.name
,SC.column_id
,SC.name
,'ALTER TABLE ' + QUOTEname(SCH.name) + '.' + QUOTEname(ST.name)
+ ' ALTER COLUMN ' + QUOTEname(SC.name) + ' '
+ STY.name +
+ CASE
WHEN STY.name IN ('char','varchar','nchar','nvarchar') AND SC.max_length = -1 THEN '(max)'
WHEN STY.name IN ('char','varchar') AND SC.max_length <> -1 THEN '(' + CONVERT(VARCHAR(5),SC.max_length) + ')'
WHEN STY.name IN ('nchar','nvarchar') AND SC.max_length <> -1 THEN '(' + CONVERT(VARCHAR(5),SC.max_length/2) + ')'
ELSE ''
END
+ ' COLLATE ' + @ToCollation
+ CASE SC.is_nullable
WHEN 0 THEN ' NOT NULL'
ELSE ' NULL'
END
FROM sys.tables ST
JOIN sys.schemas SCH
ON SCH.schema_id = ST.schema_id
JOIN sys.columns SC
ON SC.object_id = ST.object_id
JOIN sys.types STY
ON STY.system_type_id = SC.system_type_id
AND STY.user_type_id = SC.user_type_id
WHERE SCH.name = CASE
WHEN @SchemaName = '' THEN SCH.name
ELSE @SchemaName
END
AND ST.name = CASE
WHEN @TableName = '' THEN ST.name
ELSE @TableName
END
AND SC.name = CASE
WHEN @Columnname = '' THEN SC.name
ELSE @Columnname
END
AND SC.collation_name = CASE
WHEN @FromCollation = '' THEN SC.collation_name
ELSE @FromCollation
END
AND STY.name in ('char', 'varchar', 'text', 'nchar', 'nvarchar', 'ntext')
AND SC.is_computed = 0
-----------------------------------------------------------------------------------------------------------------------------
-- Get the list of tables need to be processed
-----------------------------------------------------------------------------------------------------------------------------
INSERT INTO #ChangeCollationTables
SELECT DISTINCT SchemaID
,SchemaName
,TableID
,TableName
,convert(bit,0) as Processed
,0 as RunRank
FROM #ChangeCollationColumns;
----------------------------------------------------------------------------------------------------------------------------
-- Order by foreignkey
-----------------------------------------------------------------------------------------------------------------------------
WITH fkey (ReferencingObjectid,ReferencingSchemaid,ReferencingTableName,PrimarykeyObjectid,PrimarykeySchemaid,PrimarykeyTableName,level)AS
(
SELECT DISTINCT
convert(int,null)
,convert(INT,null)
,convert(sysname,null)
,ST.object_id
,ST.schema_id
,ST.name
,0 as level
FROM sys.tables ST
LEFT JOIN sys.foreign_keys SF
ON SF.parent_object_id = ST.object_id
WHERE SF.object_id IS NULL
UNION ALL
SELECT STP.object_id
,STP.schema_id
,STP.name
,STC.object_id
,STC.schema_id
,STC.name
,f.level+1 as level
FROM sys.foreign_keys SFK
JOIN fkey f
ON SFK.referenced_object_id = ISNULL(f.ReferencingObjectid, f.PrimarykeyObjectid)
JOIN sys.tables STP
ON STP.object_id = SFK.parent_object_id
JOIN sys.tables STC
ON STC.object_id = SFK.referenced_object_id
)
UPDATE CT
SET RunRank = F.Lvl
FROM #ChangeCollationTables CT
JOIN
(
SELECT TableId = ISNULL(ReferencingObjectid,PrimarykeyObjectid)
, Lvl = MAX(level)
FROM fkey
GROUP BY ISNULL(ReferencingObjectid,PrimarykeyObjectid)
) F
ON F.TableId = CT.TableID
---------------------------------------------------------------------------------------------------------------------------
-- Backup Views
---------------------------------------------------------------------------------------------------------------------------
INSERT INTO #ChangeCollationObjectsBackupTbl
(SchemaID, TableID, ObjectName, ObjectType, CreateScript, DropScript, ExistsScript, Processed)
SELECT SchemaID, TableID, ObjectName, ObjectType, CreateScript, DropScript, ExistsScript, Processed
FROM
(
SELECT SchemaID=SV.schema_id
,TableID= x.referenced_major_id
,ObjectName=SV.name
,ObjectType='View'
,CreateScript=definition
,DropScript='DROP VIEW ' + QUOTEname(SCH.name) + '.' + QUOTEname(SV.name)
,ExistsScript=' EXISTS (SELECT 1
FROM sys.Views SV
JOIN sys.schemas SCH
ON SV.Schema_id = SCH.Schema_ID
WHERE SV.name =''' + SV.name + '''
AND SCH.name =''' + SCH.name + ''')'
,Processed=0
,Rnk = Rank() Over(Partition by SV.name order by x.referenced_major_id)
FROM sys.views SV
JOIN sys.sql_modules SQM
ON SV.object_id = SQM.object_id
JOIN
(
SELECT DISTINCT SD.object_id,SD.referenced_major_id
FROM sys.sql_dependencies SD
JOIN sys.objects so
ON SD.referenced_major_id = so.object_id
JOIN sys.columns SC
ON SC.object_id = so.object_id
AND SC.column_id = SD.referenced_minor_id
JOIN #ChangeCollationColumns CCC
ON SC.column_id = CCC.ColumnID
AND so.object_id = CCC.TableID
AND so.schema_id = CCC.SchemaID
) x
ON x.object_id = SV.object_id
JOIN sys.schemas SCH
ON SCH.schema_id = SV.schema_id
) Vie
WHERE Vie.Rnk = 1
---------------------------------------------------------------------------------------------------------------------------
-- Backup Computed Columns
---------------------------------------------------------------------------------------------------------------------------
INSERT INTO #ChangeCollationObjectsBackupTbl
(SchemaID, TableID, ObjectName, ObjectType, CreateScript, DropScript, ExistsScript, Processed)
SELECT CCC.SchemaID
,CCC.TableID
,SCC.name
,'ComputedColumn'
,'ALTER TABLE ' + QUOTEname(CCC.SchemaName) + '.' + QUOTEname(CCC.TableName) +
' ADD ' + QUOTEname(SCC.name) + ' as ' + SCC.definition
,'ALTER TABLE ' + QUOTEname(CCC.SchemaName) + '.' + QUOTEname(CCC.TableName) +
' DROP COLUMN ' + QUOTEname(SCC.name)
,'EXISTS (SELECT 1
FROM sys.computed_columns SCC
JOIN sys.tables ST
ON ST.object_id = SCC.object_id
JOIN sys.schemas SCH
ON ST.schema_id = SCH.schema_id
WHERE SCC.name =''' + SCC.name + '''
AND ST.name =''' + CCC.TableName + '''
AND SCH.name =''' + CCC.SchemaName + ''')'
,0
FROM sys.computed_columns SCC
JOIN #ChangeCollationTables CCC
ON SCC.object_id = CCC.TableID
JOIN sys.tables ST
ON ST.object_id = CCC.TableID
AND ST.schema_id = CCC.SchemaID
-----------------------------------------------------------------------------------------------------------------------------
-- Backup Statistics
-----------------------------------------------------------------------------------------------------------------------------
INSERT INTO #ChangeCollationObjectsBackupTbl
(SchemaID, TableID, ObjectName, ObjectType, CreateScript, DropScript, ExistsScript, Processed)
SELECT CCC.SchemaID
,CCC.TableID
,STA.name
,'Statistics'
,NULL
,'DROP STATISTICS' + QUOTEname(CCC.SchemaName) + '.' + QUOTEname(CCC.TableName) + '.' + QUOTEname(STA.name)
,' EXISTS ( SELECT * FROM sys.stats WHERE name = ''' + STA.name + ''' AND object_id = ' + CONVERT(VARCHAR(50),STA.object_id) + ')'
, 0
FROM sys.stats_columns STAC
JOIN #ChangeCollationColumns CCC
ON STAC.object_id = CCC.TableID
AND STAC.column_id = CCC.ColumnID
JOIN sys.stats STA
ON STA.stats_id = STAC.stats_id
AND STA.object_id = STAC.object_id
---------------------------------------------------------------------------------------------------------------------------
-- Backup Indexes
---------------------------------------------------------------------------------------------------------------------------
IF OBJECT_ID('#CollationIDXTable','U') IS NOT NULL
BEGIN
DROP TABLE #CollationIDXTable
END
CREATE TABLE #CollationIDXTable
(
Schema_ID INT
,Object_ID INT
,Index_ID INT
,SchemaName sysname
,TableName sysname
,Indexname sysname
,IsPrimaryKey BIT
,IndexType INT
,CreateScript VARCHAR(MAX) NULL
,DropScript VARCHAR(MAX) NULL
,ExistsScript VARCHAR(MAX) NULL
,Processed BIT NULL
)
INSERT INTO [dbo].[#CollationIDXTable]
(
Schema_ID
,Object_ID
,Index_ID
,SchemaName
,TableName
,Indexname
,IsPrimaryKey
,IndexType
)
SELECT DISTINCT ST.schema_id
,ST.object_id
,SI.index_id
,SCH.name
,ST.name
,SI.name
,SI.is_primary_key
,SI.type
FROM sys.indexes SI
JOIN sys.tables ST
ON SI.object_id = ST.object_id
JOIN sys.schemas SCH
ON SCH.schema_id = ST.schema_id
JOIN sys.index_columns SIC
ON SIC.object_id = SI.object_id
AND SIC.index_id = SI.index_id
--AND SIC.is_included_column = 0
JOIN #ChangeCollationColumns CCC
ON SIC.column_id = CCC.ColumnID
AND ST.object_id = CCC.TableID
AND ST.schema_id = CCC.SchemaID
WHERE SI.type IN (1,2)
UNION
SELECT DISTINCT ST.schema_id
,ST.object_id
,SI.index_id
,SCH.name
,ST.name
,SI.name
,SI.is_primary_key
,SI.type
FROM sys.indexes SI
JOIN sys.tables ST
ON SI.object_id = ST.object_id
JOIN sys.schemas SCH
ON SCH.schema_id = ST.schema_id
JOIN sys.index_columns SIC
ON SIC.object_id = SI.object_id
AND SIC.index_id = SI.index_id
--AND SIC.is_included_column = 0
JOIN #ChangeCollationTables CCC
ON ST.object_id = CCC.TableID
AND ST.schema_id = CCC.SchemaID
JOIN sys.columns SC
ON SC.object_id = CCC.TableID
AND SC.column_id = SIC.column_id
AND SC.is_computed = 1
WHERE SI.Type IN (1,2)
SELECT @CreateSQL = ''
SELECT @IndexColsSQL = ''
SELECT @WithSQL = ''
SELECT @IncludeSQL = ''
SELECT @WhereSQL = ''
WHILE EXISTS(SELECT 1
FROM [dbo].[#CollationIDXTable]
WHERE CreateScript IS NULL)
BEGIN
SELECT TOP 1 @SchemaID = Schema_ID
,@TableID = Object_ID
,@IndexID = Index_ID
,@isPrimaryKey = IsPrimaryKey
,@Indexname = Indexname
,@IndexType = IndexType
,@SchemaName = SchemaName
,@TableName = TableName
FROM [dbo].[#CollationIDXTable]
WHERE CreateScript IS NULL
--AND SchemaName = @SchemaName
--AND TableName = @TableName
ORDER BY Index_ID
SELECT @TblSchemaName = QUOTEname(@SchemaName) + '.' + QUOTEname(@TableName)
IF @isPrimaryKey = 1
BEGIN
SELECT @ExistsSQL = ' EXISTS (SELECT * FROM sys.indexes WHERE object_id = OBJECT_ID(N''' + @TblSchemaName + ''') AND name = N''' + @Indexname + ''')'
SELECT @DropSQL = ' ALTER TABLE '+ @TblSchemaName + ' DROP CONSTRAINT [' + @Indexname + ']'
END
ELSE
BEGIN
SELECT @ExistsSQL = ' EXISTS (SELECT * FROM sys.indexes WHERE object_id = OBJECT_ID(N''' + @TblSchemaName + ''') AND name = N''' + @Indexname + ''')'
SELECT @DropSQL = ' DROP INDEX [' + @Indexname + '] ON ' + @TblSchemaName +
CASE
WHEN @IndexType IN (1,2) THEN ' WITH ( ONLINE = OFF )'
ELSE ''
END
END
IF @IndexType IN (1,2)
BEGIN
SELECT @CreateSQL = CASE
WHEN SI.is_primary_key = 1 THEN
'ALTER TABLE ' + @TblSchemaName + ' ADD CONSTRAINT [' + @Indexname + '] PRIMARY KEY ' + SI.type_desc
WHEN SI.Type IN (1,2) THEN
' CREATE ' + CASE SI.is_unique WHEN 1 THEN ' UNIQUE ' ELSE '' END + SI.type_desc + ' INDEX ' + QUOTEname(SI.name) + ' ON ' + @TblSchemaName
END
,@IndexColsSQL = ( SELECT SC.name + ' '
+ CASE SIC.is_descending_key
WHEN 0 THEN ' ASC '
ELSE 'DESC'
END + ','
FROM sys.index_columns SIC
JOIN sys.columns SC
ON SIC.object_id = SC.object_id
AND SIC.column_id = SC.column_id
WHERE SIC.object_id = SI.object_id
AND SIC.index_id = SI.index_id
AND SIC.is_included_column = 0
ORDER BY SIC.key_ordinal
FOR XML PATH('')
)
,@WithSQL =' WITH (PAD_INDEX = ' + CASE SI.is_padded WHEN 1 THEN 'ON' ELSE 'OFF' END + ',' + CHAR(13) +
' IGNORE_DUP_KEY = ' + CASE SI.ignore_dup_key WHEN 1 THEN 'ON' ELSE 'OFF' END + ',' + CHAR(13) +
' ALLOW_ROW_LOCKS = ' + CASE SI.allow_row_locks WHEN 1 THEN 'ON' ELSE 'OFF' END + ',' + CHAR(13) +
' ALLOW_PAGE_LOCKS = ' + CASE SI.allow_page_locks WHEN 1 THEN 'ON' ELSE 'OFF' END + ',' + CHAR(13) +
CASE SI.type WHEN 2 THEN 'SORT_IN_TEMPDB = OFF,DROP_EXISTING = OFF,' ELSE '' END +
CASE WHEN SI.fill_factor > 0 THEN ' FILLFACTOR = ' + CONVERT(VARCHAR(3),SI.fill_factor) + ',' ELSE '' END +
' STATISTICS_NORECOMPUTE = OFF, SORT_IN_TEMPDB = OFF, ONLINE = OFF) ON ' + QUOTEname(SFG.name)
,@IncludeSQL = ( SELECT QUOTEname(SC.name) + ','
FROM sys.index_columns SIC
JOIN sys.columns SC
ON SIC.object_id = SC.object_id
AND SIC.column_id = SC.column_id
WHERE SIC.object_id = SI.object_id
AND SIC.index_id = SI.index_id
AND SIC.is_included_column = 1
ORDER BY SIC.key_ordinal
FOR XML PATH('')
)
,@WhereSQL = SI.filter_definition
FROM sys.indexes SI
JOIN sys.filegroups SFG
ON SI.data_space_id =SFG.data_space_id
WHERE object_id = @TableID
AND index_id = @IndexID
SELECT @IndexColsSQL = '(' + SUBSTRING(@IndexColsSQL,1,LEN(@IndexColsSQL)-1) + ')'
IF LTRIM(RTRIM(@IncludeSQL)) <> ''
SELECT @IncludeSQL = ' INCLUDE (' + SUBSTRING(@IncludeSQL,1,LEN(@IncludeSQL)-1) + ')'
IF LTRIM(RTRIM(@WhereSQL)) <> ''
SELECT @WhereSQL = ' WHERE (' + @WhereSQL + ')'
END
SELECT @CreateSQL = @CreateSQL
+ @IndexColsSQL + CASE WHEN @IndexColsSQL <> '' THEN CHAR(13) ELSE '' END
+ ISNULL(@IncludeSQL,'') + CASE WHEN @IncludeSQL <> '' THEN CHAR(13) ELSE '' END
+ ISNULL(@WhereSQL,'') + CASE WHEN @WhereSQL <> '' THEN CHAR(13) ELSE '' END
+ @WithSQL
UPDATE [dbo].[#CollationIDXTable]
SET CreateScript = @CreateSQL
,DropScript = @DropSQL
,ExistsScript = @ExistsSQL
WHERE Schema_ID = @SchemaID
AND Object_ID = @TableID
AND Index_ID = @IndexID
END
INSERT INTO #ChangeCollationObjectsBackupTbl
(SchemaID, TableID, ObjectName, ObjectType, CreateScript, DropScript, ExistsScript, Processed)
SELECT Schema_ID,Object_ID,Indexname,'Index-'
+ CASE IndexType
WHEN 1 THEN 'Clustered'
WHEN 2 THEN 'NonClustered'
ELSE ''
END
,CreateScript, DropScript, ExistsScript, 0
FROM [#CollationIDXTable]
-----------------------------------------------------------------------------------------------------------------------------
-- Backup Check Constraints
-----------------------------------------------------------------------------------------------------------------------------
INSERT INTO #ChangeCollationObjectsBackupTbl
(SchemaID, TableID, ObjectName, ObjectType, CreateScript, DropScript, ExistsScript, Processed)
SELECT CCC.SchemaID
,CCC.TableID
,SCC.name
,'ComputedColumn'
,'ALTER TABLE ' + QUOTEname(CCC.SchemaName) + '.' + QUOTEname(CCC.TableName) +
' ADD CONSTRAINT ' + QUOTEname(SCC.name) + ' CHECK ' + SCC.definition
,'ALTER TABLE ' + QUOTEname(CCC.SchemaName) + '.' + QUOTEname(CCC.TableName) +
' DROP CONSTRAINT ' + QUOTEname(SCC.name)
,'EXISTS (SELECT 1
FROM sys.check_constraints SCC
JOIN sys.tables ST
ON ST.object_id = SCC.Parent_object_id
JOIN sys.schemas SCH
ON ST.schema_id = SCH.schema_id
WHERE SCC.name =''' + SCC.name + '''
AND ST.name =''' + CCC.TableName + '''
AND SCH.name =''' + CCC.SchemaName + ''')'
,0
FROM sys.check_constraints SCC
JOIN #ChangeCollationTables CCC
ON SCC.parent_object_id = CCC.TableID
JOIN sys.tables ST
ON ST.object_id = CCC.TableID
AND ST.schema_id = CCC.SchemaID
-----------------------------------------------------------------------------------------------------------------------------
-- Backup Foreignkey Constraints
-----------------------------------------------------------------------------------------------------------------------------
INSERT INTO #ChangeCollationObjectsBackupTbl
(SchemaID, TableID, ObjectName, ObjectType, CreateScript, DropScript, ExistsScript, Processed)
SELECT STP.schema_id
,STP.object_id
,SF.name
,'Foreign Key'
,' ALTER TABLE ' + QUOTEname(SCHEMA_name(STP.schema_id)) + '.' + QUOTEname(STP.name)
+' WITH CHECK ADD CONSTRAINT ' + QUOTEname(SF.name) + ' FOREIGN KEY(' +
STUFF
(
(
SELECT ',' + QUOTEname(SC.name)
FROM sys.foreign_key_columns SFC
JOIN sys.columns SC
ON SC.object_id = SFC.parent_object_id
AND SC.column_id = SFC.parent_column_id
WHERE SFC.constraint_object_id = SF.object_id
ORDER BY SC.column_id
FOR XML PATH ('')
),1,1,'')
+ ') REFERENCES ' + QUOTEname(SCHEMA_name(STC.schema_id)) + '.' + QUOTEname(STC.name)
+ ' (' +
+ STUFF
(
(
SELECT ',' + QUOTEname(SC.name)
FROM sys.foreign_key_columns SFC
JOIN sys.columns SC
ON SC.object_id = SFC.referenced_object_id
AND SC.column_id = SFC.referenced_column_id
WHERE SFC.constraint_object_id = SF.object_id
ORDER BY SC.column_id
FOR XML PATH ('')
),1,1,'')
+ ')'
,'ALTER TABLE ' + QUOTEname(SCHEMA_name(STP.schema_id)) + '.' + QUOTEname(STP.name) + ' DROP CONSTRAINT [' + SF.name + ']'
,' EXISTS ( SELECT 1 FROM sys.FOREIGN_KEYS WHERE name =''' + SF.name + ''' and parent_object_id = ' + CONVERT(varchar(50),SF.parent_object_id) + ')'
,0
FROM sys.foreign_keys SF
JOIN sys.tables STP
ON STP.object_id = SF.parent_object_id
JOIN sys.tables STC
ON STC.object_id = SF.referenced_object_id
WHERE EXISTS (
SELECT 1
FROM sys.foreign_key_columns SFCIn
JOIN #ChangeCollationColumns CCC
ON SFCIn.parent_object_id = CCC.TableID
AND SFCIn.parent_column_id = CCC.ColumnID
WHERE SFCIn.constraint_object_id = SF.object_id
)
UNION
SELECT STP.schema_id
,STP.object_id
,SF.name
,'Foreign Key'
,' ALTER TABLE ' + QUOTEname(SCHEMA_name(STC.schema_id)) + '.' + QUOTEname(STC.name)
+' WITH CHECK ADD CONSTRAINT ' + QUOTEname(SF.name) + ' FOREIGN KEY(' +
STUFF
(
(
SELECT ',' + QUOTEname(SC.name)
FROM sys.foreign_key_columns SFC
JOIN sys.columns SC
ON SC.object_id = SFC.parent_object_id
AND SC.column_id = SFC.parent_column_id
WHERE SFC.constraint_object_id = SF.object_id
ORDER BY SC.column_id
FOR XML PATH ('')
),1,1,'')
+ ') REFERENCES ' + QUOTEname(SCHEMA_name(STP.schema_id)) + '.' + QUOTEname(STP.name)
+ ' (' +
+ STUFF
(
(
SELECT ',' + QUOTEname(SC.name)
FROM sys.foreign_key_columns SFC
JOIN sys.columns SC
ON SC.object_id = SFC.referenced_object_id
AND SC.column_id = SFC.referenced_column_id
WHERE SFC.constraint_object_id = SF.object_id
ORDER BY SC.column_id
FOR XML PATH ('')
),1,1,'')
+ ')'
,'ALTER TABLE ' + QUOTEname(SCHEMA_name(STC.schema_id)) + '.' + QUOTEname(STC.name) + ' DROP CONSTRAINT [' + SF.name + ']'
,' EXISTS ( SELECT 1 FROM sys.FOREIGN_KEYS WHERE name =''' + SF.name + ''' and parent_object_id = ' + CONVERT(varchar(50),SF.parent_object_id) + ')'
,0
FROM sys.foreign_keys SF
JOIN sys.tables STP
ON STP.object_id = SF.referenced_object_id
JOIN sys.tables STC
ON STC.object_id = SF.parent_object_id
WHERE EXISTS (
SELECT 1
FROM sys.foreign_key_columns SFCIn
JOIN #ChangeCollationColumns CCC
ON SFCIn.referenced_object_id = CCC.TableID
AND SFCIn.referenced_column_id = CCC.ColumnID
WHERE SFCIn.constraint_object_id = SF.object_id
)
-----------------------------------------------------------------------------------------------------------------------------
-- Loop through Tables to change Collation
-----------------------------------------------------------------------------------------------------------------------------
DECLARE @BackupID int
DECLARE @ObjectType VARCHAR(50)
DECLARE @ObjectName sysname
-----------------------------------------------------------------------------------------------------------------------------
-- Inner Loop -- Drop the objects
-----------------------------------------------------------------------------------------------------------------------------
UPDATE [dbo].[#ChangeCollationTables]
SET Processed = 0
WHILE EXISTS(SELECT 1
FROM #ChangeCollationTables
WHERE ISNULL(Processed,0) = 0
)
BEGIN
SELECT @sql = ''
SELECT TOP 1 @SchemaID = SchemaID
,@TableID = TableID
,@TableName = TableName
,@SchemaName = SchemaName
--,@sql = 'IF ' + ExistsScript + CHAR(13) + DropScript + CHAR(13)
FROM [dbo].[#ChangeCollationTables]
WHERE ISNULL(Processed,0) = 0
ORDER BY RunRank DESC, SchemaID ASC,TableID ASC
UPDATE #ChangeCollationObjectsBackupTbl
SET Processed = 0
WHERE SchemaID = @SchemaID
AND TableID = @TableID
WHILE EXISTS(SELECT 1
FROM #ChangeCollationObjectsBackupTbl
WHERE ISNULL(Processed,0) = 0
AND SchemaID = @SchemaID
AND TableID = @TableID
)
BEGIN
SELECT @sql = ''
SELECT TOP 1 @BackupID = BackupID
,@ObjectName = ObjectName
,@ObjectType = ObjectType
,@sql = 'IF ' + ExistsScript + CHAR(13) + DropScript + CHAR(13)
FROM #ChangeCollationObjectsBackupTbl
WHERE ISNULL(Processed,0) = 0
AND SchemaID = @SchemaID
AND TableID = @TableID
ORDER BY BackupID DESC
IF @GenerateScriptsOnly = 1
BEGIN
PRINT @sql
END
ELSE
BEGIN
PRINT @sql
EXEC (@sql)
END
UPDATE #ChangeCollationObjectsBackupTbl
SET Processed = 1
WHERE SchemaID = @SchemaID
AND TableID = @TableID
AND BackupID = @BackupID
END
UPDATE [dbo].[#ChangeCollationTables]
SET Processed = 1
WHERE SchemaID = @SchemaID
AND TableID = @TableID
END
-----------------------------------------------------------------------------------------------------------------------------
-- Apply the collation changes
-----------------------------------------------------------------------------------------------------------------------------
UPDATE [dbo].[#ChangeCollationTables]
SET Processed = 0
WHILE EXISTS(SELECT 1
FROM #ChangeCollationTables
WHERE ISNULL(Processed,0) = 0
)
BEGIN
SELECT @sql = ''
SELECT TOP 1 @SchemaID = SchemaID
,@TableID = TableID
,@TableName = TableName
,@SchemaName = SchemaName
--,@sql = 'IF ' + ExistsScript + CHAR(13) + DropScript + CHAR(13)
FROM [dbo].[#ChangeCollationTables]
WHERE ISNULL(Processed,0) = 0
ORDER BY RunRank DESC, SchemaID ASC,TableID ASC
SELECT @sql = ''
SELECT @sql = @sql + AlterScript + CHAR(13)
FROM #ChangeCollationColumns
WHERE SchemaID = @SchemaID
AND TableID = @TableID
IF @GenerateScriptsOnly = 1
BEGIN
PRINT @sql
END
ELSE
BEGIN
PRINT @sql
EXEC (@sql)
END
UPDATE [dbo].[#ChangeCollationTables]
SET Processed = 1
WHERE SchemaID = @SchemaID
AND TableID = @TableID
END
-----------------------------------------------------------------------------------------------------------------------------
-- Inner Loop -- ReApply the objects
-----------------------------------------------------------------------------------------------------------------------------
UPDATE [dbo].[#ChangeCollationTables]
SET Processed = 0
WHILE EXISTS(SELECT 1
FROM #ChangeCollationTables
WHERE ISNULL(Processed,0) = 0
)
BEGIN
SELECT @sql = ''
SELECT TOP 1 @SchemaID = SchemaID
,@TableID = TableID
,@TableName = TableName
,@SchemaName = SchemaName
--,@sql = 'IF ' + ExistsScript + CHAR(13) + DropScript + CHAR(13)
FROM [dbo].[#ChangeCollationTables]
WHERE ISNULL(Processed,0) = 0
ORDER BY RunRank asc, SchemaID ASC,TableID ASC
UPDATE #ChangeCollationObjectsBackupTbl
SET Processed = 0
WHERE SchemaID = @SchemaID
AND TableID = @TableID
WHILE EXISTS(SELECT 1
FROM #ChangeCollationObjectsBackupTbl
WHERE ISNULL(Processed,0) = 0
AND SchemaID = @SchemaID
AND TableID = @TableID
AND CreateScript IS NOT NULL
)
BEGIN
SELECT @sql = ''
SELECT TOP 1 @BackupID = BackupID
,@ObjectName = ObjectName
,@ObjectType = ObjectType
,@sql = CASE ObjectType
WHEN 'View' THEN CreateScript + CHAR(13)
ELSE 'IF NOT ' + ExistsScript + CHAR(13) + CreateScript + CHAR(13)
END
FROM #ChangeCollationObjectsBackupTbl
WHERE ISNULL(Processed,0) = 0
AND SchemaID = @SchemaID
AND TableID = @TableID
AND CreateScript IS NOT NULL
ORDER BY BackupID ASC
IF @GenerateScriptsOnly = 1
BEGIN
PRINT @sql
END
ELSE
BEGIN
PRINT @sql
EXEC (@sql)
END
UPDATE #ChangeCollationObjectsBackupTbl
SET Processed = 1
WHERE SchemaID = @SchemaID
AND TableID = @TableID
AND BackupID = @BackupID
END
UPDATE [dbo].[#ChangeCollationTables]
SET Processed = 1
WHERE SchemaID = @SchemaID
AND TableID = @TableID
END
GO
Update – Junho de 2023
Como o processo acima pode causar dúvidas em como ser aplicado, decidimos demonstrar o print do passo a passo a ser executado.
Obs Importante: Faça um backup antes de alterar as informações da base
Obs Importante 2: Certifique-se de ninguém estar usando a base
- Clique com o botão direito na base, e depois em Propriedades
Propriedades da base de dados
- Confirme o nome da collation que esta usando
Verificando a collation usada
- Abra a query nessa base de dados, selecione tudo e execute (mesmo com erros a direita por causa das tabelas temporárias que serão criadas)
Instalando a procedure
- Abra uma nova aba, e execute a procedure
Executando a procedure
- Feche todas as abas de query dessa base
- Agora clique com o botão direito na base, e vá em Propriedades novamente
- Vá em Opções e mude a Collation
Modificando a Collation
- Agora é só confirmar e aguardar encerrar (dependendo do tamanho da base pode demorar)
Bom pessoal, por hoje é só.
Abraços e até a próxima.
Esta dando erro.
Msg 207, Level 16, State 1, Procedure ChangeCollation_SP, Line 504
Invalid column name ‘filter_definition’.
Boa noite Brother.
Que estranho.
Qual é a versão do SQL Server?
Um grande abraço.
Olá, Dan. Estou precisando fazer justamente isso em minha base do protheus. Teria o contato de alguém para fazer esse trabalho para mim?
Estou em processo de migrar o dicionário pro banco, mas gera um erro e o suporte informou ser por conta do collation.
Bom dia Paulo, tudo bem?
Nós da Atilio Sistemas ( https://atiliosistemas.com/ ) fazemos esse tipo de trabalho, mas sempre com agendas marcadas com antecedência.
Se você quiser entrar em contato e solicitar um orçamento, fique à vontade.
Ou se for o caso, se quiser fazer por ai mesmo, seriam apenas dois passos:
1. Copiar todo o código acima e colar no seu SQL Server (talvez apenas mudando a linha 10, colocando o nome da collation atual sua)
2. Execute o código acima para ele criar a procedure no seu ambiente
3. Ai você executa a procedure ChangeCollation_SP
Um grande abraço.
Dan, o código não está rodando. O sql acusa erro em várias linhas. Como faço para solicitar um orçamento com vocês? Tenho urgência.
Boa tarde Paulo.
Te mandamos por eMail o procedimento de como rodar. Assim que possível veja e nos dê um feedback se deu tudo certo.
Abraços.
Ao executar deu erro pra mim:
Msg 530, Nível 16, Estado 1, Procedimento ChangeCollation_SP, Linha 175 [Linha de Início do Lote 0]
The statement terminated. The maximum recursion 100 has been exhausted before statement completion.
Boa tarde Kadu, tudo joia?
Que estranho, pode ser alguma configuração. Em todo o caso, pesquisei no Google por “The maximum recursion 100 has been exhausted before statement completion sql server” e achei esses dois links que podem te ajudar:
https://learn.microsoft.com/en-us/answers/questions/105463/the-maximum-recursion-100-has-been-exhausted-befor
https://stackoverflow.com/questions/9650045/the-maximum-recursion-100-has-been-exhausted-before-statement-completion
Abraços.
Bom dia, Dan.
Preciso trocar a collation do banco do Protheus. Num banco de teste, cópia do de produção, criei a stored procedure com sucesso e rodei a mesma logo em seguida, mas o resultado não foi o esperado.
O SQL Server 2016 informa que a consulta foi concluída com erros. Tentei localizar o erro, mas sem sucesso, pois não tem nenhum erro registrado. Após quase 7 horas rodando, o resultado foi um conjunto de vários comandos!
Tem vários desses (mais de 270 mil linhas), considerando dropar índices e estatísticas, caso eles existam:
IF EXISTS (SELECT * FROM sys.indexes WHERE object_id = OBJECT_ID(N'[dbo].[TDQ010]’) AND name = N’TDQ010_UNQ’)
DROP INDEX [TDQ010_UNQ] ON [dbo].[TDQ010] WITH ( ONLINE = OFF )
Depois vem um bloco de quase 80 mil linhas procurando alterar a collation das colunas das diversas tabelas do banco:
ALTER TABLE [dbo].[TDQ010] ALTER COLUMN [TDQ_FILIAL] varchar(4) COLLATE Latin1_General_BIN NOT NULL
Aqui notei uma coisa estranha, algumas das linhas vem com a estrutura apenas ALTER TABLE [dbo].[nome_da_tabela] ALTER, o que não faz sentido.
Por fim, vem outro bloco, de cerca de 116 mil linhas, recriando os índices:
IF NOT EXISTS (SELECT * FROM sys.indexes WHERE object_id = OBJECT_ID(N'[dbo].[TDQ010]’) AND name = N’TDQ0101′)
CREATE NONCLUSTERED INDEX [TDQ0101] ON [dbo].[TDQ010](TDQ_FILIAL ASC ,TDQ_CODPLA ASC ,TDQ_CODEQP ASC ,R_E_C_N_O_ ASC ,D_E_L_E_T_ ASC )
WITH (PAD_INDEX = OFF,
IGNORE_DUP_KEY = OFF,
ALLOW_ROW_LOCKS = ON,
ALLOW_PAGE_LOCKS = ON,
SORT_IN_TEMPDB = OFF,DROP_EXISTING = OFF, STATISTICS_NORECOMPUTE = OFF, SORT_IN_TEMPDB = OFF, ONLINE = OFF) ON [PRIMARY]
E finalizando, vem o Horário de conclusão: 2024-04-14T22:28:58.5959883-03:00
Eu deveria agora tentar rodar os comandos gerados?
De acordo com os prints aqui nesse artigo, a própria stored procedure é que devia fazer isso.
Bom dia Roberto, tudo joia?
Que estranho, não peguei erros assim. Pelo log que você enviou, são só dessa tabela TDQ010 que acontece isso?
Se sim, o que você poderia fazer é:
a. Via APSDU, faça um backup da tabela TDQ
b. Via APSDU, faça um drop table na tabela TDQ
c. Rode o processamento da collation
d. Faça o sistema recriar a TDQ (execute um DbSelectArea ou ChkFile na TDQ)
e. Rode um append dos dados que foram feitos backup
Um grande abraço.
Bom dia, Dan. Sim, tudo joia.
Não é só com a TDQ010 não! São com diversas tabelas. Essa é apenas a primeira de cada bloco que informa sobre diversas tabelas, como disse no post anterior.
Boa tarde Roberto, opa que bom que esta tudo joia.
Faça o teste com a TDQ e veja se ela some da lista (após fazer o que comentamos do backup e drop)
Se sim, então era alguma sujeira na tabela, ai então faça uma user function em advpl que faz o seguinte
A. Da um select em todas as tabelas q tem dados ou monta um array com as tabelas que estão com problema
B. Vem percorrendo todas essas tabelas
C. Nesse laço de repetição você vai
C1. Fazer o bkp da tabela para uma nova, pode usar por exemplo a FWDbCreate
C2. Ai você vai replicar os dados dessa tabela pra copia (pode usar um reclock mesmo com todos os dados ou tentar outra forma)
C3. Ai você vai dar um drop na tabela
C4. Ai vai recriar a tabela com checkFile
C5. Então volte os dados do backup
Um grande abraço.
Bom dia, Dan.
Estava pendente dar um retorno sobre isso.
Depois dessa simulação em abril/2024, em bases de teste, hoje fiz em produção. O mesmo erro se manifestou, mas conforme eu já tinha descoberto na fase de testes, basta rodar novamente a stored procedure.
Minha base do Protheus tem 5.214 tabelas e 106.075 campos. Nas bases de teste do TSS, por exemplo, muito menores (apenas 95 campos distribuídos em 1.665 tabelas), o problema não ocorria. A stored rodou e converteu todos os campos.
O que ocorreu é que nessas bases com muitos campos, o buffer acaba e os comandos alter table são gerados incompletos para várias colunas.
Lembra que comentei isso?
“Aqui notei uma coisa estranha, algumas das linhas vem com a estrutura apenas ALTER TABLE [dbo].[nome_da_tabela] ALTER, o que não faz sentido.”
Ele não consegue completar a linha de comando devido a falta de buffer.
Então, depois da primeira execução, simplesmente rodei a ChangeCollation_SP novamente. Como ela pula os campos que já estão na collation order destino, a tarefa foi completada com sucesso para as colunas restantes.
Depois foi só conferir que deu tudo certo.
Bom dia Roberto, tudo joia?
Ah entendi, então era devido ao buffer em memória.
Supondo então que, num cenário onde o buffer seria pequeno, em uma base com 300 campos, e o buffer conseguisse 100 por vezes, teria que rodar o script umas 3 vezes para acertar todos os campos.
Muito obrigado pelo feedback.
Tenha uma ótima e abençoada semana.
Um grande abraço.