How to document SQL Server database objects. Introduction. In any good programming reference, you will read that a developer has to document his code, not only for himherself, but also for the person who, ten years later will be asked to maintain it. This would, of course, be made easier thanks to a good documentation of existing code. But, documentation is not simply some document we can open when we need it. Try it out. To download SQL Server 2016, go to Evaluation Center. Have an Azure account Then go Here to spin up a Virtual Machine with SQL Server 2017 already installed. Microsoft SQL Server Version. Download links and complete information for all Microsoft SQL Server versions and builds. Headers Frequently used terms and acronyms. Microsoft Sql Server Transaction Log Truncate Meaning' title='Microsoft Sql Server Transaction Log Truncate Meaning' />Its a complete process that should start no later than the end of every development task with other processes, like Code Quality Assessment and Testing. In the best case, both of these processes should take documentation into account. For instance, a unit test that fails whenever an undocumented object is found could be created. Edgewood Solutions is a technology company focused on Microsoft SQL Server and founder of MSSQLTips. View all my tips. In this post Ill take a practical approach at talking about what SQL Server Mirroring is, the advantages and considerations that follows. Availability, not scalability. If everything is done well, then we are sure that each version of a database solution that is distributed always contains documentation. Note that a unneglectable step of this documentation process should be a regular review of existing documentation, so that it stays up to date and reflects reality. The following figure depicts and summarizes development workflow as discussed above. As SQL Server database development is a good candidate for code documentation, this development workflow applies and this article will focus on following steps. Inventory assets to document. Describe undocumented assets. Review documented assets. In the following sections, we will first review a solution provided by Microsoft for documenting SQL Server database objects and come to the conclusion that this has some missing features and that we could define another way to document our code. Then we will discuss a first solution that consists in the implementation of a stored procedure that calls the built in CRUD components presented above in order to manage extended properties from a single entry point. However, this solution wont overcome some aspects. So, we will review a second solution that will implement steps in development workflow related to documentation. The first step, Inventory assets to document, will leverage a corresponding stored procedure that will populate one or more SQL tables based on objects in the INFORMATIONSCHEMA schema and optionally consider existing extended properties. The second and third steps are more of a manual process and we will discuss a set of database objects that will become helpful in the completion of this process and the way to chain them. Finally, as we should not close the door to direct extended property usage, we will review a stored procedure that will take the data back from the documentation we did, using the review process, and apply the documentation to extended properties for each SQL database. Some of presented procedures are still under development and have only been tested with common SQL Server database objects like tables, views, procedures, types and functions. Handling of other kinds of objects like those related to SQL Server Service Broker are still to be implemented. Microsofts solution for SQL Server documentation Extended Properties. Well, SQL Server developers at Microsoft may agree with the principle of documenting database development. This is certainly the reason why we can find a feature called Extended Properties since at least SQL Server 2. You can imagine it as metadata associated with a given database object and even a database itself. Since the metadata are stored in the database, it are accessible to any database principal with sufficient privileges. Furthermore, it are backed up and restored whenever database is respectively backed up or restored. We can define multiple extended properties to a single object. These properties can bear the name you want but there are commonly used names like. MSDescription that is used by multiple tools as the name for a description property of the object. We will talk about it again later in this article. Version and Version. Date mainly for database or schema objects. These extended properties can be accessed using a T SQL query to sys. Here is a sample results of a query that selects every column from that table. Records in that table can be respectively created, edited or removed using following stored procedures. Their names speak for themselves. There is also a built in function that allows you to list extended properties based on a series of criteria. This procedure is called sys. Those four programmable components will be referred as built in CRUD components in the remainder of the article. We could also access these properties using SQL Server Management Studio by right clicking on a database object and choosing to display its properties. In the dialog, we will find an Extended Properties tab. While this feature seems very helpful, its really underused and there are simple reasons to it. Lack of knowledge developers do not even know it exists. Lack of time. Usage of three stored procedures above is not that simple the add procedure fails when a record exists, the update one when there is no defined extended property. One database at a time as extended properties are stored in a database, you cant query for extended properties from multiple databases in a single SELECT query. Properties are dropped when the object is dropped. Solution 1 a stored procedure to manage extended properties. There are some problems in the list of potential reasons for the lack of adoption of extended properties that can be overcome with the implementation of a stored procedure. For instance, it would be easy to remove the obligation to use multiple objects and check whether an extended property already exists or not. That the raison dtre of Utils. Manage. Extended. Property stored procedure. This stored procedure will take action of any extended property no matter the database. It will use have almost the same signature as built in CRUD components for extended events management. Lets review its parameters. ALTER PROCEDURE Utils. Manage. Extended. Property. Database. Name VARCHAR2. NULL. Property. Name VARCHAR2. MSDescription. Property. Value VARCHAR1. Level. Type0 VARCHAR1. SCHEMA. Level. Name0 VARCHAR1. Level. Type1 VARCHAR1. NULL. Level. Name1 VARCHAR1. Run Concurrent Program From Oaf Page. NULL. Level. Type2 VARCHAR1. NULL. Level. Name2 VARCHAR1. NULL. Operation. Mode VARCHAR3. Overwrite. Debug BIT 0. ALTER PROCEDUREUtils. Manage. Extended. Property    Database. Name                   VARCHAR2. NULL,    Property. Name                   VARCHAR2. MSDescription,    Property. Battery 3 Gratis Completo Portugues here. Value                  VARCHAR1. Level. Type0                    VARCHAR1. SCHEMA,              Level. Name0                    VARCHAR1. Level. Type1                    VARCHAR1. NULL,        Level. Name1                    VARCHAR1. NULL,    Level. Type2                    VARCHAR1. NULL,        Level. Name2                    VARCHAR1. NULL,    Operation. Mode                  VARCHAR3. Overwrite,        Debug                          BIT0. First, there is the database name in which our stored procedure should apply modifications. If no one is provided, it will take current database name. Then, there is the name of the extended property on which we want to operate followed by common levellt X typename parameters of splt action extendedproperty built in CRUD components. Finally, we can find Operation. Mode and Debug parameters.