Query from multiple tables based on the table name?

Question

I have hundreds of tables in SQL where they are named in the format such as dbo.exampledata_2024-01-01 and a new table is created every week. I want to pull the data I need from either the last 12 tables or the last 3 months based on the dates on the name? Is this possible?

I'm not entirely sure where to start with this problem

Answer 1

The following approach makes use of the information_schema.tables to locate the tables that match the given naming convention. Note, if the last 10 chars of a table name cannot be converted to a date the script will error. Please consider the following purely as an example of the complexities that will always arise from the multiple table approach you have now:

DECLARE @DynamicSQL NVARCHAR(MAX) = '';
DECLARE @TableName NVARCHAR(100);

/* alter the next line to adjust the period e.g. 12 weeks, 60 days etc. */
DECLARE @StartDate DATE = DATEADD(MONTH, -3, GETDATE());

DECLARE table_cursor CURSOR FOR
SELECT TABLE_NAME
FROM INFORMATION_SCHEMA.TABLES
WHERE TABLE_NAME LIKE 'dbo.exampledata_%'
AND CAST(RIGHT(TABLE_NAME, 10) AS DATE) >= @StartDate;

OPEN table_cursor;
FETCH NEXT FROM table_cursor INTO @TableName;

WHILE @@FETCH_STATUS = 0
BEGIN
    SET @DynamicSQL = @DynamicSQL + 
    'SELECT * FROM ' + @TableName + ' UNION ALL ';

    FETCH NEXT FROM table_cursor INTO @TableName;
END

CLOSE table_cursor;
DEALLOCATE table_cursor;

SET @DynamicSQL = LEFT(@DynamicSQL, LEN(@DynamicSQL) - 10); -- Removes the last 'UNION ALL'

EXEC sp_executesql @DynamicSQL;

Also note this dynamic query is juat a union of all the tables - you haven't specified any details of what that query should do.