Database Dictionary Tips

A data dictionary is a collection of descriptions about the data and objects in a data model for the benefit of programmers and users who need to refer to them when building the application, reports, etc. It is ideal to create a data dictionary while working on the data model. When the database is small, developers often use built-in SQL Server tools and scripts to create the data model. However when the data model grows into an enterprise level data model with all of the associated complexities, the management and maintenance grow as well. So what options are available to manage and build the data model? Are there any third party tools available?

Developers and/or Database Administrators (DBAs) are often asked to create a report of different database objects on a database server. I have been evident of situations where people start writing code at the last minute to collect details about different database objects and then the results are sent in the form of Excel spreadsheets. Also such reports are one of the requirements in release notes for any build. In this tip we will discuss how we can create a last minute report, with almost no coding effort for basic details about the database objects limiting the requirement of coding efforts for only advanced level details.

I need a way to create a data dictionary that will stay up to date. I make many changes to the database and I spend more time trying to keep the documentation updated than doing database administration.

Systems could often have volumes of documentation written about them because of their complexity, the extensive changes over time and the many minds that worked on them throughout their existence. Because of this, documentation can get scattered and outdated, and finding the appropriate information bears the risk of losing time sifting through the many folders and files that may exist. In this tip we look at how to leverage Extended Properties to document your database.

In an earlier tip (Building a SQL Server Data Dictionary with the Extended Properties and Third Party Tools) I outlined the value of using the extended properties across some of the industry leading data modeling tools. What if I do not have access to any of those tools? Is it possible to build the extended properties and then be able to generate reports by using Excel or Reporting Services? How can I capture the data dictionary at a point in time?

I need a way to create a data dictionary that will stay up to date. I make many changes to the database and I spend more time trying to keep the documentation updated than doing database administration.

In an earlier tip (Building a SQL Server Data Dictionary with the Extended Properties and Third Party Tools) I outlined the value of using the extended properties across some of the industry leading data modeling tools. What if I do not have access to any of those tools? Is it possible to build the extended properties and then be able to generate reports by using Excel or Reporting Services? How can I capture the data dictionary at a point in time?

A data dictionary is a collection of descriptions about the data and objects in a data model for the benefit of programmers and users who need to refer to them when building the application, reports, etc. It is ideal to create a data dictionary while working on the data model. When the database is small, developers often use built-in SQL Server tools and scripts to create the data model. However when the data model grows into an enterprise level data model with all of the associated complexities, the management and maintenance grow as well. So what options are available to manage and build the data model? Are there any third party tools available?

Systems could often have volumes of documentation written about them because of their complexity, the extensive changes over time and the many minds that worked on them throughout their existence. Because of this, documentation can get scattered and outdated, and finding the appropriate information bears the risk of losing time sifting through the many folders and files that may exist. In this tip we look at how to leverage Extended Properties to document your database.

Developers and/or Database Administrators (DBAs) are often asked to create a report of different database objects on a database server. I have been evident of situations where people start writing code at the last minute to collect details about different database objects and then the results are sent in the form of Excel spreadsheets. Also such reports are one of the requirements in release notes for any build. In this tip we will discuss how we can create a last minute report, with almost no coding effort for basic details about the database objects limiting the requirement of coding efforts for only advanced level details.

Systems could often have volumes of documentation written about them because of their complexity, the extensive changes over time and the many minds that worked on them throughout their existence. Because of this, documentation can get scattered and outdated, and finding the appropriate information bears the risk of losing time sifting through the many folders and files that may exist. In this tip we look at how to leverage Extended Properties to document your database.

Developers and/or Database Administrators (DBAs) are often asked to create a report of different database objects on a database server. I have been evident of situations where people start writing code at the last minute to collect details about different database objects and then the results are sent in the form of Excel spreadsheets. Also such reports are one of the requirements in release notes for any build. In this tip we will discuss how we can create a last minute report, with almost no coding effort for basic details about the database objects limiting the requirement of coding efforts for only advanced level details.

In an earlier tip (Building a SQL Server Data Dictionary with the Extended Properties and Third Party Tools) I outlined the value of using the extended properties across some of the industry leading data modeling tools. What if I do not have access to any of those tools? Is it possible to build the extended properties and then be able to generate reports by using Excel or Reporting Services? How can I capture the data dictionary at a point in time?

A data dictionary is a collection of descriptions about the data and objects in a data model for the benefit of programmers and users who need to refer to them when building the application, reports, etc. It is ideal to create a data dictionary while working on the data model. When the database is small, developers often use built-in SQL Server tools and scripts to create the data model. However when the data model grows into an enterprise level data model with all of the associated complexities, the management and maintenance grow as well. So what options are available to manage and build the data model? Are there any third party tools available?

I need a way to create a data dictionary that will stay up to date. I make many changes to the database and I spend more time trying to keep the documentation updated than doing database administration.