Sometimes you'd like to go a do a quick string search and replace of SQL for your captured triggers, procedures, functions or views.
The DDL for these objects is held within different migration repository tables. You can create the following view within your migration repository and use it to update the underlying migration repository.
CREATE OR REPLACE VIEW MGV_ALL_CAPTURED_SQL AS
WITH
captured_schema AS
(SELECT SCHEMA_ID
FROM mgv_all_schema
WHERE connection_id IN
(SELECT ID FROM md_connections WHERE NVL(type,'Captured') != 'CONVERTED')),
captured_connections AS
(SELECT ID FROM md_connections WHERE NVL(type,'Captured') != 'CONVERTED') ,
captured_view_trigger AS
(SELECT v.trigger_id vt
FROM mgv_all_view_triggers v
WHERE v.connection_id IN
(SELECT * FROM captured_connections)) ,
captured_table_trigger AS
(SELECT t.trigger_id tt
FROM mgv_all_table_triggers t
WHERE t.connection_id IN
(SELECT * FROM captured_connections))
SELECT ID,'md_stored_programs' ObjType, Name objectName, native_sql
FROM md_stored_programs,
captured_schema
WHERE language = 'MSTSQL'
AND SCHEMA_ID_FK = captured_schema.schema_id
UNION ALL
SELECT ID,'md_views' ObjType, view_Name objectName, native_sql
FROM md_views,
captured_schema
WHERE language = 'MSTSQL'
AND SCHEMA_ID_FK = captured_schema.schema_id
UNION ALL
SELECT ID,'md_triggers' ObjType, trigger_Name objectName, native_sql
FROM md_triggers
WHERE language = 'MSTSQL'
AND (md_triggers.id in (select vt from captured_view_trigger union select tt from captured_table_trigger ));
This view lists the procedures,function,views and triggers in the captured model.
Using it we can update those objects SQL.
update md_views
set native_sql = replace(native_sql, 'noexpand', 'noexpand nolock')
where id in ( select id from mgv_all_captured_sql where native_sql like '%noexpand%') ;
update md_stored_programs
set native_sql = replace(native_sql, 'noexpand', 'noexpand nolock')
where id in ( select id from mgv_all_captured_sql where native_sql like '%noexpand%') ;
update md_triggers
set native_sql = replace(native_sql, 'noexpand', 'noexpand nolock')
where id in ( select id from mgv_all_captured_sql where native_sql like '%noexpand%');
commit;
In the above example I'm replacing all occurances of "noexpand" with "noexpand nolock". This is a bug in 1.5 (fixed for 1.5.1.) where the Microsoft documentation of noexpand is not complete. I needed to add "nolock" after noexpand for the translator to recognize the syntax.
