As SQL Server professionals we all rely on up-to-date documentation from clients as a way for us to get to know their environments quicker. More often than not the documentation is either outdated or they don't have any, which makes our task a bit more difficult as we now have to go through their servers and databases individually which adds days to the engagements.
Recently I faced this situation again but I was prepared. I have recently started using GenesisOne Solutions T-SQL Source Code Unscrambler to help me understand and document client environments within hours instead of weeks.
Why did I decide to use GenesisOne?
My client is in the financial services industry. They had a tight deadline and a very limited budget. After showing my client the results they handed me more work!
The database, while not big, is used by their main application.
- 957 Tables
- 1635 Stored Procedures
- 43 views
- 83 Functions
And for good measure add one week to learn the environment and make recommendations on how to improve database performance… and did I mention there was no documentation?
Once the Unscrambler is installed, the process of generating documentation is simple and automated:
Enter your product key and click on activate.
Now enter your server name(s) then click add server. You will notice that it supports both SQL and windows authentication. You will notice on the right side pane you are able to remove servers you no longer wish to document.
In the left pane you can view the database objects and on the right you are able to see a graphical view of your table and its dependencies.
Next expand the view tree and select a view. As you can see there are four tabs each giving more insight into your view(s). First is the data flow chart; it gives you a visual flow of your view as well as which objects make up the view.
Next is the property tab showing you everything in a tabular form.
I like the next tab: the summary tab tells in English the story of your object's code! Awesome! This saves a hell of a lot time having to explain to people what the code does.
The next tab is the dependency viewer in a tabular form. It tells what makes up your object, in my case I chose a view and as you can see it tells me which schema.table.collumn makes up my view.
Last but not least is the dependency diagram which gives you a high level view of your object. As you can see, each colour represents a different object.
On the top right hand side of the pane you have the option to export your object to either PNG, PDF or SVG formats or, if you do not want to export objects individually, you can document your entire database within seconds into PDF format. To do this simply right click on the database and click “Generate Report” as shown below.
Now select a location to save the PDF and click save. At the bottom of the left pane you will see a progress bar indicating how long it will take to complete your document. Once completed (very, very quick) you will get the download completed screen.
After which you can now open the documentation for your entire database.
To help you get out of the block quicker, please watch the demo video below.
I managed to finish within 5 days and make the required recommendations. I have also asked C# MVP Dirk Strauss (T|B) to give GenesisOne a go. His feedback: "As a developer GenesisOne allows me to learn my client(s) environment fast, allowing me to produce results faster."
What I would like to see
- SSAS support
- SSIS support
- SSRS support
- The ability to generate reports for entire instances instead of per database
- The option to save generated files in a custom location
From all the tools that I have used in the past, T-SQL Unscrambler is different as it is not a traditional documentation tool but a visual aid for SQL professionals.