通过object_id查询表 [英] Query the table by object_id

问题描述

请建议



有不同OBJECT_ID-s的表引用相应的表格，其中每条记录的数据选自



是否可以将该表作为其OBJECT_ID引用的记录集



如下< br $>

 select * from（GET_RECORDSET_FROM_TABLE_BY_OBJECT_ID（@table_object_id））

我的尝试：



T-SQL论坛，MSDN，在线帮助

解决方案

正如格里夫所说，这似乎是一个糟糕的设计，可能是 EAV模型 [ ^ ]。



为了选择记录，您将需要使用动态SQL。你显然需要采取所有相关的预防措施，以避免引入SQL注入漏洞。



在基础级别，你需要这样的东西：

  DECLARE   @ TableName   nvarchar （ 257 ）， @ command   nvarchar （ 4000 ）， @ params   nvarchar （ 100 ）; 
 
  SELECT  
  @ TableName  = QUOTENAME（S.name） + N ' 。' + QUOTENAME（T.name）
  FROM  
 sys.tables 作为 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 [ ^ ]

您的意思是加入： SQL连接 [ ^ ]

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

解决方案

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[^]

Do you mean a JOIN: SQL Joins[^]

这篇关于通过object_id查询表的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！