Blog Pro de Jean-Baptiste HEREN

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 :

[SQL]
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

[VB]
Sub generateSQLSpecialProperties()
  ' Author: Jean-Baptiste Heren - 2011-03-29 - http://blog.jbheren.com
  ' 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
      Loop
    
    myCol = 2 ' Return to specifiedcolumn
    myRow = myRow + 1 ' Move to next row
    
    Print #fnum, dataSQL
  Loop
  
  ' Close the file
  Close #fnum
  
End Sub

2- Extract table properties from SQL Sercer System tables

[SQL]
/* get extended properties on Tables */
SELECT 
  A.NAME as table_name
 ,B.name as property_name
 ,B.value as property_value
FROM SYSOBJECTS A INNER JOIN sys.extended_properties B on A.id = B.major_id
WHERE A.type = 'U'