How to add comments to your database

  • Darko Martinović

    Hall of Fame

    Points: 3535

    Comments posted to this topic are about the item How to add comments to your database

  • Stewart "Arturius" Campbell

    SSC Guru

    Points: 71483

    Nice, easy one to end the week on, thanks Darko

    ____________________________________________
    Space, the final frontier? not any more...
    All limits henceforth are self-imposed.
    “libera tute vulgaris ex”

  • HappyGeek

    SSCoach

    Points: 18670

    Nice simple one to end the week on, thank you.

    ...

  • Chris Harshman

    SSC-Forever

    Points: 41845

    I almost fell for Extended Attributes, but did guess the right answer.  I think that page of my memory might have been pushed out by the least recently used algorithm 😉

  • Darko Martinović

    Hall of Fame

    Points: 3535

    There are lot of examples on the internet, how to setup extended properties using T-SQL or PowerShell.

    In some cases, will be useful, do this task by utilizing .NET.

    Extended property is a class located in  Microsoft.SqlServer.Smo assembly. This assembly is usually located in SDK\Assemblies directory of your SQL Server installation folder.

    Let us create a console application, by using Visual Studio 2013 or above. 
    Let’s use VB.NET, because it is easier to follow.

    At first step, add few references, as showed on the picture bellow.


    Let's create extended property with the name 'Creator' and with value 'Sql Server Central'

    In order to do that, put this code snippet in your VisualStudio solution. Replace ‘instance name’ with your instance name.
    Replace ‘username’ with your user name e.g. ‘sa’.
    Replace ‘user password’ with password.
    The example, works with ‘AdventureWorks2012’.
    You can easy follow and replace database name as well as object name.

    Press F5

    In addition, you see the result

     

    Imports Microsoft.SqlServer.Management.Common
    Imports Microsoft.SqlServer.Management.Smo

    Module Module1



      Private Const C_DATABASENAME = "AdventureWorks2012"
      Private Const CREATEOR = "Creator"
      Private Const VALUE = "Sql Server Central"

      Sub Main()



       'Replace instance name with your instance name. The same with user name & password
       Dim cnn As ServerConnection = New ServerConnection("your instance name", "user name", "password")
       cnn.Connect()


       Dim server As Server = New Server(cnn)


       'Set extended property on database level
       Dim db As Microsoft.SqlServer.Management.Smo.Database = server.Databases(C_DATABASENAME)
       Dim extProperty As ExtendedProperty = Nothing
       If db.ExtendedProperties(CREATEOR) Is Nothing Then
        extProperty = New ExtendedProperty
         extProperty.Parent = db
        extProperty.Name = CREATEOR
        extProperty.Value = VALUE
        extProperty.Create()
       Else
         extProperty = db.ExtendedProperties(CREATEOR)
        extProperty.Value = VALUE
        extProperty.Alter()
       End If


       'Iterate through schemas
       'For Each s As Schema In db.Schemas
       '  If s.ExtendedProperties(CREATEOR) Is Nothing Then
       '   extProperty = New ExtendedProperty
       '   extProperty.Parent = s
       '   extProperty.Name = CREATEOR
       '   extProperty.Value = VALUE
       '   extProperty.Create()
       '  Else
       '   extProperty = s.ExtendedProperties(CREATEOR)
       '   extProperty.Value = VALUE
       '   extProperty.Alter()
       '  End If

       'Next

       'Example with the schema HumenResources
       Dim sch As Schema = db.Schemas("HumanResources")
       If sch.ExtendedProperties(CREATEOR) Is Nothing Then
         extProperty = New ExtendedProperty
        extProperty.Parent = sch
         extProperty.Name = CREATEOR
        extProperty.Value = VALUE
         extProperty.Create()
       Else
        extProperty = sch.ExtendedProperties(CREATEOR)
        extProperty.Value = VALUE
        extProperty.Alter()
       End If

       'iterate through tables
       'For Each t As Table In db.Tables
       '  If t.ExtendedProperties(CREATEOR) Is Nothing Then
       '   extProperty = New ExtendedProperty
       '   extProperty.Parent = t
       '   extProperty.Name = CREATEOR
       '   extProperty.Value = VALUE
       '   extProperty.Create()
       '  Else
       '   extProperty = t.ExtendedProperties(CREATEOR)
       '   extProperty.Value = VALUE
       '   extProperty.Alter()
       '  End If

       'Next


       'Example with the table Employee
       Dim tbl As Table = db.Tables("Employee", "HumanResources")
       If tbl.ExtendedProperties(CREATEOR) Is Nothing Then
         extProperty = New ExtendedProperty
        extProperty.Parent = tbl
         extProperty.Name = CREATEOR
        extProperty.Value = VALUE
         extProperty.Create()
       Else
        extProperty = tbl.ExtendedProperties(CREATEOR)
         extProperty.Value = VALUE
        extProperty.Alter()
       End If


       'Example with column
       Dim column As Column = tbl.Columns("NationalIDNumber")
       If column.ExtendedProperties(CREATEOR) Is Nothing Then
        extProperty = New ExtendedProperty
        extProperty.Parent = column
        extProperty.Name = CREATEOR
        extProperty.Value = VALUE
        extProperty.Create()
       Else
        extProperty = column.ExtendedProperties(CREATEOR)
         extProperty.Value = VALUE
        extProperty.Alter()
       End If

       'Indexes
       Dim ind As Index = tbl.Indexes("PK_Employee_BusinessEntityID")
       If ind.ExtendedProperties(CREATEOR) Is Nothing Then
         extProperty = New ExtendedProperty
        extProperty.Parent = ind
        extProperty.Name = CREATEOR
        extProperty.Value = VALUE
        extProperty.Create()

       Else
         extProperty = ind.ExtendedProperties(CREATEOR)
        extProperty.Value = VALUE
        extProperty.Alter()
       End If


       'Stored procedure
       Dim sp As StoredProcedure = db.StoredProcedures("uspUpdateEmployeeHireInfo", "HumanResources")
       If sp.ExtendedProperties(CREATEOR) Is Nothing Then
         extProperty = New ExtendedProperty
        extProperty.Parent = sp
        extProperty.Name = CREATEOR
        extProperty.Value = VALUE
        extProperty.Create()

       Else
         extProperty = sp.ExtendedProperties(CREATEOR)
        extProperty.Value = VALUE
        extProperty.Alter()
       End If


       'Dim trg As DatabaseDdlTrigger = db.Triggers("DDlTriggerLogging")
       'If trg.ExtendedProperties(CREATEOR) Is Nothing Then
       '  extProperty = New ExtendedProperty
       '  extProperty.Parent = trg
       '  extProperty.Name = CREATEOR
       '  extProperty.Value = VALUE
       '  extProperty.Create()

       'Else
       '  extProperty = trg.ExtendedProperties(CREATEOR)
       '  extProperty.Value = VALUE
       '  extProperty.Alter()
       'End If


       'Constraint
       Dim cons As Check = tbl.Checks("CK_Employee_BirthDate")
       If cons.ExtendedProperties(CREATEOR) Is Nothing Then
         extProperty = New ExtendedProperty
        extProperty.Parent = cons
        extProperty.Name = CREATEOR
         extProperty.Value = VALUE
       extProperty.Create()

       Else
         extProperty = cons.ExtendedProperties(CREATEOR)
        extProperty.Value = VALUE
        extProperty.Alter()
       End If


       'View
       Dim view As View = db.Views("vEmployee", "HumanResources")
       If view.ExtendedProperties(CREATEOR) Is Nothing Then
         extProperty = New ExtendedProperty
        extProperty.Parent = view
        extProperty.Name = CREATEOR
        extProperty.Value = VALUE
         extProperty.Create()

       Else
        extProperty = view.ExtendedProperties(CREATEOR)
        extProperty.Value = VALUE
        extProperty.Alter()
       End If


       'Assembliy
       'Dim ass As SqlAssembly = db.Assemblies("SQLCLRReporter")
       'If ass.ExtendedProperties(CREATEOR) Is Nothing Then
       '  extProperty = New ExtendedProperty
       '  extProperty.Parent = ass
       '  extProperty.Name = CREATEOR
       '  extProperty.Value = VALUE
       '  extProperty.Create()

       'Else
       '  extProperty = ass.ExtendedProperties(CREATEOR)
       '  extProperty.Value = VALUE
       '  extProperty.Alter()
       'End If


       Dim xmlsc As XmlSchemaCollection = db.XmlSchemaCollections("IndividualSurveySchemaCollection", "Person")
       If xmlsc.ExtendedProperties(CREATEOR) Is Nothing Then
         extProperty = New ExtendedProperty
        extProperty.Parent = xmlsc
        extProperty.Name = CREATEOR
        extProperty.Value = VALUE
         extProperty.Create()

       Else
        extProperty = xmlsc.ExtendedProperties(CREATEOR)
        extProperty.Value = VALUE
        extProperty.Alter()
       End If


       Dim fk As ForeignKey = tbl.ForeignKeys("FK_Employee_Person_BusinessEntityID")
       If fk.ExtendedProperties(CREATEOR) Is Nothing Then
         extProperty = New ExtendedProperty
        extProperty.Parent = fk
        extProperty.Name = CREATEOR
         extProperty.Value = VALUE
        extProperty.Create()

       Else
         extProperty = fk.ExtendedProperties(CREATEOR)
        extProperty.Value = VALUE
        extProperty.Alter()
       End If



       'Dim tf As UserDefinedFunction = db.UserDefinedFunctions("function name", "schema name")
       'If tf.ExtendedProperties(CREATEOR) Is Nothing Then
       '  extProperty = New ExtendedProperty
       '  extProperty.Parent = tf
       '  extProperty.Name = CREATEOR
       '  extProperty.Value = VALUE
       '  extProperty.Create()

       'Else
       '  extProperty = tf.ExtendedProperties(CREATEOR)
       '  extProperty.Value = VALUE
       '  extProperty.Alter()
       'End If



       'Dim types As UserDefinedTableType = db.UserDefinedTableTypes("type name", "schema name")
       'If types.ExtendedProperties(CREATEOR) Is Nothing Then
       '  extProperty = New ExtendedProperty
       '  extProperty.Parent = types
       '  extProperty.Name = CREATEOR
       '  extProperty.Value = VALUE
       '  extProperty.Create()

       'Else
       '  extProperty = types.ExtendedProperties(CREATEOR)
       '  extProperty.Value = VALUE
       '  extProperty.Alter()
       'End If


       Dim types2 As UserDefinedDataType = db.UserDefinedDataTypes("Flag")
       If types2.ExtendedProperties(CREATEOR) Is Nothing Then
        extProperty = New ExtendedProperty
        extProperty.Parent = types2
        extProperty.Name = CREATEOR
        extProperty.Value = VALUE
        extProperty.Create()

       Else
         extProperty = types2.ExtendedProperties(CREATEOR)
        extProperty.Value = VALUE
        extProperty.Alter()
       End If



       If cnn.IsOpen Then
         cnn.Disconnect()
        cnn = Nothing
       End If
       If server IsNot Nothing Then
         server = Nothing
       End If




      End Sub

    End Module


  • Revenant

    SSC-Forever

    Points: 42467

    Nice question and nice post - thanks!

  • TomThomson

    SSC Guru

    Points: 104772

    Nice straightforward question and good clear explanation. 

    But I can't agree with the remark in your post above that says "Let’s use VB.NET, because it is easier to follow".
    Personally I have found that developers generally find JavaScript much easier to follow than VB in any of its variants,  and also manage to provide a better intended functionality to bugs ratio when writing in JavaScript than when writing in VB.

    Tom

  • Revenant

    SSC-Forever

    Points: 42467

    TomThomson - Monday, July 10, 2017 3:57 PM

    Nice straightforward question and good clear explanation. 

    But I can't agree with the remark in your post above that says "Let’s use VB.NET, because it is easier to follow".
    Personally I have found that developers generally find JavaScript much easier to follow than VB in any of its variants,  and also manage to provide a better intended functionality to bugs ratio when writing in JavaScript than when writing in VB.

    Absolutely. Also IMO important consideration is that JS is the number one programming language so in case of a shuffle on the team or a handover from dev to maint it is much easier to find someone with that experience.

  • Scott Coleman

    One Orange Chip

    Points: 27406

    RE: VB vs Java (why not PowerShell?) for managing extended properties.  Why use either?
    You can use sys.extended_properties to view all extended properties.
    If you put an INSTEAD OF trigger on this view that turns INSERT, UPDATE and DELETE operations into sp_addextendedproperty, sp_updateextendedproperty, and sp_dropextendedproperty calls, you can easily manage extended properties with normal SQL operations.

    I wrote this once just for fun, but I was afraid to leave it deployed because of the danger of "DELETE FROM sys.extended_properties" with no WHERE clause.  Maybe it would be acceptable with a limitation of "IF (SELECT COUNT(*) FROM DELETED) = 1" to only allow one property change at a time, or "IF (SELECT COUNT(*) FROM (SELECT DISTINCT class, major_id FROM DELETED) c) = 1" to only allow properties of one object at a time to be changed.

  • Lowell

    SSC Guru

    Points: 323442

    Scott Coleman - Tuesday, July 11, 2017 11:17 AM

    RE: VB vs Java (why not PowerShell?) for managing extended properties.  Why use either?
    You can use sys.extended_properties to view all extended properties.
    If you put an INSTEAD OF trigger on this view that turns INSERT, UPDATE and DELETE operations into sp_addextendedproperty, sp_updateextendedproperty, and sp_dropextendedproperty calls, you can easily manage extended properties with normal SQL operations.

    I wrote this once just for fun, but I was afraid to leave it deployed because of the danger of "DELETE FROM sys.extended_properties" with no WHERE clause.  Maybe it would be acceptable with a limitation of "IF (SELECT COUNT(*) FROM DELETED) = 1" to only allow one property change at a time, or "IF (SELECT COUNT(*) FROM (SELECT DISTINCT class, major_id FROM DELETED) c) = 1" to only allow properties of one object at a time to be changed.

    i think today sys.extended_properties cannot be modified the way you could in SQL2000;
    would it not take a supreme hack, via a Dedicated Admin Connection, to try and modify or otherwise hook into anything in the sys schema?
    I know i spent quite a bit of time wanting to create a view sys.functions as a companion to sys.procedures, and gave up.

    Lowell


    --help us help you! If you post a question, make sure you include a CREATE TABLE... statement and INSERT INTO... statement into that table to give the volunteers here representative data. with your description of the problem, we can provide a tested, verifiable solution to your question! asking the question the right way gets you a tested answer the fastest way possible!

  • Darko Martinović

    Hall of Fame

    Points: 3535

    Scott Coleman - Tuesday, July 11, 2017 11:17 AM

    RE: VB vs Java (why not PowerShell?) for managing extended properties.  Why use either?
    You can use sys.extended_properties to view all extended properties.
    If you put an INSTEAD OF trigger on this view that turns INSERT, UPDATE and DELETE operations into sp_addextendedproperty, sp_updateextendedproperty, and sp_dropextendedproperty calls, you can easily manage extended properties with normal SQL operations.

    I wrote this once just for fun, but I was afraid to leave it deployed because of the danger of "DELETE FROM sys.extended_properties" with no WHERE clause.  Maybe it would be acceptable with a limitation of "IF (SELECT COUNT(*) FROM DELETED) = 1" to only allow one property change at a time, or "IF (SELECT COUNT(*) FROM (SELECT DISTINCT class, major_id FROM DELETED) c) = 1" to only allow properties of one object at a time to be changed.

    Usually extended properties goes with database delivery ( publishing database changes ). 

    The main question is how is database delivery is accomplished. 

     I am coauthor of custom solution, which use .NET in order to deploy database changes. 

    So, in my opinion .NET is better choice then T-SQL. 

    It can be controlled better. 

    This is just my opinion from the perspective of someone who manages this process from hundreds of instances and I don't know how many databases.

    I'm talking about versions of SQL Server from 2005 onwards and all kind of editions. 

    I'm sure your solution is good and if you're happy with it, great. 

    Thanks for the post. 🙂

  • Darko Martinović

    Hall of Fame

    Points: 3535

    Revenant - Monday, July 10, 2017 4:48 PM

    TomThomson - Monday, July 10, 2017 3:57 PM

    Nice straightforward question and good clear explanation. 

    But I can't agree with the remark in your post above that says "Let’s use VB.NET, because it is easier to follow".
    Personally I have found that developers generally find JavaScript much easier to follow than VB in any of its variants,  and also manage to provide a better intended functionality to bugs ratio when writing in JavaScript than when writing in VB.

    Absolutely. Also IMO important consideration is that JS is the number one programming language so in case of a shuffle on the team or a handover from dev to maint it is much easier to find someone with that experience.

    From the my perspective, javaScript is not a programming language. It is a script. 
    So to conclude the story, I will in the future produce an article with javaScript and SQL Server.🙂

Viewing 12 posts - 1 through 12 (of 12 total)

You must be logged in to reply to this topic. Login to reply