How to get the Maximum date across all the tables in a database considering the date column is same-CodePudding

I want to get the Maximum date from all the tables in my database. I am using user table ALL_TABLES to get the table_name and column_name but I'm not bale to extract the max date of all the tables present in a database.

SELECT MAX(dt_load)
  FROM (SELECT table_name, column_name
          FROM all_tables
         WHERE column_name = 'DT_LOAD');

I know that I need to use Dynamic SQL but I'm not able to get that

CodePudding user response：

You can generate a query using:

SELECT 'SELECT MAX(max_dt) FROM (' AS query FROM DUAL
UNION ALL
SELECT CASE WHEN ROWNUM > 1 THEN ' UNION ALL ' END
       || 'SELECT MAX(dt_load) AS max_dt FROM "' || owner || '"."' || table_name || '"'
FROM   all_tab_columns
WHERE  column_name = 'DT_LOAD'
UNION ALL
SELECT ')' FROM DUAL;

If you want to make it in to a single statement then you could use LISTAGG but it will fail if the query is more than 4000 characters or you can aggregate into a CLOB:

DECLARE
  v_sql      CLOB;
  v_max_date DATE;
BEGIN
  FOR query IN (
    SELECT 'SELECT MAX(max_dt) FROM (' AS query FROM DUAL
    UNION ALL
    SELECT CASE WHEN ROWNUM > 1 THEN ' UNION ALL ' END
           || 'SELECT MAX(dt_load) AS max_dt FROM "' || owner || '"."' || table_name || '"'
    FROM   all_tab_columns
    WHERE  column_name = 'DT_LOAD'
    UNION ALL
    SELECT ')' FROM DUAL
  )
  LOOP
    v_sql := v_sql || query.query;
  END LOOP;

  EXECUTE IMMEDIATE v_sql INTO v_max_date;
  DBMS_OUTPUT.PUT_LINE(v_max_date);
END;
/

Which, for the sample data:

CREATE TABLE table1 (dt_load) AS
SELECT SYSDATE FROM DUAL;

CREATE TABLE table2 (id, dt_load) AS
SELECT LEVEL, TRUNC(SYSDATE) - LEVEL FROM DUAL CONNECT BY LEVEL <= 5;

Outputs:

2022-11-21 12:24:54

fiddle

CodePudding user response：

With some sample tables:

SQL> create table test as select 1 id, date '2022-11-21' dt_load from dual;

Table created.

SQL> create table test_2 as select 1 id, date '2022-08-15' dt_load from dual;

Table created.

Dynamic SQL, as you said:

SQL> declare
  2    l_max_date date;
  3    l_max_overall date := date '0001-01-01';
  4  begin
  5    for cur_R in (select table_name
  6                  from user_tab_columns
  7                  where column_name = 'DT_LOAD'
  8                 )
  9    loop
 10      execute immediate 'select max(dt_load) from ' || cur_r.table_name into l_max_date;
 11      dbms_output.put_line(cur_r.table_name ||': '|| to_char(l_max_date, 'dd.mm.yyyy'));
 12      l_max_overall := greatest(l_max_overall, l_max_date);
 13    end loop;
 14    dbms_output.put_line('Overall MAX date: ' || to_char(l_max_overall, 'dd.mm.yyyy'));
 15  end;
 16  /
TEST: 21.11.2022
TEST_2: 15.08.2022
Overall MAX date: 21.11.2022

PL/SQL procedure successfully completed.

SQL>