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:
Expand|Select|Wrap|Line Numbers
- 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
- ==========================================
Expand|Select|Wrap|Line Numbers
- Select AppName, ProgName
- From UnionEverythingFoundIn
- (
- Select Truename
- From MasterTable
- Where
- FriendlyName = 'Applications'
- And
- TrueName <> 'Applications0'
- )
We've done this in TQL like:
Expand|Select|Wrap|Line Numbers
- 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
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