CODEWITHSUNDEEP
sql-server
WonderWorker
WonderWorker

Reputation: 9062

Reference a table by object_id in SQL Server

I need a method of querying tables by their id instead of their name.

Using:

DECLARE @TableId AS INT = 1340567

I need an equivalent to the following example:

SELECT TableName.* FROM TableName WHERE TableName.Id BETWEEN 100 AND 199

Note: The TableName.Id is TableName's primary key.

I imagine it to look something like:

DECLARE @TableId AS INT = 1340567

SELECT GetTable(@TableId).* FROM GetTable(@TableId) WHERE GetTable(@TableId).Id BETWEEN 100 AND 199

But obviously, that doesn't work.

Note: I am aware that object_ids change over time as SQL Server drops and recreates the tables internally. It is out of interest only and I'm having difficulty finding information about it.

Upvotes: 0

Views: 490

Answers (2)

WonderWorker
WonderWorker

Reputation: 9062

Don't do this.

This is a really bad idea since an object's Id will change over time as SQL Server drops and recreates tables internally.

Upvotes: 0

Gordon Linoff
Gordon Linoff

Reputation: 1269773

What you want to do requires dynamic SQL, something like this:

declare @sql nvarchar(max);

set @sql = 'select * from [table] where id between 100 and 199';

set @sql = replace(@sql, '[table]', quotename(object_name(@tableid)));

exec sp_executesql @sql;

Upvotes: 3

Related Questions