July 6, 2012 at 8:06 pm
So ...
I have some views that consist entirely of tables across a linked server.
My challenge is to now identify which fields are joined between tables.
i.e. if we had the simple SQL below.....
Select
T1.B,T2.B
from
LinkedServer.Db.Schema.T1 as T1
INNER JOIN
LinkedServer.Db.Schema.T2 as T2
ON T1.A = T2.A AND T1.D = T2.E
I need to end up with a table showing....
T1 | A | T2 | A
T1 | D | T2 | E
In reality of course, some of these views are quite complex and involve half a dozen tables, conditioned joins etc ...
Researching this challenge via Google in a nightmare, maybe I'm just not getting the key words/phrases that I need right.
I really don't want to have to resort to parsing syscomments if I can at all help it - I'm just imagining how complex that could get.
Can anyone point me in the right direction ?
Many Thanks.
July 7, 2012 at 12:22 am
Only way to do it is manually.Unless these tables are from same linked server and have some relationship.Even then you might have to check it manually because sometimes joins are not just on FK's they can be on other columns as well.
Go through your views and do it manually.It will be faster then researching on google, at least in this case.
Other solution is maybe to generate the estimated exec plan. Then check on the join operator what condidition are used for join. This should be little bit simpler then searching manually.But sometime optimizer might skip the tables from joinif they have FK relation ship and you wont see them in the plan.
GulliMeel
Finding top n Worst Performing queries[/url]
Improve the performance of Merge Join(special case)
How to Post Performance Problem -Gail Shaw[/url]
July 9, 2012 at 7:35 am
oooh, bad news for me then 🙁
But thanks, at least now I know that I'm not missing some simple solution.
I'm thinking that the DB engine must have this logic 'under the hood' , otherwise it'd never be able to draw diagrams from SQL code. The problem being therefore that there is no exposed methods of accessing this functionality ?
Guess I'll be off to research SQL paring 🙁
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply