Descriptions

Databases and objects in databases on SQL Server can be described with the Extended Property MS_Description. This description can be read by and used with Visual Studio, which gives a handy access to detailed documentation.

Unfortunately does instances and server-level objects like logins not have Extended Properties. So these elements can not have a description direct attached. There are some alternatives you can consider for these elements such like custom registry keys, file in root directory. But please don't create a custom table in the master database!

Description Lifecycle

A description can be created with the stored procedure sp_addextendedproperty. The procedure has eight parameters to control the name, value and level in the object hierarchy of the extended property. The value of the description has the type sql_variant, and the size can not be more than 7,500 bytes.

Later the description can be changed with the stored procedure sp_updateextendedprocedure.

Sometimes a tool or a clone can require the description to be removed. This can be done with the stored procedure sp_dropextendedproperty.

Extended Properties can be viewed through the catalog view sys.extended_properties or the function sys.fn_listextendedproperty.

Example on table description

EXECUTE sys.sp_updateextendedproperty
  @name=N'MS_Description', @value=N'List of SQL Server collations',
  @level0type=N'SCHEMA',@level0name=N'sqladmin',
  @level1type=N'TABLE',@level1name=N'collation';

SQL Server Management Studio

In SQL Server Management Studio (SSMS) the description on a object can be set as described above. But there are two more ways to add a description to a object. They are short described below, but the order they are mentioned in does not imply any ranking of the possibilities.
The first way to add a description is in the SSMS table design tool. Right-click on a object in the Object Explorer and click Design. Show the properties of the object with another right-click or press Alt+Enter. Then you can edit the Description property.

The second way to add a description is in the Database Diagrams of a given database. In here you can access the properties of a objects and edit the Description property.

Visual Studio

In SQL Server Management Studio (SSMS) extended properties are in the object properties. Here each extended property like MS_Description can be created, viewed, changed and deleted.

A Visual Studio data project can be created on a existing database or with a new database. Open the design of a table and the description in MS_Description is in the properties window, usually down to the right in Visual Studio.

In the example above the table "collation" has the description "SQL Server collations", that is placed in the extended property MS_Description of the table.

Actually the value of the description can also be handled (create/change) in a Visual Studio data project.

Data Classifications

When Microsoft introduced data classification with SQL Server 2012 this information was stored in extended properties. But with SQL Server 2016 this information is in (hidden) system tables.

Discussion

I can only recommend that you put the extended property MS_Description on each database and every object in the databases. At first it wil be a disturbance and somewhat annoying. But when you databases get in production, have lived for a few years and new developers og DBAs are to keep the database alive, then you (hopefully) will experience the benefits. That be in SSMS, Visual Studio or a 3rd part tool.

As a start you could add MS_Description to your static code test. At least on database, data objects and functional object. Later you can extend the test to cover more technical parts of the database like filegroups or users.

History

2023-01-01 : SSMS section added.
2022-05-31 : Entry created.

Restore AdventureWorks on SQL Server 2019

 There are several SQL Server sample databases from Microsoft, and one of the easiest to start and still somewhat up-to-date is AdventureWorks. There are various versions and editions of the database, and they are described in the Microsoft document "AdventureWorks sample databases".

This is an example on how to restore AdventuresWorks on a SQL Server 2019 Database Engine instance.

Before you start restoring you have to make the backup file available to the instance. This can be done by copying the file to the default backup location.

Then you can start the restore and some minor configurations to make the database available as below.

USE [master];
RESTORE DATABASE [AdventureWorks] FROM  DISK = N'R:\BACKUP\AdventureWorks2019.bak' WITH
  MOVE N'AdventureWorks2017' TO N'R:\DATA\AdventureWorks.mdf',
  MOVE N'AdventureWorks2017_log' TO N'R:\LOGS\AdventureWorks_log.ldf',
  NORECOVERY, STATS = 5;
GO
RESTORE DATABASE [AdventureWorks] WITH RECOVERY;
GO

ALTER DATABASE [AdventureWorks] SET COMPATIBILITY_LEVEL = 150;
ALTER AUTHORIZATION ON DATABASE::[AdventureWorks] TO [sa];
GO
ALTER DATABASE [AdventureWorks] MODIFY FILE (NAME=N'AdventureWorks2017', NEWNAME=N'AdventureWorks');
ALTER DATABASE [AdventureWorks] MODIFY FILE (NAME=N'AdventureWorks2017_log', NEWNAME=N'AdventureWorks_log');
GO
EXECUTE [AdventureWorks].sys.sp_updateextendedproperty
  @name=N'MS_Description',
  @value=N'AdventureWorks Sample OLTP Database' ;
GO

-- Option
ALTER DATABASE [AdventureWorks] SET QUERY_STORE = ON;
ALTER DATABASE [AdventureWorks] SET QUERY_STORE (OPERATION_MODE = READ_WRITE);
GO

The configurations are not necessary to make the database available to you, but they are nice to make it general available and functional on SQL Server 2019. Configuring Query Store on the database is optional, but could be handy on a non-production instance.