| Author |
Topic |
|
Hommer
Aged Yak Warrior
808 Posts |
 Posted - 2012-12-11 : 11:37:16
|
| Hi, my sqlteam gurus,I don't even know how to google this one, but I think if I describe the task at hand, some of you may have an answer.Select rows from mutiple tables where they all have a known column name and that column has a given value.So this next gave me my list of tablesselect object_Name(Object_ID) from sys.columns where name ='MyIDCol'I can copy/paste each tbl intoSelect * from mytbl where MyIDCol = 'myKnownValue'My question is can I do away with this manual copy/paste? Also, how can I make the query only return those NOT (0 row(s) affected)SELECTs?Thanks! |
|
|
sunitabeck
Master Smack Fu Yak Hacker
5155 Posts |
Posted - 2012-12-11 : 11:50:17
|
| See if this thread will help you - look for Elizabeth Darcy's reply. Your case is slightly different, but that can be accommodated by changing the string that is constructed. |
 |
|
|
Hommer
Aged Yak Warrior
808 Posts |
Posted - 2012-12-11 : 13:15:04
|
| Ok, thanks!The #table part is easy. But @sql needs more work.DECLARE @sql NVARCHAR(4000);SET @sql = STUFF( ( SELECT ' UNION ALL SELECT * FROM ' + QUOTENAME(tbl) 'where [myEntityId] =12345-5CD9-BBAE-DECD-abcdf' FROM #t ) ,1,11,'')EXEC( @sql );That run into subquery returned more than 1 value.Well, for now I have copied/pasted through the process. I will work on my dynamic sql later for next time. |
|
|
|
sunitabeck
Master Smack Fu Yak Hacker
5155 Posts |
Posted - 2012-12-11 : 13:57:14
|
| Probably the way to debug it is to replace the "EXEC( @sql );" with "PRINT @sql" and examine the code that is printed out to see what it does and whether it is indeed generating a valid query. You could also copy and paste the query and run it to see the error and then tweak the query. |
|
|
|
Hommer
Aged Yak Warrior
808 Posts |
Posted - 2012-12-11 : 15:08:06
|
| Oh, I did try print @sql as well, but same error. Too many things in this sample code are new to me. I need to figure out which part applys to my case.i.e. Stuff(), Union All Selectr, QuoteName... |
|
|
|
LoztInSpace
Aged Yak Warrior
940 Posts |
Posted - 2012-12-11 : 20:07:53
|
| What are you doing this for? If you don't mind me saying so, if it's more than idle curiosity, you are most likely doing something very wrong. |
|
|
|
Hommer
Aged Yak Warrior
808 Posts |
Posted - 2012-12-12 : 13:26:56
|
| Well, I am trying to locate a piece of data users have inputed. And, I don't know the table or column name that will contain the value. But I do know those tables should have a column named AppID, and the value for that column should be 123. So my first select gave me all the tables that has the column APPID... |
|
|
|
sunitabeck
Master Smack Fu Yak Hacker
5155 Posts |
Posted - 2012-12-12 : 14:22:50
|
| Have you solved your problem, or are you still looking for a solution? |
|
|
|
Hommer
Aged Yak Warrior
808 Posts |
Posted - 2012-12-12 : 14:59:08
|
| quoting myself:"Well, for now I have copied/pasted through the process (for each table). I will work on my dynamic sql later for next time." |
|
|
|
|
Select rows from mutiple tables w/ known col name