Taking over a database you’re not familiar with requires you to dedicate some time to analyze and understand it. Deciphering an inherited database takes some time, that among other factors, depends on the available documentation (if any), comments left in object SQL, and database complexity.
What is a legacy database?
One of the explanations for the “legacy database” term says that it’s called legacy because the person who created it is no longer in charge of it and there’s not a single person who completely understands the database structure and how it works.
If your colleague who was developing a database left the company, the outsourced company is no longer maintaining the database, or you just came into a company and you got a database to maintain and administer – you’re left with a legacy database. Without any or proper documentation, and no former developers to talk to – a lot of hard work is ahead of you.
What you need to know about the database
First – look for any kind of SQL Server documentation. If there’s not any – find out which applications use the database and what kind of data is stored in it.
Go through the database objects, type by type. Start with tables and try to figure out which table stores which data, check out the table structure, find the largest tables.
Find the most often used routines, see the DDL for the top stored procedures, and understand what they do.
Find out the dependencies and parent-children relations in the database. The easiest way to understand the database structure is to create a database diagram.
Check if any jobs are created and scheduled.
Be prepared that any unusual table structures, inconsistent field specifications, improper or inefficient structures, and bad relationship definitions can make the database more difficult to understand.
Then, you can dive into a detailed analysis – object by object, line by line.
How to get to know a database
The first option is SQL Server Management Studio
- Select the database in the Object Explorer
-
Expand the Tables node to see the list of tables, and then columns, keys, and constraints in each table. Expand the nodes for other object types, as deep as possible.
-
Right–click a table and select View Dependencies to see the objects that the table depends on and which depend on the table.
- To get the visual presentation of the database, right-click Database Diagrams and select New Database Diagram.
-
Add the tables for which you want to see the relations and a diagram will be shown.
DDL scripts for objects, object properties, and SQL jobs can also be obtained via the Object Explorer context menu.
The downside of this method is that you cannot get overall documentation for the whole database, you have to select objects one by one, and find out piece by piece. The database diagram has to be created manually and it only shows tables – no other object types, such as triggers, stored procedures, or views.
Another option is to use a SQL Server documentation tool, like ApexSQL Doc. With ApexSQL Doc, you can document SQL Server instances, databases, objects, SSAS cubes, SSRS reports and SSIS packages. It generates comprehensive documentation in multiple searchable and printable formats including CHM, HTML, PDF and Microsoft Word’s DOC and DOCX, and can automate and schedule the documentation process.
-
Start ApexSQL Doc
-
Click New to create a new project
-
Click Add database to add the database that is to be documented
-
Select the SQL Server instance and provide db_owner credentials
- Click OK
-
In the database list on the right, select the database that is to be documented.
-
To get the diagram with graphical presentation of database objects and their relationships, in the Database options tab, select the Graphical dependencies option.
-
In the File format tab, select the output type – chm, html, pdf, Microsoft Word’s doc or docx.
-
Click Generate in the Home tab.
-
Specify the path and file name and click Save.
When the documentation is created, the following notification appears:
A complete documentation, including the T-SQL DDL for database objects, a detailed graphical dependences presentation, SQL Server jobs and other SQL Server info will be generated.
Getting to know a legacy database can be easier and faster with a complete and detailed documentation, which covers all database and SQL Server object information, relations, and permissions. Create it and bundle it all into a single browsable document using this SQL Server documentation tool – ApexSQL Doc
April 18, 2013
