1. 

   Dev, Member

   Hi,

   We are approaching ~100+ reports with myDBR. Is there a way to search for a field name across all the reports in the database or is there any way to do that? We'd like to know if we can change a certain field in one table, what other reports are using the same field.

   Thank you,
2. 

   myDBR Team, Key Master

   You can create a report for the search. As an example a generic search report for MariaDB/MySQL would look like this:

   CREATE PROCEDURE sp_DBR_find_report(in_search varchar(101))
   
   BEGIN
   
   select 'dbr.parameters.show';
   
   select 'dbr.hidecolumns', 'folder_id';
   
   select 'dbr.url', 'index.php?a=editor', '', '[Name]', 'm=folder_id', 'r=report_id', 'show_link=[is_report]>0';
   
   select 'dbr.url', 'index.php?a=editor', '', '[Name]', 'proc=Name', 'show_link=[is_report]==0';
   
   select 'dbr.url', 'index.php', '', '[Location]', 'm=folder_id', 'highlight=report_id';
   
   select r.name, ifnull(r.proc_name, p.specific_name) as 'Name', fn_report_location(r.proc_name) as 'Location', p.created as 'Created at',  r.folder_id, r.report_id, ifnull(r.report_id,0) as 'is_report'
   
   from information_schema.routines p
   
     left join mydbr_reports r on r.proc_name =p.routine_name
   
   where p.routine_definition like concat('%', in_search, '%' ) and p.routine_schema=database() and p.routine_name not like 'sp_MyDBR%'
   
   union
   
   select r.name, r.proc_name, fn_report_location(r.proc_name), p.created, r.folder_id, r.report_id, ifnull(r.report_id,0)
   
   from mydbr_reports r
   
     join information_schema.routines p on r.proc_name =p.routine_name and p.routine_schema=database()
   
   where r.proc_name = in_search or r.name like concat('%', in_search, '%' ); 
   
   END
   
   

   And the helper function fn_report_location:

   CREATE FUNCTION fn_report_location(in_procedure varchar(100)) RETURNS text CHARSET utf8mb3 COLLATE utf8mb3_general_ci
   
       READS SQL DATA
   
   BEGIN
   
   declare v_folder_id int;
   
   declare v_folder_id_orig int;
   
   declare v_depth int;
   
   declare v_breadcrumb text;
   
   declare v_foldername varchar(1024) character set utf8mb4;
   
   select r.folder_id, f.name into v_folder_id, v_breadcrumb
   
   from mydbr_reports r
   
     join mydbr_folders f on r.folder_id=f.folder_id
   
   where r.proc_name = in_procedure;
   
   while (v_folder_id is not null) do
   
     select mother_id into v_folder_id
   
     from mydbr_folders
   
     where folder_id=v_folder_id;
   
     if (v_folder_id is not null) then
   
       select name into v_foldername
   
       from mydbr_folders
   
       where folder_id=v_folder_id;
   
       if (v_foldername is not null) then
   
         select concat(v_foldername, ' → ', v_breadcrumb) into v_breadcrumb;
   
       end if;
   
     end if;
   
   end while;
   
   return v_breadcrumb;
   
   END
   
   

   The same for SQL Server:

   create procedure sp_DBR_find_report(
   
   @search varchar(max)
   
   )
   
   as
   
   begin
   
   select 'dbr.parameters.show';
   
   select 'dbr.hidecolumns', 'folder_id';
   
   select 'dbr.url', 'index.php?a=editor', '', '[Name]', 'm=folder_id', 'r=report_id', 'show_link=[is_report]>0'
   
   select 'dbr.url', 'index.php?a=editor', '', '[Name]', 'proc=Name', 'show_link=[is_report]==0'
   
   select 'dbr.url', 'index.php', '', '[Location]', 'm=folder_id', 'highlight=report_id'
   
   select r.name, isnull(r.proc_name, p.specific_name) as 'Name', dbo.fn_report_location(r.proc_name) as 'Location', p.created as 'Created at', r.folder_id, r.report_id, isnull(r.report_id,0) as 'is_report'
   
   from information_schema.routines p
   
     left join mydbr_reports r on r.proc_name =p.routine_name
   
   where p.routine_definition like '%'+@search+'%' and p.routine_catalog=db_name() and p.routine_name not like 'sp_MyDBR%'
   
   union
   
   select r.name, r.proc_name, dbo.fn_report_location(r.proc_name), p.created, r.folder_id, r.report_id, isnull(r.report_id,0)
   
   from mydbr_reports r
   
     join information_schema.routines p on r.proc_name =p.routine_name and p.routine_schema=db_name()
   
   where r.proc_name = @search or r.name like '%'+@search+'%'; 
   
   end
   
   

   And the function:

   create function fn_report_location( @report_name sysname )
   
   returns varchar(max)
   
   as
   
   begin
   
   declare @folder_id int
   
   declare @breadcrumb varchar(max)
   
   select @folder_id = r.folder_id
   
   from mydbr_reports r
   
   where r.proc_name = @report_name;
   
   with breadcrumb_cte (folder_id, breadcrumb)
   
   as (
   
   select f.mother_id, cast(f.name as varchar(max))
   
   from mydbr_folders f
   
   where f.folder_id=@folder_id
   
   union all
   
   select f.mother_id, cast(f.name as varchar(max))+' -> '+b.breadcrumb
   
   from breadcrumb_cte b
   
   join mydbr_folders f on f.folder_id=b.folder_id
   
   )
   
   select @breadcrumb=breadcrumb
   
   from breadcrumb_cte
   
   where folder_id is null
   
   return @breadcrumb
   
   end
   
   

   Hope this helps.

   --
   myDBR Team
3. 

   Dev, Member

   Thank you. It worked great!

Reply

You must log in to post.