Back to OIM Explorer

dbo.QBM_PDatabaseCollationCheck

Stored ProcedureSQL_STORED_PROCEDURESandbox DB

Stored Procedure.

Source: sandbox-db sys.sql_modules

Source size: 3.842 characters

Interpretation

  • Database routine. Review parameters, called procedures, DBQueue inserts, and QBM_PJobCreate helper calls before assuming side effects.

Relations

  • No extracted relations.

Typed Edges

  • references source dbo.QBM_FSQColumDef source text reference
  • references source dbo.QBM_FSQPrimaryKeyDef source text reference
  • references source dbo.QBM_FSQPrimaryKeyDef_S source text reference
  • references source dbo.QBM_PSessionErrorAdd source text reference

References

Referenced By

  • No direct source references extracted.

Complete Source

SQL160 lines
1CREATE PROCEDURE QBM_PDatabaseCollationCheck(2  @ModuleName varchar(3),3  @TablePattern varchar(30) = '%'4)5AS6BEGIN7  DECLARE @Targetcollation nvarchar(64)8  DECLARE @TableName nvarchar(64)9  DECLARE @columnname nvarchar(64)10  DECLARE @pkDef nvarchar(max)11  DECLARE @SQLcmd nvarchar(max)12  DECLARE @DebugSwitch int = 013  DECLARE @Tables TABLE(TableName varchar(64) collate database_default)14  DECLARE @Columns TABLE(ColumnName varchar(64) collate database_default)15  DECLARE @IndexName nvarchar(64)16  SET XACT_ABORT OFF17  BEGIN TRY18    SELECT TOP 1 @Targetcollation = d.collation_name19    FROM sys.databases d20    WHERE21      d.database_id = DB_ID()22    IF @DebugSwitch > 023    BEGIN24      print '@ModuleName ' + @ModuleName print '@TablePattern ' + @TablePattern25    END26    DELETE @Tables27    INSERT INTO @Tables28    SELECT29      DISTINCT t.TABLE_NAME30    FROM information_schema.columns c31    JOIN INFORMATION_SCHEMA.TABLES t32      ON c.TABLE_NAME = t.TABLE_NAME33    LEFT34    OUTER35    JOIN DialogTable dt36      ON t.TABLE_NAME = dt.TableName collate database_default37    WHERE38      c.DATA_TYPE LIKE '%char%' AND t.TABLE_TYPE = 'base table' AND c.COLLATION_NAME !=@Targetcollation AND t.TABLE_NAME NOT LIKE 'sys%' AND t.TABLE_NAME39    NOT LIKE 'ms%' AND t.TABLE_NAME NOT LIKE 'IH%' AND t.TABLE_NAME NOT LIKE 'conflict%' AND((40    LEFT(ISNULL(dt.UID_DialogTable, '---'), 3) = @ModuleName OR @ModuleName = '%') AND t.TABLE_NAME LIKE @TablePattern)41    IF @DebugSwitch > 042    BEGIN43      SELECT *44      FROM @Tables45    END46    SELECT @TableName = '#'47    WHILE @TableName > ' '48    BEGIN49      SELECT @TableName = NULL50      SELECT TOP 1 @TableName = t.TableName51      FROM @Tables t52      IF @TableName IS NULL53      BEGIN54        CONTINUE55      END56      IF dbo.QBM_FSQPrimaryKeyDef_S(@TableName) > ' '57      BEGIN58        SELECT59          @pkDef = 'alter table ' + @TableName + ' add ' + dbo.QBM_FSQPrimaryKeyDef_S(@TableName)60      END61      ELSE62      BEGIN63        SELECT @pkDef = ''64      END65      SELECT TOP 1 @SQLcmd = 'alter table ' + @TableName + ' drop constraint ' + kc.name66      FROM sys.key_constraints kc67      JOIN sys.tables t68        ON kc.parent_object_id = t.object_id69      WHERE70        kc.type = 'PK' AND t.name = @TableName71      IF @@ROWCOUNT > 072      BEGIN73        IF @DebugSwitch > 074        BEGIN75          print @SQLcmd76        END77        EXEC sp_executesql @SQLcmd78      END79      IF @DebugSwitch > 080      BEGIN81        print '@TableName ' + @TableName82      END83      DELETE @Columns84      INSERT INTO @Columns(ColumnName)85      SELECT86        DISTINCT c.COLUMN_NAME87      FROM INFORMATION_SCHEMA.COLUMNS c88      WHERE89        c.data_type LIKE '%char%' AND c.TABLE_NAME = @TableName AND c.COLLATION_NAME <> @Targetcollation AND c.TABLE_NAME NOT LIKE 'sys%'90      SELECT @columnname = '#'91      WHILE @columnname > ' '92      BEGIN93        SELECT @columnname = NULL94        SELECT TOP 1 @columnname = c.ColumnName95        FROM @Columns c96        IF @columnname IS NULL97        BEGIN98          CONTINUE99        END100        IF @DebugSwitch > 0101        BEGIN102          print '@columnname ' + @columnname103        END104        SELECT @Indexname = N '#'105        WHILE @IndexName IS NOT NULL106        BEGIN107          SELECT @IndexName = NULL108          SELECT TOP 1 @IndexName = i.name109          FROM sys.tables t110          JOIN sys.indexes i111            ON t.object_id = i.object_id112          JOIN sys.index_columns ic113            ON i.index_id = ic.index_id AND ic.object_id = t.object_id114          JOIN sys.columns c115            ON ic.column_id = c.column_id AND c.object_id = t.object_id116          WHERE117            t.name = @TableName AND c.name = @columnname118          IF @IndexName IS NOT NULL119          BEGIN120            SELECT121              @SQLcmd = N 'drop index "' + @TableName + N '"."' + @IndexName + N '"' print @sqlcmd122            EXEC sp_executeSQL @SQLcmd123          END124        END125        SELECT126          @SQLcmd = 'alter table ' + c.TABLE_NAME + ' ' + 'alter column' + ' ' + dbo.QBM_FSQColumDef(c.TABLE_NAME,127          c.COLUMN_NAME)128        FROM QBM_VSchemaColumns c129        WHERE130          c.COLUMN_NAME = @columnname AND c.TABLE_NAME = @TableName131        IF @DebugSwitch > 0132        BEGIN133          print @SQLcmd134        END135        EXEC sp_executesql @SQLcmd136        DELETE @Columns137        WHERE138          ColumnName = @columnname139      END140      IF @pkDef > ' '141      BEGIN142        IF @DebugSwitch > 0143        BEGIN144          print @pkDef145        END146        EXEC sp_executesql @pkDef147      END148      DELETE @Tables149      WHERE150        TableName = @TableName151    END152  END TRY153  BEGIN CATCH154    EXEC QBM_PSessionErrorAdd DEFAULT155    RAISERROR('',156    18,157    1)158      WITH NOWAIT159  END CATCH160END
Open raw exported source
SQL ยท Raw26 lines
1     create   procedure QBM_PDatabaseCollationCheck (@ModuleName varchar(3)  , @TablePattern varchar(30) = '%'  ) as begin declare @Targetcollation2 nvarchar(64) declare @TableName nvarchar(64) declare @columnname nvarchar(64) declare @pkDef nvarchar(max) declare @SQLcmd nvarchar(max) declare @DebugSwitch3 int = 0 declare @Tables Table (TableName varchar(64) collate database_default) declare @Columns Table (ColumnName varchar(64) collate database_default4) declare @IndexName nvarchar(64) SET XACT_ABORT OFF BEGIN TRY select top 1 @Targetcollation = d.collation_name from sys.databases d where d.database_id5 = DB_ID() if @DebugSwitch > 0 begin print '@ModuleName ' + @ModuleName print '@TablePattern ' + @TablePattern end delete  @Tables  insert into @Tables6  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 DialogTable7 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 !=@Targetcollation8 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(ISNULL9(dt.UID_DialogTable, '---'), 3) = @ModuleName or @ModuleName = '%' ) and t.TABLE_NAME like @TablePattern ) if @DebugSwitch > 0 begin select * from @Tables10 end     select @TableName = '#' while @TableName > ' ' begin select @TableName = null select top 1 @TableName = t.TableName from @Tables t if @TableName11 is null begin continue end   if dbo.QBM_FSQPrimaryKeyDef_S(@TableName) > ' ' begin select @pkDef = 'alter table ' + @TableName + ' add ' + dbo.QBM_FSQPrimaryKeyDef_S12(@TableName) end else begin select @pkDef = '' end  select top 1 @SQLcmd = 'alter table ' + @TableName + ' drop constraint ' + kc.name from sys.key_constraints13 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 begin14 print @SQLcmd end exec sp_executesql @SQLcmd end if @DebugSwitch > 0 begin print '@TableName ' + @TableName end  delete  @Columns insert into @Columns15(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_NAME16 <> @Targetcollation and c.TABLE_NAME not like 'sys%'     select @columnname = '#' while @columnname > ' ' begin select @columnname = null select top 117 @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.indexes19 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_id20 = 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 if23 @DebugSwitch > 0 begin print @SQLcmd end exec sp_executesql @SQLcmd delete  @Columns where ColumnName = @columnname end       if @pkDef > ' ' begin if24 @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