Get table name from another table

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

General SQL Server Forums

New to SQL Server Programming

Get table name from another table

Author

Topic

spope
Starting Member

2 Posts

Posted - 2010-04-13 : 13:47:14

We have a program that takes historical temperature and pressure readings for various sensors. When a log is setup the program creates another table in the database, and appends a master list table with the name of the new table and a description. If we have 10 different sensors that we are monitoring, the database will have 11 tables:
tblTrend_0000 - filled with the trend data from sensor 0
.
.
.
tblTrend_0009 - filled with the trend data from sensor 9
tblMastList - filled with the following data
Outside Temp tblTrend_0000
.
.
.
Inside Temp tblTrend_0009

We would like to be able to query the correct table based on the plain text description instead of having to know what the table name of the table that it is stored in. Is there a way to do this in SQL or are we going to be forced to do it in a higher level program?

Thank you for your help.

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2010-04-13 : 14:54:15

You will need to use dynamic SQL for this, which is not good for performance or security. I assume that the program can't be changed, so please read up on dynamic SQL.

Here is a quick example:

DECLARE @s varchar(10), @sql varchar(500)

SELECT @s = TableName
FROM SomeTable
WHERE SomeColumn = 'SomeValue'

SET @sql = 'SELECT * FROM ' + @s

EXEC(@sql)

Tara Kizer
Microsoft MVP for Windows Server System - SQL Server
http://weblogs.sqlteam.com/tarad/

Subscribe to my blog

spope
Starting Member

2 Posts

Posted - 2010-04-13 : 15:39:31

That worked well for entering a new query directly, but everytime I try incorporate that into a function I get errors. When I try to do a table function, I get error on my first DECLARE @s. When I try to do scalar functions I get errors that I cannot return a datatype of table.

Thanks!

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2010-04-13 : 18:48:16

You can't use dynamic SQL in functions, so you will be unable to do what you want in a function. There is no workaround with a function except to hard-code the table names.

Tara Kizer
Microsoft MVP for Windows Server System - SQL Server
http://weblogs.sqlteam.com/tarad/

Subscribe to my blog

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2010-04-15 : 08:31:02

Make sure you read this fully
www.sommarskog.se/dynamic_sql.html

Madhivanan

Failing to plan is Planning to fail

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources