query to check all SCD tables that have data for specific date
You should be able to create a stored procedure that executes your query to get the table names. Then you loop over the results and execute your effective date query for each table individually.

You might even be able to do this without wrapping it into an stored procedure.