sql - Return data before current month

Question

The following code returns data from 11 tables. Each table contains the year and month. I need to make use of an enddate parameter to check that only tables todate are pulled out. So if the user wants data from 201505 it should pull out all tables only till 201509 as from 201510 tables do not exists assuming we in the month of 201510. Code below:

{

declare 
@enddate varchar(6),
@FirstTableMonth int =201505,
@Table_Name sysname,
@TableMonth int,
@end int,
@CurrentMonth int = 0,
@NextYearMonth int = 1

set @enddate = 201611

WHILE @CurrentMonth < 11
BEGIN

    SELECT  @TableMonth =   CASE WHEN (@FirstTableMonth + @CurrentMonth) % 100 < 13 THEN 
                                @FirstTableMonth + @CurrentMonth 
                            ELSE
                                @FirstTableMonth + 100 - (@FirstTableMonth % 100) + @NextYearMonth
                            END,
                    @NextYearMonth =    CASE WHEN (@FirstTableMonth + @CurrentMonth) % 100 < 13 THEN 
                                    @NextYearMonth
                                ELSE
                                    @NextYearMonth + 1
                                END,
                    @end = case when @enddate 
                    @Table_Name =  'xx_'+CAST(@TableMonth as varchar)+'_T' 

                    SET @CurrentMonth = @CurrentMonth + 1
                    print @Table_Name;
END

}

Answer 1

You get all table names from INFORMATION_SCHEMA.TABLES. So just apply your criteria to get them:

select table_name
from information_schema.tables
where table_type = 'BASE TABLE'
and table_name like 'xx\_______\_T' escape ''  
and table_name >= 'xx_' + CAST(@FirstTableMonth as varchar) + '_T';

SQL fiddle: http://www.sqlfiddle.com/#!6/7b4f5/2