As Griff said, this seems like a bad design, and possibly a case for
the EAV model[
^].
In order to select the records, you're going to need to use dynamic SQL. You'll obviously need to take all relevant precautions to avoid introducing a SQL Injection vulnerability.
At the basic level, you'd need something like this:
DECLARE @TableName nvarchar(257), @command nvarchar(4000), @params nvarchar(100);
SELECT
@TableName = QUOTENAME(S.name) + N'.' + QUOTENAME(T.name)
FROM
sys.tables As T
INNER JOIN sys.schemas As S
ON S.schema_id = T.schema_id
WHERE
T.object_id = @table_object_id
;
SET @command = N'SELECT * FROM ' + @TableName + N' WHERE [PK] = @PK';
SET @params = N'@PK uniqueidentifier';
EXEC sp_executesql @command, @params, @PK = @PK;
sp_executesql (Transact-SQL) | Microsoft Docs[
^]