SQL Server extended Properties

Extended properties is one way to store documentation and information relate to the SQL Server object.

Why we need extended properties? when we can use extended property?

               suppose
                 - I want to perform DB shrink to a DB,
                 - when DB shrink is in progress, i have to block any other tasks to perform on my DB.
                 - or in other word, I should know if DBShrink is running on my DB before I do any operation in it

In such scenario, it will be good, create an extended property on my DB, say "DBStatus" with value "DBShrink" before start actual DB shrink on my DB, so that my application can read this property and stop perform any other operation on the DB until DB Shrink completes. Once I complete DBshrink, i could remove/change status from "DBShrink" to "string.Empty".

above example explain apply extended properties in DB level, but you could apply extended property in Table. Table Column, Procedure, Views etc

You could view extended properties, by checking your DB properties (DBname->Properties)

We could create Extended Properties in 2 ways

1. SQL command

        Use [DatabaseName]

exec sp_addextendedproperty @ name = N'DBStatus' ,@value = N'DBShrink'

To get extended properties use below query

select * from sys.extended_properties where NAME = 'DBStatus' 

2. SMO with C# 

   Refer 2 assemblies 

         1. Microsoft.SqlServer.Smo

         2. Microsoft.SqlServer.ConnectionInfo

   Code as Below

using Microsoft.SqlServer.Management.Smo;
using Microsoft.SqlServer.Management.Common;

public void AddCustomPropertiesTo_DB()
{
            string connstring = @"Server=localhost;user=sa;password=Password!;";
          System.Data.SqlClient.SqlConnection conn = new            
          System.Data.SqlClient.SqlConnection(connstring);
          ServerConnection servConn = new ServerConnection(conn);
          Server server = new Server(servConn);
            
            Database db = server.Databases["{MyDatabaseName}"];  //your database name
            db.ExtendedProperties.Refresh(); //always good to refresh before use. 

            ExtendedProperty extProp  = new ExtendedProperty() 
                                                                       { Parent = db, 
                                                                           Name = "DBStatus", 
                                                                            Value = "DBShrink" 
                                                                        };
           extProp.Create();

           //Update property value

            ExtendedProperty extProp1 = db.ExtendedProperties["DBStatus"];
            extProp1.Value = "DBShrink Completed";
            extProp1.Alter();

           //Drop an extended property

              ExtendedProperty extProp1 = db.ExtendedProperties["DBStatus"];
              extProp1.Drop();
}