My client has an old proprietary medical charting application whose data is stored in SQL server 2000 tables. This application is so old it will only run on a Windows 2000 operating system.
The application is called MD Dictate . My task is to export data from this ancient application. The data is to be imported into a much more modern charting application. The application has no reporting procedures. No way to print out data except for one patient at a time.
I am reasonably familiar with Enterprise Manager. I am NOT an expert. When I invoke Enterprise Manager, I can see the databases and tables for MD Dictate . It seems all the data I need is in one database.
The challenge: I have to export data from various tables and write the data to a csv file. There is no documentation on what tables contain what and how the keys are configured. The name, address, phone, etc are in table 1 , chart number is in table 2, other information are in other tables. Most likely there are associative tables. So, somehow I have to determine the keys to each table so I can construct Tsql statements to successfully export the desired data. My thought was to create a denormalzed table whose fields would be the same as the csv file, then loop through each patient, obtain all the relevant data for that patient, and write it to the denormalized table.
MySQL workbench has a tool which supposedly will reverse engineer a database and, I guess, will display the relationships between the tables. May be there is some similar tool for SQL server?
What inexpensive tool might help me reverse engineer a SQL server database ?
Perhaps my method is not the best. What ideas would you suggest?