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'a ,@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
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.