Searching through different tables?

I have several tables with a Sequence as the primary key.

They are unrelated at all, there are no foreign keys or anything, or anything that could link one to another, so join won’t work.

Union won’t work either because they are basically different tables.

I know that you could assign “null” to those columns that exist in one table but not another, if that’s the case, I could just make everything into one big table with `Sparse Columns’, but I just “personally” don’t like a table with too many null values.

The only relationship they got is that they share a Sequence as a primary key, so they all have a unique primary key, even across each other.

So, how can I “effectively” search through all these tables for the unique primary key I’m looking for instead of selecting through each table until a non-null result is returned?

Thank you very much for your help!

Answer

If it suits you, something with dynamic query can look like this:

create table tableA ( id int, col1 char ,col2 char );
create table tableB ( id int, col3 int ,col4 int );
create table tableC ( id int, col5 bit ,col6 bit );

insert tableA values (1, 'a', 'b');
insert tableB values (2, 2, 2);
insert tableC values (3, 0, 1);
 
declare @sql nvarchar(max);

with cte_union AS 
(
    select id, 'tableA' as t from tableA
    union all
    select id, 'tableB' from tableB
    union all
    select id, 'tableC' from tableC
)
select @sql = 'SELECT * FROM ' + t + ' WHERE id = ' + CAST(id AS nvarchar(MAX)) 
from cte_union 
where id = 1;   --your @id param

exec sp_executesql @sql;