This modification will make sure you include only actually existing tables and do not miss any of them:

declare @SQLCmd nvarchar(max), @params nvarchar(max), @DiscDate datetime ;

SET @DiscDate = '20090630';

set @params = N'@BranchID varchar(10), @DiscDate date';

select

@SQLCmd = ISNULL(@SQLCmd, 'select * from TableCurrent where branchid = @BranchID and (disc_dte is null or disc_dte > @DiscDate)')

+ char(13) + char(10) + 'UNION ALL ' + char(13) + char(10)

+ 'select * from ' + QUOTENAME(O.NAME ) + ' where branchid = @BranchID and (disc_dte is null or disc_dte > @DiscDate)'

--SELECT *

FROM sys.objects AS O

WHERE O.type = 'U' AND O.name LIKE 'Table[_]20[0-1][0-9]%[_]20[01][0-9]'

print @SQLCmd;

exec sp_executesql @SQLCmd, @params = @params, @BatchID = '950', @DiscDate = @DiscDate;

And if you want to avoid unnecessary scanning too far in the history you may add an extra condition into the WHERE clause.

It will include only those tables which can actually contain records from the date period you're interersted in:

declare @SQLCmd nvarchar(max), @params nvarchar(max), @DiscDate datetime ;

SET @DiscDate = '20090630';

set @params = N'@BranchID varchar(10), @DiscDate date';

select

@SQLCmd = ISNULL(@SQLCmd, 'select * from TableCurrent where branchid = @BranchID and (disc_dte is null or disc_dte > @DiscDate)')

+ char(13) + char(10) + 'UNION ALL ' + char(13) + char(10)

+ 'select * from ' + QUOTENAME(O.NAME ) + ' where branchid = @BranchID and (disc_dte is null or disc_dte > @DiscDate)'

--SELECT *

FROM sys.objects AS O

WHERE O.type = 'U' AND O.name LIKE 'Table[_]20[0-1][0-9]%[_]20[01][0-9]'

AND O.name >= 'Table_' + CONVERT(nvarchar(128), YEAR(@DiscDate)-1)

print @SQLCmd;

exec sp_executesql @SQLCmd, @params = @params, @BatchID = '950', @DiscDate = @DiscDate;