Click here to Skip to main content
15,867,308 members
Please Sign up or sign in to vote.
0.00/5 (No votes)
See more:
please suggest

there is the table with different OBJECT_ID-s referencing to the corresponding tables where the data for each record selected from

whether it possible to get the table as the recordset referenced by its OBJECT_ID

something as follows

select * from (GET_RECORDSET_FROM_TABLE_BY_OBJECT_ID(@table_object_id))


What I have tried:

T-SQL forums, MSDN, online help
Posted
Updated 23-Oct-18 4:12am

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:
SQL
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[^]
 
Share this answer
 
Comments
[no name] 24-Oct-18 2:31am    
Hi!

This is not about design.
It's rather curious
All objects in the database have their own ID's.
When the SQL executes a query, it obviously “translates” the name of the requested table into the system identifier of this table, using the system view “all_objects” or some other way.
Then I wonder if I can refer to the table by its identifier not by name, as long as I can obtain the ID of this table.
Richard Deeming 24-Oct-18 7:32am    
The only way to query a table by its ID is to retrieve the name and use dynamic SQL. Whether or not SQL Server queries the table by ID behind the scenes, it doesn't provide any way for you to do that directly.
[no name] 29-Oct-18 3:39am    
>>Whether or not SQL Server queries the table by ID behind the scenes, it doesn't provide any way for you to do that directly.

Actually that was the question! :)
Do you mean a JOIN: SQL Joins[^]
 
Share this answer
 
Comments
[no name] 23-Oct-18 4:12am    
nope
Join is for chain the tables
this is different
I would like to refer to the table by it ID, not by name

this is like

select * from "Table_name"
but
select * from "Table_OBJECT_ID"
OriginalGriff 23-Oct-18 4:31am    
That would imply that you have a different (by identical schema) table for each object in your Objects table - and that's a poor idea because it means adding a table each time. They should be combined into a single table and a column added to allow you to reference (as a foreign key) the Objects table. You then use that key to select just the rows you want from one single table. By adding an index to that column, SQL can process it pretty efficiently.

You can do what you want, but it's both cumbersome in that you need to create a new command string then use EXEC to run it, and to add or remove objects you have to add and delete tables as well - which means you need table delete permissions for your user which is a very bad idea!
[no name] 23-Oct-18 8:45am    
apparently it was not so good example
I meant the table from which I select just one record by PK and then I'll bind the table by ID
Imagine the table that binds PartNumbers of the complex devices and simple devices.
Each device has its own properties an so described in separate table and obviously can be included in different devices
That is why I've created the "linkTable" which binds PartNumbers of the complex devices with the corresponded PN of their parts.

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)



CodeProject, 20 Bay Street, 11th Floor Toronto, Ontario, Canada M5J 2N8 +1 (416) 849-8900