May 23, 2012 at 2:41 pm
Hey all,
I received a database containing user data from a firm, and my job is to locate and extract certain parts of that data. I have essentially no prior knowledge of the structure of the database. I have looked at the different numbers of rows in each table and have identified some tables of interest. One thing I'm having trouble with is figuring out where certain fields that are key fields come from. That is, if there is a customer type key field in the Customer table, I want to know what table contains that key as its primary key and contains descriptive information on the different types.
What is the best way to do this? I've tried making a database diagram and adding related tables. However, one thing I don't like about this is the lines between the tables aren't drawn to the fields they are linked to. The lines themselves may say the foreign key constraint in a tool tip, but they don't accurately diagram the relationships.
By contrast, if I add 2 tables to a view that I know have a relationship, the diagram in the view draws lines from the primary key of one table to the field in the other table that references that key. The issue is that you have to know that the tables are related already as far as I know. Is there a fast way to figure out these tables relationships? I would appreciate any advice anyone can give. Thanks.
May 23, 2012 at 2:55 pm
something like this help?
SELECT f.name AS ForeignKey, OBJECT_NAME(f.parent_object_id) AS TableName,
COL_NAME(fc.parent_object_id, fc.parent_column_id) AS ColumnName,
OBJECT_NAME (f.referenced_object_id) AS ReferenceTableName,
COL_NAME(fc.referenced_object_id, fc.referenced_column_id) AS ReferenceColumnName
FROM sys.foreign_keys AS f
INNER JOIN sys.foreign_key_columns AS fc
ON f.OBJECT_ID = fc.constraint_object_id
_______________________________________________________________
Need help? Help us help you.
Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.
Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.
Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/
May 23, 2012 at 3:24 pm
Sean is assuming that these constraints are defined as such. If they are not, it just takes a lot of testing... One of my favorite things to do with an unknown database is reverse engineer it!
Jared
CE - Microsoft
May 23, 2012 at 3:41 pm
Sean, thanks for the query. It is helpful, but one thing I'm having trouble with is filtering by TableName.
When I try to include a where clause like:
where TableName = 'TableName1'
I get the error "Invalid column name 'tablename'.
It would be helpful if I could see the results for a single table at a time.
Jared, it does seem that some of the relationships aren't formally implemented. Do you have any tips for reverse engineering databases?
May 23, 2012 at 4:12 pm
Have you tried the Visio Database Reverse Engineer feature? It creates a diagram and tries to show relationships- probably not all correct in your case, but it's a start.
Do you have the GUI of the application for which it was created? Browsing the application while running a SQL trace will help you put the lines in the right places.
May 23, 2012 at 4:28 pm
Personally, I will usually fire up a trace against the database and goof off with a particular component of the environment. From there, I'll see what procs are called or batch sql is sent.
From that, I start building my own diagram. I rarely use RI in my systems and my procs are usually where most of the information you'd need are. I almost always will start there.
Never stop learning, even if it hurts. Ego bruises are practically mandatory as you learn unless you've never risked enough to make a mistake.
For better assistance in answering your questions[/url] | Forum Netiquette
For index/tuning help, follow these directions.[/url] |Tally Tables[/url]
Twitter: @AnyWayDBA
May 23, 2012 at 5:01 pm
I hear good things about the free Schema Surf product from Atlantis Interactive:
http://www.atlantis-interactive.co.uk/products/schemasurf/default.aspx
Not used it myself, but it was recommended by Mladen Prajdic, which is good enough for me.
Paul White
SQLPerformance.com
SQLkiwi blog
@SQL_Kiwi
May 23, 2012 at 5:08 pm
Personally, I have never used any tools. I look at it like a big puzzle. Of course, I first look for table names and columns that are named the same or similar. I also look for stored procs that may indicate relationships. My favorite is stuff like usp_add_new product or add_new product. That's a good one to help you identify which tables need records added to create a "product." Sometimes, you just have to play and test. Example: Microsoft Great Plains had tables GL10000 and PA10000. Once I figured out the naming conventions they used, it was easier to reverse engineer the database. So in that case, it was a decryption of terminology first... Then the reverse engineering.
Jared
CE - Microsoft
May 23, 2012 at 5:30 pm
SQLKnowItAll (5/23/2012)
Personally, I have never used any tools.
I just downloaded Schema Surf to try it on AdventureWorks. I have to say it's very impressive, and much more than just key relationships. Try it one day if you get a spare minute.
Paul White
SQLPerformance.com
SQLkiwi blog
@SQL_Kiwi
May 23, 2012 at 5:37 pm
SQL Kiwi (5/23/2012)
SQLKnowItAll (5/23/2012)
Personally, I have never used any tools.I just downloaded Schema Surf to try it on AdventureWorks. I have to say it's very impressive, and much more than just key relationships:w00t:. Try it one day if you get a spare minute.
I may do that tomorrow 🙂 it was fun when I only had 3 databases. My current employer has about 100 and I've only been involved in the development of 1:hehe: Thanks Paul!
Jared
CE - Microsoft
May 23, 2012 at 11:46 pm
SQLKnowItAll (5/23/2012)
Sean is assuming that these constraints are defined as such. If they are not, it just takes a lot of testing... One of my favorite things to do with an unknown database is reverse engineer it!
Heh... one of my favorite things to do is to hunt down the person who left out the DRI and find out if they can escape some pretty tight ropes in the presence of high velocity porkchops. 😉
--Jeff Moden
Change is inevitable... Change for the better is not.
May 24, 2012 at 5:51 am
Jeff Moden (5/23/2012)
SQLKnowItAll (5/23/2012)
Sean is assuming that these constraints are defined as such. If they are not, it just takes a lot of testing... One of my favorite things to do with an unknown database is reverse engineer it!Heh... one of my favorite things to do is to hunt down the person who left out the DRI and find out if they can escape some pretty tight ropes in the presence of high velocity porkchops. 😉
Now don't you think pork chops is a bit much? At least lower the velocity... 😉
Jared
CE - Microsoft
May 24, 2012 at 7:41 am
SQLKnowItAll (5/23/2012)
Sean is assuming that these constraints are defined as such. If they are not, it just takes a lot of testing... One of my favorite things to do with an unknown database is reverse engineer it!
That is true, however since the OP mentioned the lines in a DB diagram I made the assumption they were trying to discover the actual foreign keys not logical ones. 😉
_______________________________________________________________
Need help? Help us help you.
Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.
Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.
Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/
May 24, 2012 at 7:55 am
Sean Lange (5/24/2012)
SQLKnowItAll (5/23/2012)
Sean is assuming that these constraints are defined as such. If they are not, it just takes a lot of testing... One of my favorite things to do with an unknown database is reverse engineer it!That is true, however since the OP mentioned the lines in a DB diagram I made the assumption they were trying to discover the actual foreign keys not logical ones. 😉
I just wanted to add to it, as I'm sure you have noticed that in reality there are almost always a number of relathionships that end up not getting defined that should be. You know, a nice mixture of good and bad. 😎
Jared
CE - Microsoft
May 24, 2012 at 8:06 am
SQLKnowItAll (5/24/2012)
Sean Lange (5/24/2012)
SQLKnowItAll (5/23/2012)
Sean is assuming that these constraints are defined as such. If they are not, it just takes a lot of testing... One of my favorite things to do with an unknown database is reverse engineer it!That is true, however since the OP mentioned the lines in a DB diagram I made the assumption they were trying to discover the actual foreign keys not logical ones. 😉
I just wanted to add to it, as I'm sure you have noticed that in reality there are almost always a number of relathionships that end up not getting defined that should be. You know, a nice mixture of good and bad. 😎
I wish I could say that in reality it is a nice mixture. In my experience it is mostly bad with the occasional splattering of decent and a hint of good every once in awhile. If Jeff had worked with me at most of the places I have been, pigs would be extinct. 😛
_______________________________________________________________
Need help? Help us help you.
Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.
Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.
Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/
Viewing 15 posts - 1 through 15 (of 16 total)
You must be logged in to reply to this topic. Login to reply