dbo.QBM_PDatabaseCollationCheck

1     create   procedure QBM_PDatabaseCollationCheck (@ModuleName varchar(3)  , @TablePattern varchar(30) = '%'  ) as begin declare @Targetcollation
2 nvarchar(64) declare @TableName nvarchar(64) declare @columnname nvarchar(64) declare @pkDef nvarchar(max) declare @SQLcmd nvarchar(max) declare @DebugSwitch
3 int = 0 declare @Tables Table (TableName varchar(64) collate database_default) declare @Columns Table (ColumnName varchar(64) collate database_default
4) declare @IndexName nvarchar(64) SET XACT_ABORT OFF BEGIN TRY select top 1 @Targetcollation = d.collation_name from sys.databases d where d.database_id
5 = DB_ID() if @DebugSwitch > 0 begin print '@ModuleName ' + @ModuleName print '@TablePattern ' + @TablePattern end delete  @Tables  insert into @Tables
6  select distinct t.TABLE_NAME  from information_schema.columns c join INFORMATION_SCHEMA.TABLES t on c.TABLE_NAME = t.TABLE_NAME left outer join DialogTable
7 dt on t.TABLE_NAME = dt.TableName collate database_default where c.DATA_TYPE like '%char%' and t.TABLE_TYPE = 'base table' and c.COLLATION_NAME !=@Targetcollation
8 and t.TABLE_NAME not like 'sys%' and t.TABLE_NAME not like 'ms%' and t.TABLE_NAME not like 'IH%' and t.TABLE_NAME not like 'conflict%' and ( (left(ISNULL
9(dt.UID_DialogTable, '---'), 3) = @ModuleName or @ModuleName = '%' ) and t.TABLE_NAME like @TablePattern ) if @DebugSwitch > 0 begin select * from @Tables
10 end     select @TableName = '#' while @TableName > ' ' begin select @TableName = null select top 1 @TableName = t.TableName from @Tables t if @TableName
11 is null begin continue end   if dbo.QBM_FSQPrimaryKeyDef_S(@TableName) > ' ' begin select @pkDef = 'alter table ' + @TableName + ' add ' + dbo.QBM_FSQPrimaryKeyDef_S
12(@TableName) end else begin select @pkDef = '' end  select top 1 @SQLcmd = 'alter table ' + @TableName + ' drop constraint ' + kc.name from sys.key_constraints
13 kc join sys.tables t on kc.parent_object_id = t.object_id where kc.type = 'PK' and t.name = @TableName if @@ROWCOUNT > 0 begin if @DebugSwitch > 0 begin
14 print @SQLcmd end exec sp_executesql @SQLcmd end if @DebugSwitch > 0 begin print '@TableName ' + @TableName end  delete  @Columns insert into @Columns
15(ColumnName) select distinct c.COLUMN_NAME from INFORMATION_SCHEMA.COLUMNS c where c.data_type like '%char%' and c.TABLE_NAME = @TableName and c.COLLATION_NAME
16 <> @Targetcollation and c.TABLE_NAME not like 'sys%'     select @columnname = '#' while @columnname > ' ' begin select @columnname = null select top 1
17 @columnname = c.ColumnName from @Columns c if @columnname is null begin continue end if @DebugSwitch > 0 begin print '@columnname ' + @columnname end 
18 select @Indexname = N'#' while @IndexName is not null begin select @IndexName = null select top 1 @IndexName = i.name from sys.tables t join sys.indexes
19 i on t.object_id = i.object_id     join sys.index_columns ic on i.index_id = ic.index_id and ic.object_id = t.object_id join sys.columns c on ic.column_id
20 = c.column_id and c.object_id = t.object_id where t.name = @TableName and c.name = @columnname if @IndexName is not null begin select @SQLcmd = N'drop index "'
21 + @TableName + N'"."' + @IndexName + N'"' print @sqlcmd exec sp_executeSQL @SQLcmd end  end  select @SQLcmd = 'alter table ' + c.TABLE_NAME + ' ' + 'alter column'
22 + ' ' + dbo.QBM_FSQColumDef(c.TABLE_NAME , c.COLUMN_NAME ) from QBM_VSchemaColumns c where c.COLUMN_NAME = @columnname and c.TABLE_NAME = @TableName if
23 @DebugSwitch > 0 begin print @SQLcmd end exec sp_executesql @SQLcmd delete  @Columns where ColumnName = @columnname end       if @pkDef > ' ' begin if
24 @DebugSwitch > 0 begin print @pkDef end exec sp_executesql @pkDef end  delete  @Tables where TableName = @TableName end      END TRY BEGIN CATCH exec 
25QBM_PSessionErrorAdd default RAISERROR ('', 18, 1) WITH NOWAIT END CATCH end 
26