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 rows from mutiple tables w/ known col name
 New Topic  Reply to Topic
 Printer Friendly
Author Previous Topic Topic Next Topic  

Hommer
Aged Yak Warrior

786 Posts

Posted - 12/11/2012 :  11:37:16  Show Profile  Reply with Quote
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

5155 Posts

Posted - 12/11/2012 :  11:50:17  Show Profile  Reply with Quote
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.
Go to Top of Page

Hommer
Aged Yak Warrior

786 Posts

Posted - 12/11/2012 :  13:15:04  Show Profile  Reply with Quote
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.
Go to Top of Page

sunitabeck
Flowing Fount of Yak Knowledge

5155 Posts

Posted - 12/11/2012 :  13:57:14  Show Profile  Reply with Quote
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.
Go to Top of Page

Hommer
Aged Yak Warrior

786 Posts

Posted - 12/11/2012 :  15:08:06  Show Profile  Reply with Quote
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...
Go to Top of Page

LoztInSpace
Aged Yak Warrior

940 Posts

Posted - 12/11/2012 :  20:07:53  Show Profile  Reply with Quote
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.
Go to Top of Page

Hommer
Aged Yak Warrior

786 Posts

Posted - 12/12/2012 :  13:26:56  Show Profile  Reply with Quote
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
Go to Top of Page

sunitabeck
Flowing Fount of Yak Knowledge

5155 Posts

Posted - 12/12/2012 :  14:22:50  Show Profile  Reply with Quote
Have you solved your problem, or are you still looking for a solution?
Go to Top of Page

Hommer
Aged Yak Warrior

786 Posts

Posted - 12/12/2012 :  14:59:08  Show Profile  Reply with Quote
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."


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.06 seconds. Powered By: Snitz Forums 2000