Auto Create Views from the Tribal SITS data dictionary

As I’ve discussed the Tribal SITS Data Model does not have the nicest table and fields names and has a huge number of lookup tables. In this situation I like to build a layer of views over the base tables to provide a friendlier interface to build queries on. Still this is a bunch of work – could I auto create these views from the SITS data dictionary? Short answer: Yes. But they’ll probably need a bit of manual tidying up. That said I can produce working views that will still save a lot of effort.

The SITS data dictionary tables provide everything I’ll need to build a basic view on any table and associated lookup tables. My goal will be to produce a view of all fields in a table using the more verbose column description as the view column names. And that joins to all lookup tables. And includes the lookup table description column for all lookup values. All the dictionary data required will come from this view which has already been discussed:

CREATE view vw_desc_sits_entity
as

select d.dct_code dictionary
,	e.ent_prfx + e.ent_code table_name
,	e.ent_code entity_code
,	e.ent_name
,	f.fld_seqn
,	f.fld_code column_name
,	f.fld_name description
,	f.fld_indx indexed
,	f.fld_mand mandatory
,	f.fld_type field_type
,	u.udf_name
,	u.udf_type
,	f.fld_indb in_database
,	le.ent_prfx + le.ent_code lookup_table
,	lf.fld_code lookup_PK
,	'left join ' + le.ent_prfx + le.ent_code + ' ' + le.ent_code + ' on ' + e.ent_code + '.' + f.fld_code + ' = ' + le.ent_code + '.' + lf.fld_code lookup_join
,	u.udf_prof
,	f.fld_help help_text
from men_dct d
	join men_ent e
		on d.dct_code = e.ent_dctc
	join dbo.men_fld f
		on e.ent_dctc = f.fld_dctc
		and e.ent_code = f.fld_entc
	left join men_fld lf
		on lf.fld_dctc = f.fld_dct1
		and lf.fld_entc = f.fld_ent1
		and lf.fld_code = f.fld_fld1
	left join men_ent le
		on lf.fld_dctc = le.ent_dctc
		and lf.fld_entc = le.ent_code
	left join men_udf u
		on f.fld_dctc = u.udf_dctc
		and f.fld_entc = u.udf_entc
		and f.fld_code = u.udf_fldc

So I’ll create a stored procedure that will take a SITS table name and the dictionary the entity is in. It will then return a create view script for that table.

create procedure create_sits_view

@sits_table varchar(255)
, @sits_dictionary varchar(255)

as

First I’ll get the entity code of the table and the entity name. The entity name will be cleaned up a bit and used as the view name:

declare @entity_code varchar(255)
declare @view_name varchar(255)

select @view_name = 'vw_' + lower(replace(max(ent_name), ' ', '_'))
, @entity_code = max(entity_code)
from vw_desc_sits_entity
where table_name = @sits_table

Then I’ll initialise a select clause and a from clause variable. In the from clause the entity code will be used as the table alias:

declare @select_clause varchar(max) = 'select '
declare @from_clause varchar(max) = 'from ' + @sits_table + ' ' + @entity_code + ' '

Next the rest of the variables I’ll need:

declare @column_name as varchar(255)a base table column name
declare @column_name_desc as varchar(255)cleaned up column name to be used as view column name
declare @view_column_name as varchar(255)view column name in []
declare @udf_column_name as varchar(255)cleaned up UDF name to be used as view column name
declare @lookup_table as varchar(255)a lookup table name
declare @lookup_join as varchar(255)a join from the base table to a lookup table
declare @lookup_entity_code as varchar(255)the entity code of a lookup table
declare @lookup_column_name as varchar(255)a column from the lookup table
declare @lookup_view_column_name as varchar(255)cleaned up lookup column name to be used as view column name

Then I’ll setup a cursor to loop through all columns in the table. Any columns in the dictionary that are not in the database are excluded by in_database = ‘Y’:

declare view_columns cursor
for
select	column_name
,	lower(replace(description, ' ', '_')) column_name_desc
,	'[' + lower(replace(description, ' ', '_')) + '_' + column_name + ']' view_column_name
,	'[' + lower(replace(udf_name, ' ', '_')) + '_' + column_name + ']' udf_column_name
,	lookup_table
,	lookup_join
from	vw_desc_sits_entity
where	table_name = @sits_table
and	dictionary = @sits_dictionary
and	in_database = 'Y'
order by	fld_seqn

OPEN view_columns
FETCH NEXT FROM view_columns INTO @column_name, @column_name_desc, @view_column_name, @udf_column_name, @lookup_table, @lookup_join

WHILE @@FETCH_STATUS = 0  
BEGIN  

For each column I’ll add the column to the select clause. If the field is a UDF I’ll use the configured UDF field name as the view column name:

set @view_column_name = isnull(@udf_column_name, @view_column_name)
set @select_clause = @select_clause + @entity_code + '.' + @column_name + ' ' + @view_column_name + ', '

Next if the column has a lookup table I’ll retrieve the lookup table entity code and the name column from the lookup table. Most lookup tables have a name column. If there is not one I’ll create the join to the table anyway but won’t include any columns from the lookup table in the select clause:

select	@lookup_entity_code = max(entity_code)
,	@lookup_column_name = max(column_name)
,	@lookup_view_column_name =   '[' + @column_name_desc + '_' + lower(replace(max(description), ' ', '_')) + ']'
from	vw_desc_sits_entity
where	table_name = @lookup_table
and	column_name like '%name'

Now I’ll add in the join to the lookup table to the from clause and the name column, if there is one, to the select clause. In the data dictionary the primary key on a table lookups up to itself so I need to exclude this case:

if @lookup_entity_code != @entity_code
begin

	set @from_clause = @from_clause + @lookup_join + ' '
	select @select_clause = @select_clause + @lookup_entity_code + '.' + @lookup_column_name + ' ' + @lookup_view_column_name + ', '

end

Finally I close the cursor loop and return the complete create view query:

FETCH NEXT FROM view_columns INTO @column_name, @column_name_desc, @view_column_name, @udf_column_name, @lookup_table, @lookup_join

end

close view_columns
deallocate view_columns


select 'create view ' + @view_name + ' as ' + left(@select_clause, len(@select_clause) - 1) + ' ' + @from_clause


Note that I’ve used select to output rather than print as print is limited to 8000 characters and SITS table definitions are BIG! Also not that some field names in the SITS data dictionary are not unique so I’ve had to suffix all the view column names with the field code. This is a shame but still produces a much better column name. Still it would be good to only do this on the non unique field names rather than them all. I’ve tested with a few of the big SITS tables and it produces working create view scripts. For example:

create_sits_view 'ins_stu', 'srs'

Finally here’s the complete stored procedure code:

create procedure create_sits_view

	@sits_table varchar(255)
,	@sits_dictionary varchar(255)

as


declare @entity_code varchar(255)
declare @view_name varchar(255)

select	@view_name = 'vw_' + lower(replace(max(ent_name), ' ', '_'))
,		@entity_code = max(entity_code)
from	vw_desc_sits_entity
where	table_name = @sits_table

declare @select_clause varchar(max) = 'select '
declare @from_clause varchar(max) = 'from ' + @sits_table + ' ' + @entity_code + ' '

declare @column_name as varchar(255)
declare @column_name_desc as varchar(255)
declare @view_column_name as varchar(255)
declare @udf_column_name as varchar(255)
declare @lookup_table as varchar(255)
declare @lookup_join as varchar(255)
declare @lookup_entity_code as varchar(255)
declare @lookup_column_name as varchar(255)
declare @lookup_view_column_name as varchar(255)

declare view_columns cursor 
for 
select	column_name
,		lower(replace(description, ' ', '_')) column_name_desc
,		'[' + lower(replace(description, ' ', '_')) + '_' + column_name + ']' view_column_name
,		'[' + lower(replace(udf_name, ' ', '_')) + '_' + column_name + ']' udf_column_name
,		lookup_table
,		lookup_join
from	vw_desc_sits_entity
where	table_name = @sits_table
and		dictionary = @sits_dictionary
and		in_database = 'Y'
order by fld_seqn

OPEN view_columns
FETCH NEXT FROM view_columns INTO @column_name, @column_name_desc, @view_column_name, @udf_column_name, @lookup_table, @lookup_join 

WHILE @@FETCH_STATUS = 0  
BEGIN  


	set @view_column_name = isnull(@udf_column_name, @view_column_name)
	set @select_clause = @select_clause + @entity_code + '.' + @column_name + ' ' + @view_column_name + ', '

	if @lookup_table is not null
	begin 

		select	@lookup_entity_code = max(entity_code)
		,		@lookup_column_name = max(column_name)
		,		@lookup_view_column_name =   '[' + @column_name_desc + '_' + lower(replace(max(description), ' ', '_')) + ']'
		from	vw_desc_sits_entity
		where	table_name = @lookup_table
		and		column_name like '%name'
		
		if @lookup_entity_code != @entity_code
		begin	 

			set @from_clause = @from_clause + @lookup_join + ' '
			select	@select_clause = @select_clause + @lookup_entity_code + '.' + @lookup_column_name + ' ' + @lookup_view_column_name + ', '
		
		end

	end



FETCH NEXT FROM view_columns INTO @column_name, @column_name_desc, @view_column_name, @udf_column_name, @lookup_table, @lookup_join 

end

close view_columns
deallocate view_columns


select 'create view ' + @view_name + ' as ' + left(@select_clause, len(@select_clause) - 1) + ' ' + @from_clause