Quick and Dirty Data Dictionary

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.

SELECT
     DB_NAME() AS [DB NAME]
    ,SysTbls.name AS [Table Name]
    ,SysSch.name AS [Schema Name]
    ,tblexp.ExtPropValue AS [Table Extended Property]
    ,SysCols.name AS [Column Name]
    ,ExtProp.value AS [Extended Property]
    ,SysTyp.name AS [Data Type]
    ,CASE WHEN SysTyp.name IN('nvarchar','nchar')
               THEN (SysCols.max_length / 2)
          WHEN SysTyp.name IN('char', 'varchar')
               THEN SysCols.max_length
          ELSE NULL
          END AS 'Length of Column'
    ,CASE WHEN SysCols.is_nullable = 0
               THEN 'No'
          WHEN SysCols.is_nullable = 1
               THEN 'Yes'
          ELSE NULL
          END AS 'Column is Nullable'
    ,CASE WHEN SysTyp.name IN ('numeric', 'decimal')
               THEN SysCols.precision
          ELSE NULL
          END AS 'Precision'
    ,CASE WHEN SysTyp.name IN ('numeric', 'decimal')
               THEN SysCols.scale
          ELSE NULL
          END AS 'Scale'
    ,SysObj.create_date AS [Table Create Date]
    ,SysObj.modify_date AS [Table Modify Date]
FROM      sys.tables AS SysTbls WITH (NOLOCK)
LEFT JOIN sys.columns AS SysCols  WITH (NOLOCK)
         ON SysTbls.[object_id] = SysCols.[object_id]
LEFT JOIN sys.extended_properties AS ExtProp WITH (NOLOCK)
         ON ExtProp.major_id = SysCols.[object_id]
         AND ExtProp.minor_id = SysCols.column_id
         AND class = 1 --Object or column
LEFT JOIN sys.objects as SysObj WITH (NOLOCK)
         ON SysTbls.[object_id] = SysObj.[object_id]
LEFT JOIN sys.types AS SysTyp WITH (NOLOCK)
         ON SysCols.user_type_id = SysTyp.user_type_id
LEFT JOIN sys.schemas SysSch WITH (NOLOCK)
         ON SysTbls.schema_id = SysSch.schema_id
LEFT JOIN (
    SELECT
         SysTbls.object_id
        ,SysTbls.name AS [TableName]
        ,ExtProp.value AS [ExtPropValue]
    FROM      sys.tables AS SysTbls  WITH (NOLOCK)
    LEFT JOIN sys.extended_properties AS ExtProp WITH (NOLOCK)
             ON ExtProp.major_id = SysTbls.[object_id]
    LEFT JOIN sys.objects as SysObj WITH (NOLOCK)
             ON SysTbls.[object_id] = SysObj.[object_id]
    WHERE class = 1 --Object or column
          and minor_id = 0
      AND SysTbls.name IS NOT NULL
      )
        tblexp  ON SysTbls.object_id = tblexp.object_id

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.

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

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

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.