August 26, 2009 at 6:45 pm
Excuse my ignorance on this one...but..
I have been assigned a reporting task. The task is basically to add several fields to the report procedure (and ultimatley the report) on a huge DB with wide tables.
Looking at the existing report proc they do allot of inner joins.
I know the fields needed and have found them in the tables using a small query I found online.
How do I find what to join them on? There doesn't appear to be a 'matching' column in any other table.
August 26, 2009 at 8:54 pm
Typically, you will need to identify the Primary/Unique and Foreign Key columns of the Tables you wish to join.
You can look it up in the Entity Relationship Diagram(if you have one) or simply expand the Tables Keys nodes in SSMS which lists all Primary and
Foreign Keys defined on a Table.
August 26, 2009 at 8:55 pm
As you indicate that you already realize, you need to know on what basis the various tables are related. Sometimes the columns by which tables are related may be given completely different names. For example, a user profile table may have a key named "UserID". The table that logs each time a user logs in may have a column "LogInID". Even though the names are different, they'll have the data to match up the log records with the user profiles.
Have you looked at the definitions for the tables involved? The database, you may hope, could have foreign keys defined to help maintain referential integrity. Those relationships may be all you need. If that doesn't do it, you'll want to track down documentation for the db that explains what each table is and, more relevantly to your question, how they are related.
In any case, you want to look for how your new tables are related to any existing tables in that report.
August 27, 2009 at 7:04 am
Sometimes it's set up in a way that you really wouldn't be able to decipher on your own. If there's no database diagrams or comments anywhere, it can get pretty tough. The database system we have was designed quite a while ago, and some things that connect together, would never be known if it weren't explained by someone who already knew. For example, we have a document table with a LinkID column, and a LinkType column. Depending on the value of the LinkType column, the LinkID joins to different tables. If I hadn't had another DBA who knew how that worked, there's no way I'd have been able to decipher it.
I'd suggest you talk to one of the other DB people at your company, and have them give you a quick overview of the database you're dealing with, and any quirks that might exist regarding relationships between tables. It would probably save you a lot of time.
August 27, 2009 at 7:02 pm
Thanks everyone, very informative and helpful.
Good karma to you all!
August 28, 2009 at 8:27 am
Not sure if this addresses part of the issue for you, but if you just need a quick way of finding tables that have a particular column name that you are looking for, you can use this (I got this from somebody that had posted it on here and I use it a lot):
SELECT t.name AS table_name, SCHEMA_NAME(schema_id) AS schema_name, c.name AS column_name FROM sys.tables AS t INNER JOIN sys.columns c ON t.OBJECT_ID = c.OBJECT_ID WHERE c.name LIKE
'%[NameOfColumn]%' --change to column name looking for
ORDER BY schema_name, table_name
Viewing 6 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply