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

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 Database Engine instance. The scripts and technical details are in the Github repository SQLAdmin (AdventureWorks).

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 of the Database Engine instance.

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

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. Configuring Query Store on the database is optional before SQL Server 2022, but could be handy on a non-production instance.

History

2024-10-28 : Post made general and with scripts in Github.
2022-05-05 : Post created on SQL Server 2019

Check for pending restart

 Windows pending restart is unfortunately not a singular registration, but is in three differen places in the Windows Registry:

• HKLM: SOFTWARE\Microsoft\Windows\CurrentVersion\WindowsUpdate\Auto Update\RebootRequired
• HKLM: System\CurrentControlSet\Control\Session Manager, PendingFileRenameOperations
• HKLM: SOFTWARE\CapaSystems\RestartStatus, Reboot

The last one is specific for the Capa (CapaSystems.dk) systems, that I have met in a IT infrastructure.

xp_dirtree (T-SQL)

It is possible to get the content of a directory from T-SQL with the Extended Stored Procedure (XP) master.dbo.xp_dirtree. This only requires the SQL Server service account to have access to the directory and have browse rights.

Syntax:

EXECUTE master.dbo.xp_dirtree <Directory>, <Depth>, <IsFile>

• <Directory> nvarchar(???): Full path (name) of directory. Can be UNC path. The value can end with '\' or not.
• <Depth> int: Depth of list in directory.
• <IsFile> int: Show if row is on a file. If the value in non-zero then the result will show if a row is in a file.

The result is a table with the three columns subdirectory, depth and file where

• subdirectory is the name of the subdirectory or file in the given directory.
• depth is the depth of the directory/file in the given directory. One ('1') is the root of the current directory.
• file indicates if the directory is a file ('1') or not ('0'). If not a file then it is a directory.

For some reason the value for <Directory> can't be from SERVERPROPERTY like
EXECUTE master.dbo.xp_dirtree SERVERPROPERTY('InstanceDefaultBackupPath'), 1,1;.
The error is
Msg 102, Level 15, State 1, Line 1
Incorrect syntax near 'InstanceDefaultBackupPath'.
Not even with CONVERT like
EXECUTE master.dbo.xp_dirtree CONVERT(nvarchar(512), SERVERPROPERTY('InstanceDefaultBackupPath')), 1,1;
where the error is
Msg 156, Level 15, State 1, Line 1
Incorrect syntax near the keyword 'CONVERT'.

The system function SERVERPROPERTY can be used through a temporary variable like this
DECLARE @_backuppath nvarchar(512) = CONVERT(nvarchar(512), SERVERPROPERTY('InstanceDefaultBackupPath'));
EXECUTE master.dbo.xp_dirtree @_backuppath, 1,1;

Example:

EXECUTE master.dbo.xp_dirtree N'D:\MSSQL\Backup', 1, 1;
List (backup) files in folder.

Unfortunately this Extended Stored Procedure is not documentd by Microsoft. Like many other XP's...

History

2024-06-25: Examples on SERVERPROPERTY added.
2022-03-25: Post published.

Using sp_executesql with PowerShell

 The system extended stored procedure sp_executesql is recommended in general to execute dynamic SQL to protect from SQL injection attack. Using sp_execute in PowerShell is like using any other stored procedure in PowerShell. But I prefer to do it in ADO.NET instead of using dynamic CmdLets like Invoke-SqlCmd. The reason is that I have a rather tight control with the connectivity and can make the call really fast.

The example below is a test on if a database exists with the given name. I have chosen to implement the test with a COUNT() function call so that the result always is the same type (integer) no matter if the database exists or not. Also COUNT() is usually very effective in the statement execution.

First I give the database name to look for:

$DbName = 'SSISDB'

Then I prepare the call and set up the connection to the SQL Server installation:

$SqlInstanceName = '(local)\MSSQL2019'
$CnnStr = "Server=$SqlInstanceName;Integrated Security=SSPI"
$SqlStatement = 'SELECT COUNT(*) AS [db_count] FROM [master].[sys].[databases] WHERE [name]=@dbname;'
$SqlParameters = '@dbname sysname'
$CnnSql = New-Object System.Data.SqlClient.SqlConnection
$CnnSql.ConnectionString = $CnnStr
$CnnSql.Open()

Then I call sp_executesql with the query and get the result. I close the connection as soon as possible to release the session resources:

$CmdSql = New-Object System.Data.SqlClient.SqlCommand
$CmdSql.Connection = $CnnSql
$CmdSql.CommandText = '[master].[sys].[sp_executesql]'
$CmdSql.CommandType = [System.Data.CommandType]::StoredProcedure
$CmdSql.Parameters.Add('@stmt', [System.Data.SqlDbType]::NVarChar, -1).Value = $SqlStatement
$CmdSql.Parameters.Add('@params', [System.Data.SqlDbType]::NVarChar, 500).Value = $SqlParameters
$CmdSql.Parameters.Add('@dbname', [System.Data.SqlDbType]::NVarChar, 128).Value = $DbName
$Rdr = $CmdSql.ExecuteReader()
while ($Rdr.Read()) { $DbCount = $Rdr['db_count'] }
$Rdr.Close()
$CnnSql.Close()

Afterwards I can work with the result from the query:

"Db Count: $DbCount"

This example can be used for calling any stored procedure.