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.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" <ckspot-temp@.yahoo.comwrote in message
news:1191263723.914683.228640@.r29g2000hsg.googlegr oups.com...

Quote:

Originally Posted by

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.
>

|||>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.

Find object owner

Is there a query I can write that joins 2 system tables to return the owners of objects (e.g. tables) and the object's name?

Thanks,

Dave

IN SQL SERVER 200 the SCHEMA IN the information_VIEW represents the owner OF an objects e.g. FOR tables

SELECT TABLE_SCHEMA FROM [INFORMATION_SCHEMA].TABLES

IN SQL SERVER 2005, you will have TO determine the owner OF the SCHEMA instead using

SELECT Name FROM sys.Schemas

INNER JOIN sys.server_principals

ON [Schemas].principal_id = [server_principals].Principal_id

HTH, Jens K. Suessmeyer.

http://www.sqlserver2005.de

|||

In SQL SERVER 2005, because of the ALTER AUTHORIZATION clause, the schema owner may not always be the object owner

See, http://msdn2.microsoft.com/en-us/library/ms187359.aspx

So, an alternative to the above query for SQL SERVER 2005 is

select user_name(objectproperty(object_id,'OwnerId')), name from sys.objects

This always returns the correct owner of the object and takes 'ALTER AUTHORIZATION' into account.