CODEWITHSUNDEEP
sqlsql-serverdatabase
Joni
Joni

Reputation: 11

Select from table with object_id

Is is it possible to select Data like this in SQL Server?

select * 
from table(object_id)

I would like so pass the object_id as parameter to the select because I need to select from a global temptable which looks different and has a different name every time. The object_id I get like this:

select object_id('tempdb..##temptableXXX')

Thanks for your help!

Upvotes: 0

Views: 9600

Answers (2)

M.Ali
M.Ali

Reputation: 69494

You can do the following, even though the answer looks a lot like what has already been provided. However this allows you to add more than just tables in the i.e. table valued functions views etc.

It also takes into account that the object is not in calling user's default schema and uses two-part name in the from clause i.e. [Schema].[Table]

Also it protects you against SQL-Injection attacks, using QUOTENAME() function.

/* input param*/
Declare @object_id  INT = 117575457;


Declare @SchemaName SYSNAME 
      , @TableName  SYSNAME
      , @Sql        NVARCHAR(MAX);

SELECT  @TableName  = [name] 
      , @SchemaName = SCHEMA_NAME([SCHEMA_ID]) 
from    sys.objects
WHERE   [type_desc] = 'USER_TABLE'  --<- Can use IN Clause to add functions veiws etc
  AND   [object_id] = @object_id;   --<-- User Input variable

 SET @sql = N' SELECT * FROM ' 
          + QUOTENAME(@SchemaName) + N'.' + QUOTENAME(@TableName); 

 Exec sp_executesql @sql;

Upvotes: 2

Vidmantas Blazevicius
Vidmantas Blazevicius

Reputation: 4802

You could do that with a little bit of T-SQL:

declare @tablename nvarchar(max)
select @tablename = name from sys.tables
where object_id = select object_id('tempdb..##temptableXXX')

declare @sql nvarchar(max) =
'select * from ' + @tablename
exec (@sql)

Upvotes: 5

Related Questions