Menu Close

Transform MSSQL Tables to Views for Input to Oracle Warehouse Builder

This code is written by myself. You can use it to generate views from tables in MSSQL database.

These generated views are input to Warehouse Builder as a source database objects.

The best part of it, that it does a bit of validation and transforms CamelCase notation (which is used in MSSQL database object names) to regular Oracle naming CAMEL_CASE. It does it for table name and column names.

So table name in MSSQL is  IncidentObjects, after running script it becomes RPT_INCIDENT_OBJECTS.

How to use it. Just copy this source and execute in MSSQL database. Script output is another MSSQL script. Run this output to generate views. These generated views then are imported to Warehouse Builder.


DECLARE @view_name nvarchar(100) = ''
DECLARE @table_name nvarchar(100)
declare @col_length integer = 30
declare @view_name_length integer = 30
declare @count int = 1
declare @regexp_pattern nvarchar(1000) = '(((?<=[a-z])[A-Z])|([A-Z](?![A-Z]|$)))'

-- Config section, case sensitive
/*

whole table list
declare @table_list nvarchar(max) = 'Addresses,Area,Cities,Classifiers_Vw'
*/

declare @table_list nvarchar(max) = ''

while (len(@table_list) > 0) -- fake condition
begin
 declare @temp_name nvarchar(100)
 set @temp_name = dbo.fnSplit(@table_list,',', @count)

 --print @count
 --print 'table: ' + isnull(@temp_name, 'previous table was last table.')

 if ((isnull(@temp_name, 'x')) = 'x')
 goto breaking

set @table_name = @temp_name
 set @count = (@count + 1)

/**********************/
 select @view_name = upper('RPT' + substring(upper(ts.c), 1, LEN(ts.c)))
 FROM (
 select dbo.RegexReplace(@table_name, @regexp_pattern, '_$1') AS c
 ) ts

 if len(@view_name) > @view_name_length
 begin
 -- Check if column name lenght doesn't exceed limits
 print '/************************************************************************************/'
 print '/* ERRRORR! View name "' + @view_name + '" length is too long. Exceeds ' + cast(@view_name_length as varchar(2)) + '*/'
 print '/************************************************************************************/'
 end

DECLARE c1 CURSOR READ_ONLY
FOR
select t.COLUMN_NAME, t.DATA_TYPE, t.CHARACTER_MAXIMUM_LENGTH
FROM INFORMATION_SCHEMA.COLUMNS t WHERE TABLE_NAME= @table_name

OPEN c1
 begin
 DECLARE @column_name nvarchar(100)
 DECLARE @column_alias nvarchar(100)
 DECLARE @data_type nvarchar(100)
 declare @view_ddl nvarchar(1000) = ''
 DECLARE @data_length nvarchar(100)

 FETCH NEXT FROM c1
 INTO @column_name, @data_type, @data_length

print 'IF EXISTS (SELECT TABLE_NAME FROM INFORMATION_SCHEMA.VIEWS
 WHERE TABLE_NAME = ''' + @view_name + ''')
 DROP VIEW ' + @view_name + '
 GO'
 print ''
 print 'CREATE VIEW '+ @view_name
 print 'AS'
 print 'SELECT'

WHILE @@FETCH_STATUS = 0
 BEGIN

if dbo.RegexMatch(@column_name, @regexp_pattern) = 0
 begin
 -- Not all columns will be in CamelCase, therefore make them CamelCase
 set @column_name = dbo.initcap(@column_name)
 end
 else
 begin
 set @column_name = @column_name
 end

 select @column_alias = substring(upper(t.c), 2, LEN(t.c))
 FROM (
 select dbo.RegexReplace(@column_name, @regexp_pattern, '_$1') AS c
 ) t

 /* Validate column and correct mistakes */
 if len(@column_alias) > @col_length
 begin
 print '/************************************************************************************/'
 print '/* ERRRORR! Column "' + @column_alias + '" length is too long. Exceeds ' + cast(@col_length as varchar(2)) + '*/'
 print '/************************************************************************************/'
 end
 else if @column_alias in ('DATE', 'NUMBER', 'CHAR', 'TIMESTAMP')
 begin
 print '/************************************************************************************/'
 print '/* ERRRORR! Column "' + @column_alias + '" can not be used - oracle reserved word */'
 print '/* Renaming column by adding postfix "_"*/'
 print '/************************************************************************************/'
 set @column_alias = @column_alias+'_'
 end


 /* Append new column to column string */
 if upper(@data_type) = 'NVARCHAR' and @data_length = '-1'
 begin
 set @view_ddl = @view_ddl + 'CAST(' + @column_name + ' as nvarchar(4000)) ' + @column_alias
 end
 else if upper(@data_type) = 'DATETIME'
 begin
 set @view_ddl = @view_ddl + 'CONVERT(varchar, ' + @column_name + ', 120) ' + @column_alias
 end
 else
 begin
 set @view_ddl = @view_ddl + @column_name + ' ' + @column_alias
 end

 set @view_ddl = @view_ddl + ',' + CHAR(10)

 FETCH NEXT FROM c1
 INTO @column_name, @data_type, @data_length

END

set @view_ddl = SUBSTRING(@view_ddl, 0, LEN(@view_ddl)-1) -- Remove comma from last column

 print @view_ddl
 print 'FROM ' + @table_name
 print 'WHERE DELETED = 0'
 print 'GO'
 print ''
 end -- end of cursor block
CLOSE c1
DEALLOCATE c1
end -- end of table list loop

breaking:

Leave a Reply

Your email address will not be published. Required fields are marked *