generator.sql

DECLARE @TABLE_NAME varchar(255), @TABLE_SCHEMA varchar(255), @sql varchar(max) = ''
	, @insert_sql varchar(max) = '', @select_sql varchar(max) = ''
	, @with_sql varchar(max) = '', @TableNameLast varchar(16);

DECLARE tables CURSOR FOR
select TABLE_NAME, TABLE_SCHEMA
from information_schema.tables
where TABLE_NAME like '%[TABLE_FILTER]%'
declare @TBL as Table(
	[DEF] VARCHAR(MAX),
	[IS] VARCHAR(MAX),
	[SS] VARCHAR(MAX),
	[WS] VARCHAR(MAX)
)
open tables
FETCH NEXT FROM tables INTO @TABLE_NAME, @TABLE_SCHEMA

WHILE @@FETCH_STATUS = 0
BEGIN
	
	select @sql = CONCAT('IF (OBJECT_ID(''[',@TABLE_SCHEMA,'].[',@TABLE_NAME,']'') IS NOT NULL)', char(10));
	select @sql = CONCAT(@sql, 'BEGIN', char(10));
	select @sql = CONCAT(@sql, char(9), 'DROP TABLE [',@TABLE_SCHEMA,'].[',@TABLE_NAME,'];', char(10));
	select @sql = CONCAT(@sql, 'END', char(10));
	select @sql = CONCAT(@sql, 'CREATE TABLE [', @TABLE_SCHEMA , '].[' , @TABLE_NAME , '] (' , char(10))
	

	select @with_sql = CONCAT(char(9), 'WITH (', char(10));

	select @select_sql = CONCAT('SELECT @ID SEGMENT_EVENT_ID, *', char(10));

	-- IF CONTEXT ... PROPERTIES ... ETC
	IF CHARINDEX('CONTEXT', @TABLE_NAME) > 0
	BEGIN
	select @insert_sql = CONCAT('IF ISJSON(@context_data) = 1 AND NOT EXISTS(SELECT 1 FROM [',@TABLE_SCHEMA,'].[',@TABLE_NAME,'] WHERE SEGMENT_EVENT_ID = @ID)', char(10));
	select @select_sql = CONCAT(@select_sql, char(9), 'FROM OPENJSON(@context_data)', char(10));
	END

	IF CHARINDEX('PROPERTIES', @TABLE_NAME) > 0
	BEGIN
	select @insert_sql = CONCAT('IF ISJSON(@properties_data) = 1 AND NOT EXISTS(SELECT 1 FROM [',@TABLE_SCHEMA,'].[',@TABLE_NAME,'] WHERE SEGMENT_EVENT_ID = @ID)', char(10));
	select @select_sql = CONCAT(@select_sql, char(9), 'FROM OPENJSON(@properties_data)', char(10));
	END

	IF CHARINDEX('TRAITS', @TABLE_NAME) > 0
	BEGIN
	select @insert_sql = CONCAT('IF ISJSON(@traits_data) = 1 AND NOT EXISTS(SELECT 1 FROM [',@TABLE_SCHEMA,'].[',@TABLE_NAME,'] WHERE SEGMENT_EVENT_ID = @ID)', char(10));
	select @select_sql = CONCAT(@select_sql, char(9), 'FROM OPENJSON(@traits_data)', char(10));
	END

	select @insert_sql = CONCAT(@insert_sql, 'BEGIN', char(10));
	select @insert_sql = CONCAT(@insert_sql, char(9), 'INSERT INTO [', @TABLE_SCHEMA, '].[', @TABLE_NAME, '] (' ,char(10));
	
	select @TableNameLast = SUBSTRING( @TABLE_NAME , LEN(@TABLE_NAME) -  CHARINDEX('_',REVERSE(@TABLE_NAME)) + 2  , LEN(@TABLE_NAME)  )
	select @TableNameLast = case when @TableNameLast = 'CONTEXT' then '' else CONCAT('.', @TableNameLast) end
	-- COLUMN CODE --
	DECLARE @COLUMN_NAME varchar(255), @IS_NULLABLE BIT, @DATA_TYPE varchar(64), @CHARACTER_MAXIMUM_LENGTH INT, @ORDINAL_POSITION INT;
	DECLARE columns CURSOR FOR
	select COLUMN_NAME, case when IS_NULLABLE = 'YES' then 1 else 0 end, DATA_TYPE, CHARACTER_MAXIMUM_LENGTH, ORDINAL_POSITION
	from information_schema.columns
	where TABLE_NAME = @TABLE_NAME
	order by ORDINAL_POSITION
	declare @SEPERATOR char(1) = '';
	declare @INS_SEPERATOR char(1) = '';
	open columns
	FETCH NEXT FROM columns  INTO @COLUMN_NAME, @IS_NULLABLE, @DATA_TYPE, @CHARACTER_MAXIMUM_LENGTH, @ORDINAL_POSITION
	WHILE @@FETCH_STATUS = 0
	BEGIN
		select @sql = CONCAT(@sql , CHAR(9), '[' , @COLUMN_NAME , '] ')
		select @sql = CONCAT(@sql , UPPER(@DATA_TYPE));

		select @sql = CONCAT(@sql , case 
			when @CHARACTER_MAXIMUM_LENGTH = -1 then '(MAX)' 
			when @CHARACTER_MAXIMUM_LENGTH is NULL or @CHARACTER_MAXIMUM_LENGTH = '' then '' 
			else '('+CAST(@CHARACTER_MAXIMUM_LENGTH as VARCHAR)+')' 
			end
		);

		select @sql = CONCAT(@sql , case 
			when @ORDINAL_POSITION = 1 and @IS_NULLABLE <> 1 then ' IDENTITY(1,1) PRIMARY KEY ' 
			else '' end);

		select @sql = CONCAT(@sql , case when @IS_NULLABLE <> 1 then ' NOT NULL' else '' end);
	
		select @insert_sql = case 
			when @COLUMN_NAME = 'ID' then @insert_sql 
			else CONCAT(@insert_sql, char(9), char(9), @INS_SEPERATOR, '[', @COLUMN_NAME, ']', CHAR(10)) 
		end;
		
		select @with_sql = case 
			when @COLUMN_NAME IN ('ID', 'SEGMENT_EVENT_ID') then @with_sql 
			else CONCAT(@with_sql, char(9), char(9), @SEPERATOR,'[', @COLUMN_NAME, '] ') 
		end;

		select @with_sql = case 
			when @COLUMN_NAME IN ('ID', 'SEGMENT_EVENT_ID') then @with_sql 
			else CONCAT(@with_sql , UPPER(@DATA_TYPE))
		end;
		
		select @with_sql = CONCAT(@with_sql , case 
			when @COLUMN_NAME IN ('ID', 'SEGMENT_EVENT_ID') then '' 
			when @CHARACTER_MAXIMUM_LENGTH = -1 then '(MAX)' 
			when @CHARACTER_MAXIMUM_LENGTH is NULL or @CHARACTER_MAXIMUM_LENGTH = '' then '' 
			else '('+CAST(@CHARACTER_MAXIMUM_LENGTH as VARCHAR)+')' 
			end
		);

		select @with_sql = case 
			when @COLUMN_NAME IN ('ID', 'SEGMENT_EVENT_ID') then @with_sql 
			else CONCAT(@with_sql, ' ''lax $', Lower(@TableNameLast), '.', Lower(@COLUMN_NAME),'''', CHAR(10)) 
		end;

		select @SEPERATOR = case 
			when @COLUMN_NAME IN ('ID', 'SEGMENT_EVENT_ID') then @SEPERATOR 
			else ',' 
		end;

		select @INS_SEPERATOR = case 
			when @COLUMN_NAME IN ('ID') then @INS_SEPERATOR 
			else ',' 
		end;

		FETCH NEXT FROM columns INTO @COLUMN_NAME, @IS_NULLABLE, @DATA_TYPE, @CHARACTER_MAXIMUM_LENGTH, @ORDINAL_POSITION

		select @sql = CONCAT(@sql , case 
			when @@FETCH_STATUS = 0 then ',' 
			else '' end  , CHAR(10)
		);
	END

	close columns	
	deallocate columns

	select @sql = CONCAT(@sql , char(10) + ');')
	select @insert_sql = CONCAT(@insert_sql , char(9) + ')')
	--select @select_sql = CONCAT(@select_sql , char(10) + ')')
	select @select_sql = CONCAT(@insert_sql, char(10), char(9), @select_sql, @with_sql , char(9), ')', char(10), 'END')
	--select @sql  = CONCAT(@sql, char(10), @insert_sql, char(10), @select_sql, char(10), @with_sql);

	insert into @TBL
	select @sql as DEF, @insert_sql [IS], @select_sql [SS], @with_sql [WS]

	FETCH NEXT FROM tables  INTO @TABLE_NAME, @TABLE_SCHEMA
END

close tables
deallocate tables

select *
from @TBL