How to Union dynamic tables

[Alan Woods]

Hi,
I need to query on some data. The data is from SMS2003, stored in SQL2005, all on a Windows2003 server. I write a few queries, but I consider myself an SQL beginner at best. Normally, I write simple queries like "select column1, column2, column4 from a_table where column2 = 'something'". That's about 90% of my writing ability.

Here's the challenge:
I need a query that can query a table and select a list of other tables and then list all or part of those tables. It also needs to be able to know not to list one of those tables. The schema for the first table is different from the rest, naturally, but the rest of the tables are all the same layout.

Here's an example:

MasterTable
FriendlyName     TrueName
===============================
Applications     Applications0
Applications     Applications1
Applications     Applications2
Systems          Systems0
Applications     Applications3
Applications     Applications4
Applications     Applications5
Systems          Systems1
===============================
 
Applicaitions(x)     <- (x) is a number, like 0, or 1, or 2, etc.
AppName     ProgName         Version
==========================================
Word        winword.exe      10.5.6.7
Notepad     notepad.exe      2.4.5.6
==========================================

I want to write something like:

Select AppName, ProgName
From UnionEverythingFoundIn
      (
      Select Truename
      From MasterTable 
      Where 
            FriendlyName = 'Applications'
            And
            TrueName <> 'Applications0'
      )

The problem is that I don't know of a command for "UnionEverythin gFoundIn", and I don't know how to have SQL loop through the list to dynamically build the unions like "Applicatio ns1 Union Applications2 Union...etc".

We've done this in TQL like:

USE SMS_DB
GO
DECLARE get_TableNames CURSOR FOR 
SELECT TrueName FROM MasterTable
Declare @Tablename as varchar(25)
OPEN get_TableNames
FETCH NEXT From get_TableNames into @TableName
WHILE @@FETCH_STATUS = 0
BEGIN
      Exec('SELECT * From ' + @TableName)
      FETCH NEXT FROM get_TableNames into @TableName
END
CLOSE get_TableNames
DEALLOCATE get_TableNames
RETURN

This works in the SQL Analyser but it's not "normal SQL" enough for SMS to handle it as a query.

My options, in order of preference, are:
1. A query that SMS can handle that does it all.
2. A View or Proceedure that does the hard part, but can be seen or called by SMS one demand.
3. A Job or Proceedure that SQL can run on a schedule that creates yet another table that SMS can query simply (like "Select * from new_table")

I'm putting max points on this because we need it and haven't been able to figure it out ourselves. Hopefully it will be a bit of a challenge, but not so much that you can't solve it.

Thanks,
Alan