Copy queries from views in upcycled databases

Hi Hauke,

In the current upcycle script there is no functionality which imports the definition of the views.
There are two reasons for that:
 

1. We need to generate the definition of the model first
2. Every database saves the 'Query' of the view a little bit differently, so most of the time you need to ever slightly change the import script.

Therefore we haven't incorporated it into the main upcycle script yet.

Luckily we do have the functionality available to import the view definitions into your model:


 

--How to:
--Step 0 First do the main upcyle script and generate the definitions
--Step 1 Fill in the parameters below
--Step 2 Replace DEMO_SF -> SF database
--Step 3 Replace [demo] -> Upcycle databasedemo
Declare @project_id varchar(150) = 'DEMO'
 ,@project_vrs_id varchar(150) = '1.00'
--Step 4 Execute (F5)

----------First the control procedure for all the views
insert into DEMO_sf..[control_proc]
(project_id
,project_vrs_id
,control_proc_id
,control_proc_desc
,control_proc_type
,assign_type 
,code_grp_id
,gen_order_no
,development_status
,priority
,reviewed
,tested
,generated)

select @project_id as project_id
 ,@project_vrs_id as project_vrs_id
 ,concat('vw_',v.name) as control_proc_id
 ,concat('Upcycled ', v.name, ' view') as control_proc_desc
 ,1 as control_proc_type
 ,0 as assign_type --Static
 ,'VIEWS' as code_grp_id
 ,100 as gen_order_no
 ,0 as development_status
 ,7 as priority
 ,0 as reviewed
 ,0 as tested
 ,0 as generated
 FROM [demo].sys.views v

----the create the templates with the default definition
insert into DEMO_sf..[control_proc_template]
(project_id
,project_vrs_id
,control_proc_id
,template_id
,template_desc
,order_no
,no_of_object_item
,template_code
,code_generated)
select @project_id as project_id
 ,@project_vrs_id as project_vrs_id
 ,concat('vw_',v.name) as control_proc_id
 ,v.name as template_id
 ,concat(
 'Upcycled definition of the '
 , v.name
 , ' view'
 ) as template_desc
 ,100 as order_no
 ,1 as no_of_object_item
 ,right(m.definition
 ,len(m.definition) 
 - CHARINDEX('SELECT',m.definition) + 1 
 ) as template_code 
 ,0 as code_generated
FROM [demo].sys.views v
join [demo].sys.schemas s
 on s.schema_id = v.schema_id
INNER JOIN [demo].sys.sql_modules m ON m.object_id = v.object_id


--Now allocate the templates with the views
declare cursor_voorbeeld cursor local static read_only forward_only
 for
 select dbo.replace_illegal_characters(concat('view_',v.name)) as prog_object_id 
 ,concat('vw_',dbo.replace_illegal_characters(v.name)) as control_proc_id 
 ,dbo.replace_illegal_characters(v.name) as template_id 
 FROM [demo].sys.views v
 order by prog_object_id
-- variables for the cursor
declare @prog_object_id prog_object_id
 ,@control_proc_id control_proc_id
 ,@template_id template_id
-- open the cursor
open cursor_voorbeeld

-- Get the next one
fetch next from cursor_voorbeeld into @prog_object_id, @control_proc_id, @template_id

-- @@FETCH_STATUS = 0 means as long as you can get a new record out of the cursor
while @@FETCH_STATUS = 0
begin

 EXEC task_template_assignment_add
 @project_id = @project_id,
 @project_vrs_id = @project_vrs_id,
 @prog_object_id = @prog_object_id,
 @control_proc_id = @control_proc_id,
 @template_id = @template_id


 --Get the nex view
 fetch next from cursor_voorbeeld into @prog_object_id, @control_proc_id, @template_id

end


-- Close and forget the cursor
close cursor_voorbeeld

deallocate cursor_voorbeeld