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.
[sourcecode language=”sql”]
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:
[/sourcecode]