| Author |
 Topic  |
|
|
Hommer
Aged Yak Warrior
693 Posts |
 Posted - 12/11/2012 : 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 tables
select object_Name(Object_ID) from sys.columns where name ='MyIDCol'
I can copy/paste each tbl into
Select * 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
Flowing Fount of Yak Knowledge
5152 Posts |
Posted - 12/11/2012 : 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
693 Posts |
Posted - 12/11/2012 : 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
Flowing Fount of Yak Knowledge
5152 Posts |
Posted - 12/11/2012 : 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
693 Posts |
Posted - 12/11/2012 : 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
876 Posts |
Posted - 12/11/2012 : 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
693 Posts |
Posted - 12/12/2012 : 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... |
Edited by - Hommer on 12/12/2012 13:28:06 |
|
|
|
sunitabeck
Flowing Fount of Yak Knowledge
5152 Posts |
Posted - 12/12/2012 : 14:22:50
|
| Have you solved your problem, or are you still looking for a solution? |
|
|
|
Hommer
Aged Yak Warrior
693 Posts |
Posted - 12/12/2012 : 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."
|
|
|
| |
Topic |
|
Select rows from mutiple tables w/ known col name
