0

I want to create a database where there is a list of table names stored in a table. Now with the help of this list I can access the other tables.

Ex :-

Table name :- table_list (2 column i.e. table_name,table_id)
table_list attributes 
authentication 1 
basic_info 2 
contact 3

I can directly access these tables using select statement but I want to access them using the table_list table preferably using select statement.

I tried 

select * from (select table_name as x from table_list where id=2) as y

But could not get the proper output.

Improve this question
2
  • 1
    what are you trying to achieve?Whats the point of storing table name in another table.If its for security purpose,you can create views and give limited access to users. Commented Mar 17, 2013 at 5:09
  • For my project I have already created views. But I wanted to achieve some dynamic approach to retrieve data from various tables. Also this method can provide security which is a plus point. Commented Mar 21, 2013 at 3:19

4 Answers 4

Reset to default
2

It is called Prepared Statements and their only use is when you want to implement your mentioned need in one request. Otherwise you can easily retrieve table names in a programming language and create your next statement using the data in hand. Here's how Prepared Statements work:

SELECT table_name INTO @tbl FROM my_tables WHERE id = 1 LIMIT 1;
SET @sql := CONCAT('SELECT * FROM ', @tbl);
PREPARE stmt1 FROM @sql;
EXECUTE stmt1;
DEALLOCATE PREPARE stmt1;
Improve this answer
Sign up to request clarification or add additional context in comments.

3 Comments

Sorry, I didn't have the time to test it. You are right, it's not working. I have updated it now so it works. Thanks to point it out.
SELECT getname.tbnm INTO @tbl FROM getname WHERE getname.id = 1 LIMIT 1; SET @sql := CONCAT('SELECT * FROM ', @tbl); PREPARE stmt1 FROM @sql; EXECUTE stmt1; @Mehran I tried your give statement and everything went perfect except for the last DEALLOCATE PREPARE stmt1;.
What was the problem with DEALLOCATE? I tried it myself and it works! Perhaps your question is more like what it does? In that case I must say it removes the previously created Prepared Statement and you need to call it when you have no longer the need to use the statement. Other than that I don't have any idea what might go wrong with deallocation!
0 
TRY THIS

select * from (SELECT TABLE_NAME FROM TABLE_LIST WHERE ID=2)as y
Improve this answer

1 Comment

This statement will return values from the TABLE_NAME column in the TABLE_LIST. How is this any different from the query posted by the OP?
0

The table name (an "identifier") must be a static part of the SQL text issed to the database; the identifier can't be supplied "on the fly", either as a parameter or as a result from another SQL query.

To do what you want to do, you will need a two step approach. You can use one (or more) SQL statements to obtain the identifiers you need (table name, column names, etc.), and then use that to dynamically create a second SQL statement, as a string.

The identifiers (table names, column anmes) can not be provided as parameters or "bind variables", they must be a static part of the SQL text.

For example, to generate the statement:

SELECT CONCAT('SELECT * FROM `',table_name,'` ORDER BY 1') AS stmt
  FROM table_list
 WHERE id = 2

(The coding details are dependent on what language you are using.)

Improve this answer

Comments

0

Since you are sure that the table name you want to access is x, just check whether such a table exists using a query and use x for future purpose.

Improve this answer

Comments

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.