Howto Query a Table if I only know it's Object_Id

[tyreejp]

Is there a way to query a table if I only know the table's Object_Id?

Anything like....

Select * from Object_Name(12345678)

TIA!

James

 

[SQLDenis]

declare @name varchar(50)
select @name = object_name(12345678)

exec ('select * from ' + @name )

Denis The SQL Menace
SQL blog:

http://sqlservercode.blogspot.com/

Personal Blog:

http://otherthingsnow.blogspot.com/

 

[Jamfool]

select [id],[name] from sysobjects


select object_name(6)

 

[tyreejp]

Thanks for the quick replies. I've done it your way, SQLDenis, but I've got 10 stored procedures all hitting the same global temp table and there are SO MANY select statments inside each of the stored procedures, it would be almost impossible to go back through them all and change them from they way they are to "exec ('Select * from...".

I also looked into trying it your way, Jamfool, with the "select object_name(12345)" but it only returns the name of the table that belongs to that object_id.

I was hoping for a way to return the records that are in any given table based on its object_id (I need to randomize the table name so two people can work with the global temp tables at the same time).

I guess I just need to be able to access a table's records without having to spell out the table's name in my select statment without doing it this way:

exec ('select * from ' + @name )

--
James

http://www.microsoftjr.com

 

[Jamfool]

sorry the select objectname will only return the object to which the id relates. it wasnt menat as a way to select from the table.

can you maybe give more detail behind the use of your global temp table, and why you only have the objectid. there may be an alternative way to design a solution.

using exec 'select * from' + @tablename is obviously a very bad way to code.

 

[tyreejp]

Sure!

I have a report in SSRS that calls a stored procedure passing parameters as to whether the report should be run for monthly, quarterly, or yearly figures. The stored procedure the report calls builds a global temp table with all the data the report needs. I do this for performance reasons so I don't have to hit the actual tables for data over and over again. The stored procedure that the report calls also calls upon 11 other stored procedures (1 for each section of the report) to hit this global temp table to perform its section's calculations. I had to use a global temp table so each successive stored procedure had access to the same set of temporary data. The BIG draw back is that if two people try to run the report at the same time, the first person to run the report with get an error because the data changed on them.

--
James

http://www.microsoftjr.com

 

[SQLDenis]

>> I had to use a global temp table so each successive stored procedure had access to the same set of temporary data.


Not true as long as the temp table is created in the first proc you can use a local ( 1 #) temp table and the procs that are called from the first proc will have acces to the temp table

example code

--proc 1
create proc proc1 
as

create table #temp (SomeDate datetime)
exec proc2

select * from #temp
go

--proc2
create proc proc2
as 
--insert into temp table created in proc 1
insert #temp values (getdate())
go



--execute proc 1
exec proc1

drop proc proc1,proc2

Denis The SQL Menace
SQL blog:

http://sqlservercode.blogspot.com/

Personal Blog:

http://otherthingsnow.blogspot.com/

 

[tyreejp]

SQLDenis, that's awesome!

Thanks SO much!!!!!

--
James

http://www.microsoftjr.com

 

[SQLDenis]

No problem

Denis The SQL Menace
SQL blog:

http://sqlservercode.blogspot.com/

Personal Blog:

http://otherthingsnow.blogspot.com/

 

[Jamfool]

much better solution