Quick and Dirty Data Dictionary

image

Recently, I’ve been working on databases that I’ve never touched before. Of course, when dealing with a “new to you” database there some are challenges. What’s the schema of the database? What’s the “cleanliness” of the data? Is there any useful metadata? When I look at a database for the first time I want to see an entity relationship diagram. It’s a rarity that a well maintained ERD separated into appropriate subject areas is available. Sad but true, I know. The next thing that I need to see is the data dictionary. The data dictionary contains information about the tables, columns, keys and other objects in the database. In other words, it’s darn useful. There are some tools that do a really great job of this such as Redgate’s SQL Doc or Pragmatic Works’ BI Documenter. But I wasn’t in the position to pony up the cash. So what’s a programmer to do? Write his own tool of course.

I had a few criteria in creating this tool:

  1. It had to output a Word doc.
  2. It had to show partition information as well as table/column/key information.
  3. It had to be done quickly. Like in a couple of hours.

I decided that my old friend, Reporting Services, would be perfect for this job. I’ve had some experience using the DMVs so writing these queries wasn’t so tough. Here’s the query that I use to retrieve all of the tables, columns and metadata information in a database.

Then, I cracked open Reporting Services and  threw together some sub-reports for the indexes, foreign keys and the partitions. I didn’t spend much time on the look of the report. I just needed it legible.

When I finished,  I thought that there may be others that may benefit from this report. I couldn’t be the only one that needed a quick data dictionary. So, I put the source on BitBucket for others to use. To download the report go to the SQL Server Data Dictionary project on BitBucket. Then hover over the “get source” menu on the top right and select “zip”. This will start the download of the most current version of the Reporting Services project.

image

You’ve unzipped the project open the DB Schema Report.sln file. Once Visual Studio opens double-click on the DBServer.rds data source.

image

Fill in the connection information to the server. Include the server name, database name, user id and password (if necessary).

image

Then open the DB Schema.rdl file and run it. The report will read the database schema and load the report. Feel free to leave any comments or better yet check-in an update or two! Enjoy.