Data dictionary creation software

DBMaster provides an easy way to search and build documentation to data structures for all team members. It also provides built-in change synchronization. It stores all the information in Extended Properties, so it's easier to keep the documentation in sync with the database as it changes.

It provides column level detail on SQL Server primary keys, data types, and defaults along with descriptions that enable you to easily find missing indexes and incorrect column definitions.

Key functionality of Data Dictionary tools is to give users the ability to document data. Moreover, very important is the possibility to create a collection of multiple repositories, based on different system engines.

For a better understanding of the data, some tools allow visualization of the data structure using ERD Entity-Relationship Diagrams. From the organization's point of view, a community module within a data dictionary tool proves to be useful.

It facilitates the proper information flow, as well as provides sharing opinions on specific objects among the members of an organization. Nowadays, data discovery and understanding becomes crucial for proper organization performance. ER Diagram: All. Export: CHM. MS Excel. MS Word. Online HTML. Plain text. Commercial: Commercial Free. Free edition: All. Runs on: for desktop : Linux. Mac OS.

Similarly, having no documentation is the worst-case scenario, and any organized data dictionary would be beneficial when compared to having nothing. An additional note on lock-in: If a homegrown data dictionary outgrows its environment, then it is always possible to migrate data into another solution homegrown or 3 rd party.

All database systems include a variety of internal tables and views that allow developers to view object names, data types, and other built-in properties. These views are exceptionally useful for operations and the automation of administrative functions such as index maintenance, backups, ETL, etc. The following query retrieves all columns and some related metadata within a database using system views.

You can download all code for this article here. These results are easy for an administrator or developer to consume but have limited use beyond that scope. In addition, there is no human-readable documentation included. Any data dictionary needs to be easily consumable by people in different roles within an organization. Extended properties are a convenient, built-in way to add descriptions to objects. Their documentation ability is limited, though, as you are restricted to a name and value.

Using them in practice is similar to managing system views and requires database access or a report that accesses the objects for you. For very simple and limited details, extended properties provide an easy and effective solution.

For anything more extensive, though, they are inadequate and do not constitute the level of depth required of documentation for most organizations. Building a customized data dictionary is labor-intensive, but the most time-consuming steps are not designing, architecting, and implementing code but collecting details needed to realize business meaning within this metadata. The first step in this process is to build a table to store data. For this work, the result will be simplified to a single table and not normalized.

The reasons for this are to keep the schema simple and easy to consume and to acknowledge that the table will not be large, and therefore complex dimensions are unlikely to be valuable. What goes into this table? Anything really. If what is needed is not here, add it. If some columns are unnecessary, then remove them:. It can be used to identify a row. This detail may or not be necessary, depending on the data that is being documented.

This is something generic to allow a user to understand the over-arching reason that this data exists. This will typically be the same for many tables or an entire database that share a common use-case. This will usually be the same for all columns in the table. If a database is relational, then this column can be populated from sys. If the database is not relational, this will involve some manual or semi-autonomous work to maintain.

This value can be collected quickly and easily from sys. A column for the data retention policy could similarly replace this and provide functionality for columns that undergo regular cleanup or archiving. Essentially, these columns or something similar provide detail on when data began to be populated and when or if it ends. For a table where columns are added over time but not backfilled, this can be exceptionally useful.

Additional information could be provided as well, such as the time of deletion and reason. Once a table is defined, metadata can be inserted. In its most basic usage, data can be inserted manually.

This effort would make the table about as efficient as a fully-manual wiki, document, or version-controlled source-code system. For testing, populating data like this is adequate, but long-term, automation is needed.

Effective, but not terribly exciting. Doing this repeatedly for a large database would be torturously slow and error prone. The next step towards making this documentation faster and more reliable is automation.

This effort aims to automate as much as possible but not to create a process that is so complex that it requires immense effort to maintain. The primary downside of any do-it-yourself approach is that whatever is created needs to be maintained potentially forever. Therefore, a target of a few hundred lines of T-SQL would be simple enough that maintaining the code, even by someone unfamiliar with it, would not be a complex task.

First, from the column list above, what bits of metadata do not need to be manually entered? These would include any that originate from system views or tables and can be automatically checked into the data dictionary, such as the database server, database name, column name, or row count. Realistically, the only columns that require manual intervention are those intended to add human-readable detail, such as original data source, data purpose, and notes. Even the data element name can be automated if columns follow a relatively reliable naming convention.

Therefore, automation aims to remove as much work as possible and place what work is necessary on a silver platter for review. Everyone has slightly different conventions they follow with regards to creating object or element names. There is value in pre-populating a data element name, even if there is a chance it will not be descriptive enough for permanent use. This can reduce future work, especially in databases that already follow naming conventions that are consistent and descriptive.

To accomplish this, a function can be created to take the column name and perform some string manipulation to turn it into a more friendly name. The resulting data element name may not always be the final or best name, but if it is accurate often enough, then it will be a time-saver for anyone entering or maintaining data dictionary data.

The following is a simple function that can accomplish this task:. When an input string is passed into the function, underscores are replaced with spaces, each word is capitalized, and some common abbreviations are capitalized. These are a handful of examples of how text can be formatted to meet some of the naming needs that may arise.

Feel free to customize and extend this to whatever other needs may arise! Note that since this function contains iteration, it should be used on small sets of data or metadata where row-by-row calculations of this nature are acceptable. A data dictionary where data elements are named once and never again is a perfect use-case. The results show how the column names were formatted to follow a similar naming convention that can form the first step in naming data elements:.

Ultimately, this is a process that is destined to be highly customizable. In some cases, it may be unnecessary. Either way, a scalar function is an easy way to encapsulate the logic needed to format names for use in a data dictionary.

With some goals and guidelines in mind, the process of building a stored procedure to populate and maintain a data dictionary can begin. Ideally, this process would have more value if it could poll a set of databases on a server, removing the need to execute it repeatedly for multiple databases. It would also remove the need to modify the calling code whenever a new database is added.

The table type allows a set of databases to be passed into a stored procedure, which is programmatically easier to work with than a comma-separated list or some other text field. Whenever the data dictionary load process runs, a set of database names can be passed in that will be used to filter results to only target those databases. For example, when typing an object in the search field the user interface will highlight any matching text. You can download the free trial. See also: Database Diagram Tools.

To create documentation, you can add a description of database objects , tables , and columns to the data dictionary. Data model visualization makes it easier to understand how your database works with ER diagrams. You can create ER diagrams by dragging and dropping tables from your database platform into the ERD section. There is also the ability to create cross-database and cross-platform databases to unify data management.

You can interact with the tool through a wizard and a command-line interface, which helps to speed up the documentation creation process. There are also customizable templates you can use to build professional documents to share with other members of your team. You can even save custom profiles to use for future documents. These features give you a high degree of control over how your documentation is presented. When it comes to managing tables, DBSribe has a description editor. The description editor allows you to manage elements such as tables , columns , indexes , triggers , keys , and more from one location.

DBScribe 1. There is also a day free trial. The tool also automatically scans database metadata to update documents without any manual interaction from the user.

To aid with database management and regulatory compliance there is a data lineage analysis feature. This feature allows you to view how changes to a single object will affect the rest of the database.

You can create dictionaries with annotations to help monitor data changes. The Enterprise Licensing package is customized to the needs of your company. You can download the two-week free trial. Innovasys Document! You can use the tool to document database tables, columns, views, relationships, indexes, dependencies, triggers, roles, users, and more.

There is a customizable HTML-based template that you can use to choose how your documentation is organized. For added convenience, you can add hyperlinks to external websites to help with element descriptions.

Interacting with data dictionary in Innovasys Document! X is extremely simple. To make things easier to read and navigate the source code is colorized. Alternatively, you can use the Content File Editor to create and manage documents if you prefer. X is available as a single product or as a package with HelpStudio X and Help Studio Bundle Database Note Taker is a database documentation tool that simplifies database management for a range of databases including Microsoft SQL Server.

You can create a new project in the program and select the type of database you want to import schema data from.