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

Our new SQL Server Forums are live! Come on over! We've restricted the ability to create new threads on these forums.

SQL Server Forums
Profile | Active Topics | Members | Search | Forum FAQ
Save Password
Forgot your Password?

 All Forums
 SQL Server 2008 Forums
 Transact-SQL (2008)
 SELECT FROM Multiple tables with names in a table
 Reply to Topic
 Printer Friendly
Author Previous Topic Topic Next Topic  

Starting Member

3 Posts

Posted - 03/27/2013 :  14:31:13  Show Profile  Reply with Quote
I am trying to do a query that I am not sure can be written without using a cursor and building dynamically. But if someone can help me figure out how to do it without having to use cursors that would be awesome.

So I have a table called AppSystem.ApplianceTypes. It has a varchar column named ApplianceTypeTableName that contains the name of other tables in the form of schema.tablename. There are 71 rows of tablenames in the AppSystem.ApplianceTypes table. Each of the 71 tables have a column inside them called MFG. What I want to do is do a query that lists the MFG values in all 71 tables in the same query (like a UNION). By the way, the 71 rows in AppSystem.ApplianceTypes will grow in the future as we add new tables.

If someone can figure this out, I will be praising them as a SQL King/Queen. :) If you don't think it can be done without cursors just let me know and I will figure that part out using the cursor.


Almighty SQL Goddess

38200 Posts

Posted - 03/27/2013 :  15:36:54  Show Profile  Visit tkizer's Homepage  Reply with Quote
select 'select MFG from ' + ApplianceTypeTableName + 'union all '
from AppSystem.ApplianceTypes

Run that and copy/paste the output into a new window. Remove the last "union all". If you need to do this programmatically instead, you'll need dynamic SQL.

And this is not a good database design. I would recommend fixing it now rather than waiting as queries it's likely your queries are going to need to be more and more complex due to this design problem.

Tara Kizer
Microsoft MVP for Windows Server System - SQL Server

Subscribe to my blog
Go to Top of Page
  Previous Topic Topic Next Topic  
 Reply to Topic
 Printer Friendly
Jump To:
SQL Server Forums © 2000-2009 SQLTeam Publishing, LLC Go To Top Of Page
This page was generated in 0.02 seconds. Powered By: Snitz Forums 2000