SQL Server Scripts for Database Analysis and Documentation
Introduction
I have written a set of T-SQL scripts to help me design, document, and maintain SQL Server databases, and now I’d like to share them. A few of the scripts use iif and will only work in SQL Server 2008+. These scripts are a work in progress. Some of them have been used for years, others are new and may need some work. Learn from my scripts, but they come with no cost and no promises.
Most of these scripts are used to document database objects by using SQL Server Extended Properties. This type of documentation cannot get lost because it is stored in the database system tables, but generating it and keeping it up to date can be a chore without some help. The T-SQL scripts in this article provide that help.
Extended Properties of database objects:
Database objects can have text data attached to them. This text is known as an Extended Property. Each database object can have multiple extended properties. We will concentrate on linking descriptions of each database object to the object.
It is possible to link any kind of text block to an object. For example, Extended Properties are sometimes used to store caption text for database columns for use in forms.
The extended properties are stored in system tables. There are three kinds of data required to store the database object description:
- An object_id for each name-value pair, to link it to the database object in the table sys.objects.
- The name for the category of text to store. The default type name for descriptive text is "MS_Description".
- The value is the descriptive text you want to attach to the database object.
The object descriptions can be shown, added, and updated in SQL Server Management Studio (SSMS), but this is tedious if you want to document an entire database. The T-SQL scripts below take a good deal of the tedium out of the job.
Adding or Updating descriptions to database objects with my scripts is a three step process.
- Run one of my scripts against the database you would like to document. This will generate multiple SQL statements, one for each relevant database object.
- Add or modify the descriptions as needed.
- Then run the generated scripts against the database to add or update the object descriptions.
My scripts use system views to get and save the descriptions, since the underlying tables change between versions of SQL Server, but Microsoft makes sure the views are updated to provide backward compatibility.
You can get started documenting you database by using the scripts which automatically generate queries to document db objects. This can save a lot of time.
About the Scripts
These scripts should be edited (if necessary) and executed in Microsoft SQL Server Management Studio or a similar SQL Server tool.
These scripts could also give you a basis for creating your own scripts to list or add descriptions to other types of SQL Server data objects.
Executing my scripts can't damage your database because they are read-only. If you don't immediately see how they work, you can play with them without worrying. Executing the scripts that my scripts generate does change Extended Properties, but not database structure or data.
This is information is for educational purposes, so there are no guarantees. Also, this article and the scripts are a work in progress.
If you would like to view Permissions in your database, there is a good article at SQLServerCentral.com: https://www.sqlservercentral.com/articles/database+permissions/111024/
The official resource for this subject is: Transact-SQL Reference (Database Engine) which includes System Views (Transact-SQL)
The scripts
- Any script with a name beginning with Show will list some properties of database objects, including the Description, if one is available. Output is usually a grid.
- The Add and Update scripts generate sets of scripts for creating or modifying Extended Properties, specifically Descriptions.
- Any script with a name beginning with Add will generate a set of scripts covering all of the db objects of the indicated type, with "xxx" to mark the spot where the new description will go.
- Any script with a name beginning with Update will generate a set of scripts covering all of the db objects of the indicated type, with the current description already entered. You can pick out just the descriptions you wish to update, edit them and then execute them.
- Any script with both Add and Auto in the name will generate a set of scripts for the db objects of the indicated type, with an auto-generated description for each object. This only works for a few types of objects, but it can be a real time saver.
Database Scripts
ShowAllObjectsWithExtendedProperties.sql
An extra for troubleshooting Primary Keys
ShowPKDataTypeIndexType
and one for bulk changing schemas
ChangeSchemaMultipleTables.sql
Schema Scripts
Table Scripts
These two scripts return a list of tables with row counts.
RowCount_AllTables.sql
The second script that filters out empty tables may only work directly on the server machine due to server security.
RowCount_AllTablesHavingRecords.sql
Columns
ShowTableColumnDescriptions.sql
ShowTableColumnHavingRecords.sql This script filters out empty tables and may only work directly on the server due to server security.
AddBulkTableColumnDescriptions.sql Using this script requires two steps. The first step reads your database and creates scripts where you can add descriptions. Running these edited scripts creates a second set of scripts which actually add the descriptions to the columns. The advantage over the one step process is where you have multiple columns with the same name. Many databases have CreatedDate and ModifiedDate columns in most tables. The first scripts you generate will create scripts to document all of the CreatedDates at one time. Try it. It is read only until you run the second set of scripts.
Constraints
UpdateTableConstraintDescriptions.sql
ShowTableConstraintDescriptions
AddTableConstraintAutoDescriptions.sql
AddTableConstraintDescriptions.sql
Indexes
Triggers
AddTableTriggerAutoDescriptions.sql
AddTableTriggerDescriptions.sql
UpdateTableTriggerDescriptions.sql
View Scripts
AddBulkViewColumnDescriptions.sql Using this script requires two steps. The first step reads your database and creates scripts where you can add descriptions. Running these edited scripts creates a second set of scripts which actually add the descriptions to the columns. The advantage over the one step process is where you have multiple columns with the same name. Many databases have CreatedDate and ModifiedDate columns in most tables. The first scripts you generate will create a script to document all of the CreatedDates at one time. Try it. It is read only until you run the second set of scripts.
ShowViewColumnDescriptions.sql
UpdateViewColumnDescriptions.sql
Stored Procedure and User Defined Function Scripts
Show_FN_SP_ParameterDescriptions
Add_FN_SP_ParameterAutoDescriptions
Add_FN_SP_ParameterDescriptions
This page is licensed under a Creative Commons Attribution-NonCommercial-NoDerivs 2.5 License by Conrad Muller.
These scripts are intended for education and are provided "AS IS"without warranty of any kind, either expressed or implied, including but not limited to implied warranties of merchantability and/or fitness for a particular purpose.