get all the tables within a date range

I have many weekly backup tables of the same structure and columns but with different names containing some user-specific information like this.

user_details
user_details_20211126
user_details_20211119
user_details_20211112
and so on....

Now I want to find the tables within a date range for example: If I choose the date range of 2021-11-20 to 2021-11-13 then the tables fetched would be user_details, user_details_20211126, user_details_20211119 for using them in a query containing union operation on all the fetched tables. Any idea on how to do that?

Answer

As already commented, what an awful idea … you shouldn’t be doing it that way. One table with a DATE datatype column should by the way to do it. I suggest you switch to it – the sooner, the better.

---

Meanwhile – although you didn’t name database you use & I’m using Oracle – here’s what you’ll probably have to do, regardless of the database: some kind of dynamic SQL as you have to “dynamically” compose the SELECT statement, using only tables that satisfy the condition. As you have to fetch their names from the data dictionary, you don’t have many options but – as I said – dynamic SQL.

OK, here you go.

Setting date format (you don’t have to do that):

SQL> alter session set nls_date_format = 'dd.mm.yyyy';

Session altered.

Tables whose names are user_details_something:

SQL> select table_name
  2  from user_tables
  3  where table_name like 'USER_DETAILS%'
  4  order by table_name;

TABLE_NAME
------------------------------
USER_DETAILS
USER_DETAILS_20211126
USER_DETAILS_20211127
USER_DETAILS_20211128

How to select only tables whose name contains date that is between desired values?

• query data dictionary (in Oracle, that’s user_tables)
• extract the DATE part of their names (I chose regexp_substr function; you’d use any you find appropriate)
• convert extracted substring into a valid date value (using TO_DATE function)

Therefore:

SQL> select table_name
  2  from user_tables
  3  where table_name like 'USER_DETAILS%'
  4    and to_date(regexp_substr(table_name, 'd+$'), 'yyyymmdd')
  5        between date '2021-11-26' and date '2021-11-27';

TABLE_NAME
------------------------------
USER_DETAILS_20211126
USER_DETAILS_20211127

OK; two tables between 26.11.2021 and 27.11.2021 (I didn’t feel like creating more of them).

---

The second part of the job is to actually fetch data from those tables. Here’s a function that accepts date period as parameters and returns ref cursor (you can choose something else, of course). Read comments within code:

SQL> create or replace function f_test(par_date_from in date,
  2                                    par_date_to   in date)
  3    return sys_refcursor
  4  is
  5    l_str varchar2(1000);  -- contains the whole SELECT statement
  6    rc    sys_refcursor;
  7  begin
  8    -- loop through all tables whose names satisfy the condition
  9    for cur_r in
 10    (select table_name
 11     from user_tables
 12     where table_name like 'USER_DETAILS%'
 13       and to_date(regexp_substr(table_name, 'd+$'), 'yyyymmdd')
 14           between par_date_from and par_date_to
 15    ) loop
 16      -- compose a SELECT statement
 17      l_str := l_str ||
 18        'select ename, job, datum from ' || cur_r.table_name || ' union all ';
 19    end loop;
 20
 21    -- remove trailing UNION ALL
 22    l_str := rtrim(l_str, ' union all');
 23
 24    -- open and return ref cursor
 25    open rc for l_str;
 26    return rc;
 27  end;
 28  /

Function created.

OK, let’s try it:

SQL> select f_test(date '2021-11-26', date '2021-11-27') from dual;

F_TEST(DATE'2021-11-
--------------------
CURSOR STATEMENT : 1

CURSOR STATEMENT : 1

ENAME      JOB       DATUM
---------- --------- ----------
SMITH      CLERK     26.11.2021
JONES      MANAGER   26.11.2021
SCOTT      ANALYST   26.11.2021
ADAMS      CLERK     26.11.2021
FORD       ANALYST   26.11.2021
ALLEN      SALESMAN  27.11.2021
WARD       SALESMAN  27.11.2021
MARTIN     SALESMAN  27.11.2021
BLAKE      MANAGER   27.11.2021
TURNER     SALESMAN  27.11.2021
JAMES      CLERK     27.11.2021

11 rows selected.


SQL>

---

It works for me; I hope you won’t have to make it “work” at all, but – after you apply what we’ve already suggested – run a simple

SQL> select * from user_details
  2  where datum between date '2021-11-26' and date '2021-11-27';

    DEPTNO      EMPNO ENAME      JOB       DATUM
---------- ---------- ---------- --------- ----------
        20       7369 SMITH      CLERK     26.11.2021
        20       7566 JONES      MANAGER   26.11.2021
        20       7788 SCOTT      ANALYST   26.11.2021
        20       7876 ADAMS      CLERK     26.11.2021
        20       7902 FORD       ANALYST   26.11.2021
        30       7499 ALLEN      SALESMAN  27.11.2021
        30       7521 WARD       SALESMAN  27.11.2021
        30       7654 MARTIN     SALESMAN  27.11.2021
        30       7698 BLAKE      MANAGER   27.11.2021
        30       7844 TURNER     SALESMAN  27.11.2021
        30       7900 JAMES      CLERK     27.11.2021

11 rows selected.

SQL>

Obviously, my user_details table contains the DATE datatype column and makes everything A LOT simpler.