Blog Pro de Jean-Baptiste HEREN

Notes d'un consultant Freelance en Informatique

To content | To menu | To search

Generate Extended properties in MS Sql Server 2005+

When you work with databases, it's cool to Keep all the documentation in the system, so that it is available for reporting and all kind of analysis.

Microsoft SQL Server 2005+ provides the ability to create Extended properties on tables (or even fields). Those properties are available using SQL management studio, on the tables properties panel.


Here I will explain how you can generate them fast using Excel and macro AND Extract them using SQL.

1- Generate SQL Script to insert properties and values from an Excel sheet

Usually, for any initial documentation, it will be easyer to fill an excel sheet and generate allk the indidual properties automatically. You don't want to spend a week filling all the stuff manually(!). You will find as an attachment to this post, an Excel demonstration excel file containing a simple sheet template and a macro. The maco will gerenate SQL instructions like this :

EXEC sys.sp_addextendedproperty @name=N'MY_PROPERTY_NAME', @value=N'MY_PROPERTY_VALUE', @level0type=N'SCHEMA',@level0name=N'dbo', @level1type=N'TABLE',@level1name=N'MY_TABLE_NAME';

Here is the SQL macro code

Sub generateSQLSpecialProperties()
  ' Author: Jean-Baptiste Heren - 2011-03-29 -
  ' Loop on current scheet and generate sql sys.sp_addextendedproperty based on sheet contents
  ' First column contains the table name
  ' first line contains the Properties names ex: Table_name | prop_description | prop_rule ...
  Dim tableName As String 'tablename extracted from first row of each column
  Dim filePath As String
  Dim slashPosition As Integer
  Dim pathOnly As String
  Dim dataSQL As String    ' will contain the SQL Contents
  Dim separator As String
  Dim myRow As Integer 'Row counter
  Dim myCol As Integer 'Column counter
  ' get current excel file Path
  filePath = ThisWorkbook.FullName
  slashPosition = InStrRev(filePath, "\")
  pathOnly = Left(filePath, slashPosition)
  MyFile = pathOnly + ActiveSheet.Name + "_macro_generated.sql"
  'get column values from second row
  myRow = 2
  myCol = 2
  'set and open file for output
  fnum = FreeFile()
  Open MyFile For Output As fnum
  Do Until ActiveSheet.Cells(myRow, myCol) = "" 'Loop on rows until blank.
      tableName = Trim(CStr(ActiveSheet.Cells(myRow, 1))) 'get Table name from first column
      Do Until ActiveSheet.Cells(myRow, myCol) = "" 'Loop on columns until blank.
        property_name = Trim(CStr(ActiveSheet.Cells(1, myCol))) 'get Property name from first column
        property_descr = Replace(Trim(CStr(ActiveSheet.Cells(myRow, myCol))), "'", "''")
        dataSQL = "EXEC sys.sp_addextendedproperty @name=N'" + property_name + "'"
        dataSQL = dataSQL + ", @value=N'" + property_descr + "'"
        dataSQL = dataSQL + ", @level0type=N'SCHEMA',@level0name=N'dbo', @level1type=N'TABLE'"
        dataSQL = dataSQL + ",@level1name=N'" + tableName + "';"
        myCol = myCol + 1
    myCol = 2 ' Return to specifiedcolumn
    myRow = myRow + 1 ' Move to next row
    Print #fnum, dataSQL
  ' Close the file
  Close #fnum
End Sub

2- Extract table properties from SQL Sercer System tables

/* get extended properties on Tables */
  A.NAME as table_name
 , as property_name
 ,B.value as property_value
FROM SYSOBJECTS A INNER JOIN sys.extended_properties B on = B.major_id
WHERE A.type = 'U'
Jean-Baptiste Heren

Author: Jean-Baptiste Heren

Stay in touch with the latest news and subscribe to the RSS Feed about this category

Comments are closed

You Might Also Like


Cognos Report : Display a different measure in row for the aggregated column

Following tip is when you use a Relational Datamodel (DMR). In a Cognos Report crosstab, If You need to Display different measure in the aggregated column, you can do it using a simple if-then-else...

Continue reading

Cognos 8 - convert a member unique name from one hierarchy to another

If you need to build any report or Event studio Query using the same data from different packages; and you want to prompt just once. You can convert the member unique name from one hierarchy to...

Continue reading