SQL Server Forums
Profile | Register | Active Topics | Members | Search | Forum FAQ
 
Register Now and get your question answered!
Username:
Password:
Save Password
Forgot your Password?

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

dcuffee
Starting Member

USA
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.

Thanks,
David

tkizer
Almighty SQL Goddess

USA
37167 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
http://weblogs.sqlteam.com/tarad/

Subscribe to my blog
Go to Top of Page
  Previous Topic Topic Next Topic  
 New 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.05 seconds. Powered By: Snitz Forums 2000