ok, i have a large Db, and no documentation or diagrams. how do i figure out what each column is for? is there any way i can track where data is being stored in the DB? i have to find out what tables are linked , but have no diagrams. any ideas?. if i click on create new diagram and import all teh tables, it doesnt show any relationships
Tek-Tips is the largest IT community on the Internet today!
Members share and learn making Tek-Tips Forums the best source of peer-reviewed technical information on the Internet!
-
Congratulations Wanet Telecoms Ltd on being selected by the Tek-Tips community for having the most helpful posts in the forums last week. Way to Go!
figuring out a large db i didnt create, with no docs
- Thread starter fguihen
- Start date
This looks like it will not be an easy thing. What is your user interface written in? Access has a documenter that might be of some help.
I wish I knew of a tool that would search your interface for all the references to the database (queries or stored procedures) and documents what is being used where.
The first step for me in dealing with a mess like this would be to go step by step through the user interface. Look at every form and report and code module. Find out what sps or queries each runs and then what tables and columns in those tables are referenced. Document as you go in a Word or Excel document.
By the time you finish this, you should have a working knowledge of what the relationships are, etc. If you find relationships through your search through what actually runs the user interface, I would go ahead and create them and create the diagrams to go with them. You may also find relationships by looking at the field names and then examining the data.
You should also have a list of tables,columns, sps not referenced anywhere. You will need to look at each of these closely to see if they are needed. Some may be used in DTS packages, some may be used in triggers, some may be used for admin or audit purposes. You need to find out what they are used for, if anything.
I would also script the database and read through it. SQL Server lists dependencies, but I wouldn't rely soley onthis as they can get broken if an object is dropped and recreated. But they are a starting point.
More than likely if there are no set relationships you will have data integrity problems and possibly problems with speed due to lack of indexes. Anyone who creates a database without relationships more than likely has such a poor understanding of database design that there will be many problems in it. Another problem you are likely to face is bad choices for the data types. People who do this type of programming tend to use text for everything including dates and then let their users input anything they want with no checks into the fields. You may also find tables that need normalization since the orginal designer clearly didn't have a grasp of what relationships are.
Create the documentation as you go, one form, report, table at a time. You might give some thought to what documentation you want to have. What I found most useful in one job where I had to do this for an Access database was I created a diagram (I had no fancy tools so I did this in text in Excel) that worked from tables to the user interface. So I could look at a table and see what queries it ran and what forms and reports referenced it. Was most helpful both in understanding what was going on and in knkowing what affect a later change would have.
I wish I knew of a tool that would search your interface for all the references to the database (queries or stored procedures) and documents what is being used where.
The first step for me in dealing with a mess like this would be to go step by step through the user interface. Look at every form and report and code module. Find out what sps or queries each runs and then what tables and columns in those tables are referenced. Document as you go in a Word or Excel document.
By the time you finish this, you should have a working knowledge of what the relationships are, etc. If you find relationships through your search through what actually runs the user interface, I would go ahead and create them and create the diagrams to go with them. You may also find relationships by looking at the field names and then examining the data.
You should also have a list of tables,columns, sps not referenced anywhere. You will need to look at each of these closely to see if they are needed. Some may be used in DTS packages, some may be used in triggers, some may be used for admin or audit purposes. You need to find out what they are used for, if anything.
I would also script the database and read through it. SQL Server lists dependencies, but I wouldn't rely soley onthis as they can get broken if an object is dropped and recreated. But they are a starting point.
More than likely if there are no set relationships you will have data integrity problems and possibly problems with speed due to lack of indexes. Anyone who creates a database without relationships more than likely has such a poor understanding of database design that there will be many problems in it. Another problem you are likely to face is bad choices for the data types. People who do this type of programming tend to use text for everything including dates and then let their users input anything they want with no checks into the fields. You may also find tables that need normalization since the orginal designer clearly didn't have a grasp of what relationships are.
Create the documentation as you go, one form, report, table at a time. You might give some thought to what documentation you want to have. What I found most useful in one job where I had to do this for an Access database was I created a diagram (I had no fancy tools so I did this in text in Excel) that worked from tables to the user interface. So I could look at a table and see what queries it ran and what forms and reports referenced it. Was most helpful both in understanding what was going on and in knkowing what affect a later change would have.
Hi,
I would suggest to get a ER Tool and get the ER map and documentation from your database.
ER Studio from Embarcadero Technologies is a great tool, You can downloaded for testing (I think 10 days test period).
You can then collect tables, views, SPs, PKs, FKs and relationships.
That's a start.
Regards
PD. This is not a commercial, that is the tool I know that offers a trial period, you perhaps migth want to check ERWin to see if the have a similar offer.
I would suggest to get a ER Tool and get the ER map and documentation from your database.
ER Studio from Embarcadero Technologies is a great tool, You can downloaded for testing (I think 10 days test period).
You can then collect tables, views, SPs, PKs, FKs and relationships.
That's a start.
Regards
PD. This is not a commercial, that is the tool I know that offers a trial period, you perhaps migth want to check ERWin to see if the have a similar offer.
Visio for Architects does a decent job of reverse-engineering a DB.
Phil Hegedusich
Senior Web Developer
IIMAK
-----------
Boy howdy, my Liberal Studies degree really prepared me for this....
Phil Hegedusich
Senior Web Developer
IIMAK
-----------
Boy howdy, my Liberal Studies degree really prepared me for this....
- Status
Similar threads
- Locked
- Question
- Locked
- Question