How to add comments to your database
-
Comments posted to this topic are about the item How to add comments to your database
-
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” -
Nice simple one to end the week on, thank you.
...
-
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 😉
-
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 -
Nice question and nice post - thanks!
-
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
- TomThomson - Monday, July 10, 2017 3:57 PM
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.
-
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.
- Scott Coleman - Tuesday, July 11, 2017 11:17 AM
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! - Scott Coleman - Tuesday, July 11, 2017 11:17 AM
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. 🙂
- Revenant - Monday, July 10, 2017 4:48 PM
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 11 (of 11 total)
You must be logged in to reply to this topic. Login to reply