Generate Extended properties in MS Sql Server 2005+
Posted on Monday 28 March 2011, 23:02 - Décisionnel - Permalink Tweet
- Article
- |
- Comments(0)
- |
- Attachments(1)
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 - 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
/* 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'Post updated on Monday 28 March 2011, 23:02
Author: Jean-Baptiste Heren
Stay in touch with the latest news and subscribe to the RSS Feed about this category
