Finding common joins

At the risk of asking a stupid question -
Is anyone familiar with either a query against the systables or maybe
an outside tool that will provide a list of the most common joins that
have been made in user created views? I'm not talking about table
relationships that are established at the database level, but rather,
I'm referring to the ability to find which joins have been utilized in
poorly constructed databases where no relationships were established
in the first place.
Thanks in advance -
Cindy T.

Re: Finding common joins

Do you mean listing all poor running queries? You could list all in Profiler

--

Jack Vamvas
___________________________________
Search IT jobs from multiple sources- http://www.ITjobfeed.com/SQL




"Cindy" wrote in message
news:1191263723.914683.228640@r29g2000hsg.googlegroups.com.. .
> At the risk of asking a stupid question -
> Is anyone familiar with either a query against the systables or maybe
> an outside tool that will provide a list of the most common joins that
> have been made in user created views? I'm not talking about table
> relationships that are established at the database level, but rather,
> I'm referring to the ability to find which joins have been utilized in
> poorly constructed databases where no relationships were established
> in the first place.
> Thanks in advance -
> Cindy T.
>

Re: Finding common joins

>> I'm referring to the ability to find which joins have been utilized in poorly constructed databases where no relationships were established in the first place. <<

I am not sure that this would even be possible with an AI tool. You
would have to find VIEWs that restore split attributes, are used to
fix denormalized tables, etc.

Re: Finding common joins

Thanks Jack and Celko -
I've done quite a bit of searching - find lots of tools for query
optimization and database documentation, but nothing along the lines
of "what joins tend to be used in this database" (thinking views more
than anything else). I can understand why it's not out there - the
many possible combinations of syntax, use of aliases, subqueries, etc.
- it would be a task, that's for sure.

I'm a contractor who is often asked to do reporting on databases that
are not only undocumented but also that lack table relationships -
where a great deal of time is spent trying to understand the
relationships between the tables that have been used to date anyway in
existing views.

Maybe I just need to explore query optimization tools more, including
the profiler - because in order for them to analyze what's being done,
joins are identified - my goal is trying to pull out that information
in a quick to access format that covers the entire database rather
than than just focusing on a single query plan.

If we had the ability to generate a database table that lists joins
that were used - imagine a tool where you plug in the names of 4
tables, for example, and then you get a report where you see all the
fields, join types, and join type operators that were used between
those 4 tables - along with the frequency of use. It seems it would
go a long way toward getting a database in better shape to document
common relationships.

Does that sound like a pipe dream?



On Oct 2, 9:45 pm, --CELKO-- wrote:
> >> I'm referring to the ability to find which joins have been utilized in poorly constructed databases where no relationships were established in the first place. <<
>
> I am not sure that this would even be possible with an AI tool. You
> would have to find VIEWs that restore split attributes, are used to
> fix denormalized tables, etc.

Re: Finding common joins

Cindy wrote:

> If we had the ability to generate a database table that lists joins
> that were used - imagine a tool where you plug in the names of 4
> tables, for example, and then you get a report where you see all the
> fields, join types, and join type operators that were used between
> those 4 tables - along with the frequency of use. It seems it would
> go a long way toward getting a database in better shape to document
> common relationships.

You might want to run a Profiler trace for a while, send output to a
new table, then query the table for accesses to a table that you're
interested in. You can also look at sysobjects and syscomments to
determine which views access a given table (especially if the Profiler
output cites the view rather than the underlying tables; I've mostly
used Profiler to deal with stored procedures accessing tables directly,
so I don't know the answer to that one without testing it).

Re: Finding common joins

On Oct 3, 1:52 pm, Ed Murphy wrote:
> Cindy wrote:
> > If we had the ability to generate a database table that lists joins
> > that were used - imagine a tool where you plug in the names of 4
> > tables, for example, and then you get a report where you see all the
> > fields, join types, and join type operators that were used between
> > those 4 tables - along with the frequency of use. It seems it would
> > go a long way toward getting a database in better shape to document
> > common relationships.
>
> You might want to run a Profiler trace for a while, send output to a
> new table, then query the table for accesses to a table that you're
> interested in. You can also look at sysobjects and syscomments to
> determine which views access a given table (especially if the Profiler
> output cites the view rather than the underlying tables; I've mostly
> used Profiler to deal with stored procedures accessing tables directly,
> so I don't know the answer to that one without testing it).

Good idea - thanks all!

Re: Finding common joins

In "Microsoft SQL Server 2005 Waits and Queues" document you can find some
queries to find what index would be useful. It might not be exacly what you
seak but it will givie you other hints also.

MarcM

"Jack Vamvas" wrote in message
news:N-qdnUSxU7DqvJ_anZ2dnUVZ8vSdnZ2d@bt.com...
> Do you mean listing all poor running queries? You could list all in
> Profiler
>
> --
>
> Jack Vamvas
> ___________________________________
> Search IT jobs from multiple sources- http://www.ITjobfeed.com/SQL
>
>
>
>
> "Cindy" wrote in message
> news:1191263723.914683.228640@r29g2000hsg.googlegroups.com.. .
>> At the risk of asking a stupid question -
>> Is anyone familiar with either a query against the systables or maybe
>> an outside tool that will provide a list of the most common joins that
>> have been made in user created views? I'm not talking about table
>> relationships that are established at the database level, but rather,
>> I'm referring to the ability to find which joins have been utilized in
>> poorly constructed databases where no relationships were established
>> in the first place.
>> Thanks in advance -
>> Cindy T.
>>
>
>