In the previous article we looked at how you can add snippets of documentation to a database's metadata using extended properties. As you have seen, this is not complicated, and uses only one system stored procedure - pr_addextendedproperty. However the basic techniques used to manipulate extended properties, while simple, Can become extremely laborious when you want to look at or extract all (or a large selection) of the extended properties which you have added to a database.

So this article will show you how to:

• Extract all the extended properties from a database.
• Extract a specified subset of extended properties from a database.
• Extract all existing extended properties into a Word document using a simple VBA script.
• Once extracted, they are ready to be incorporated into your database documentation.

Extended property storage

Before proceeding to the grist of this article, it is reasonable to ask where extended properties are stored. The eminently simple and reasonable answer is that they can be found in the system metadata view sys.extended_properties. This view looks something like the following:

The good news about this view is that it is incredibly simple. The bad news is that the way that the extended properties (the "name" and "value" )are stored does not relate easily to the information passed in using the stored procedure "pr_addextendedproperty". Indeed, the stored procedure's separation of elements into a hierarchy of levels does not map easily to the "major_id" and "minor_id" of the view either.

So it is essential to realise that what you are looking at are IDs of objects. Globally, all table, view, stored procedure and function objects - and all the objects such as columns, indexes, constraints and parameters which depend on these objects can be discerned by the major_id, which is the object_id in sys.objects. For columns, the minor_id is the column_id. Just about everything else needs to be linked to another ID - sys.data_spaces for filegroups, etc. (I won't list it all out here, as the code to come has all of this). This is made easier to understand by the "class_desc", which indicates (reasonably clearly) which system table contains the major_id.

Scripting extended properties using SSMS

SQL Server Management Studio will script out extended properties as follows:

• Scripting any individual object from SSMS will also by default script out the extended properties, and will script out all extended properties relative to the object - including those such as parameters, defaults etc... which cannot be created or modified using SSMS.
• Scripting a selection of database objects by right-clicking on the database and selecting Tasks=>Generate Scripts will by default also generate the extended properties - but only if you choose to script DROP or CREATE scripts too.

So, now that you know where to look, and have a basic grasp of what you are looking at, it is time to move on to getting this metadata out of the database.

fn_listextendedproperty

To begin with, welcome to the system function "fn_listextendedproperty". As there are many good descriptions available on the web to explain it, I will not overload this article with an in-depth rehash, but do advise you to look at it, as it certainly has its uses as a quick way of listing out extended properties.

The function requires seven parameters:

1. Property name
2. Level 0 object type
3. Level 0 object name
4. Level 1 object type
5. Level 1 object name
6. Level 2 object type

So it uses the same hierarchy to extract extended properties as you needed to understand to create them.
A few quick examples should make this clearer:

Level 0 objects (Schema, Filegroup, Partition Function, Partition Schema, DDL Trigger)

Here is a schema example:

SELECT objtype, objname, name, value
FROM fn_listextendedproperty (NULL, 'schema', NULL, NULL, NULL, NULL, NULL);

Level 1 objects (Table, View, Stored Procedure, User Defined Function, Filegroup Logical file name)

This is how to get the extended properties for all tables in a schema:

SELECT objtype, objname, name, value
FROM fn_listextendedproperty (NULL, 'schema', 'Sales', 'table', NULL, NULL, NULL);
This is how to get the extended properties for a specific table in a schema:
SELECT objtype, objname, name, value
FROM fn_listextendedproperty (NULL, 'schema', 'Sales', 'table', 'Invoice', NULL, NULL);

This is how to get the extended properties for all the views in a schema:

SELECT objtype, objname, name, value
FROM fn_listextendedproperty (NULL, 'schema', 'Sales', 'view', NULL, NULL, NULL);

Level 2 objects (Column, Constraint, Index, Trigger, Parameter)

This is how to get the extended properties for all the columns in a table:

SELECT objtype, objname, name, value
FROM fn_listextendedproperty (NULL, 'schema', 'Sales', 'table', 'Invoice', 'column', NULL);

This is how to get the extended properties for a specific column in a table:

SELECT objtype, objname, name, value
FROM fn_listextendedproperty (NULL, 'schema', 'Sales', 'table', 'Invoice', 'column', 'InvoiceDate');

All in all, I am sure you will agree that it is a quick and reliable way to extract extended properties.

Notes:

• The function follows a hierarchy. If you want the extended properties for a set of tables, you need to specify the schema. If you want the extended properties for a set of columns, you need to specify the schema and table.
• This function does have its limitations, however. Apart from the extremely hierarchical approach to accessing data, it does not allow you to specify multiple filters (several schemas or tables for instance) without a little tweaking.

So here is an example of how to extend the use of the function to extract the extended properties for more than one table and for a range of classifications:

SELECT DISTINCT MD.TABLE_NAME, EP.value, EP.name
FROM 
 (
 SELECT IT.TABLE_NAME
 FROM INFORMATION_SCHEMA.Tables IT
 WHERE IT.TABLE_NAME IN ('Client','Invoice')
 ) MD

CROSS APPLY fn_listextendedproperty (NULL, 'schema', 'Sales', 'table', MD.TABLE_NAME, NULL, NULL) EP 
WHERE EP.name IN ('TestCreate', 'Overview')

This approach lets you select elements from the INFORMATION_SCHEMA table(s) and multiple elements from fn_listextendedproperty - all using standard WHERE clauses.

I will not give more examples, but hope that this will suffice to point you in the right direction. Another trick to accessing extended properties for multiple object types is to use multiple CROSS APPLY queries, like the one shown above and UNION them to provide a single output table.

Extract all the extended properties from a database using system metadata views

However, I am going to suggest that to exploit all your time and effort spent documenting a database more fully, that you are best bypassing "fn_listextendedproperty" altogether, and going straight to the metadata system views, which allow a more direct (and I feel more coherent) way of using this metadata. So from here on I will be using system metadata to access database metadata.

Here again, I will provide a set of code snippets, so that you can copy and paste those that you find useful, even if you don't need all of them.

To take a thoroughly practical example as a starting point, suppose that you have diligently documented your database objects, and now wish to add all this essential information into a Word document to impress your superiors or clients with some mind-blowing technical documentation. Without any retyping, of course.
Let's begin with the simplest of all extended properties - the database. The following code snippet will output all extended properties for the database itself:

SELECT 
'Database' AS PropertyType
,SEP.name AS DescriptionType
,SEP.value AS DescriptionDefinition
FROM sys.extended_properties SEP
WHERE class_desc = N'DATABASE'

The output will be something like:

For a schema, the code snippet and output could be something like this:

SELECT 
'Schema' AS PropertyType
,SCH.name AS SchemaName
,SEP.name AS DescriptionType
,SEP.value AS DescriptionDefinition 
FROM sys.extended_properties SEP
 INNER JOIN sys.schemas SCH
 ON SEP.major_id = SCH.schema_id

WHERE SEP.class_desc = N'SCHEMA'

And the output is:

And for a table:

SELECT 
'Table' AS PropertyType
,SCH.name AS SchemaName
,TBL.name AS TableName
,SEP.name AS DescriptionType
,SEP.value AS DescriptionDefinition
FROM sys.tables TBL
 INNER JOIN sys.schemas SCH
 ON TBL.schema_id = SCH.schema_id 
 INNER JOIN sys.extended_properties SEP
 ON TBL.object_id = SEP.major_id 
WHERE SEP.class = 1 
 AND SEP.minor_id = 0
 AND (SEP.value <> '1' AND SEP.value <> 1)

And for a column:

SELECT 
'Table Column' AS PropertyType
,SCH.name AS SchemaName
,TBL.name AS TableName
,COL.name AS ColumnName
,SEP.name AS DescriptionType
,SEP.value AS DescriptionDefinition
FROM sys.extended_properties SEP
 INNER JOIN sys.columns COL
 ON SEP.major_id = COL.object_id 
 AND SEP.minor_id = COL.column_id 
 INNER JOIN sys.tables TBL
 ON SEP.major_id = TBL.object_id 
 INNER JOIN sys.schemas SCH
 ON TBL.schema_id = SCH.schema_id
WHERE SEP.class = 1
 AND (SEP.value <> '1' AND SEP.value <> 1)

You will have noticed that these four examples follow the four levels of the extended properties hierarchy:

Extract all extended properties from major database objects

OK, so now that the basic ideas are clear, here are (without the outputs), the code snippets to extract the extended properties from all the objects that we looked at in the first article. These will continue by looking at all the extended properties of tables:

Primary keys

SELECT 
'Primary Key' AS PropertyType
,SCH.name AS SchemaName
,TBL.name AS TableName
,SKC.name AS PrimaryKeyName
,SEP.name AS DescriptionType
,SEP.value AS DescriptionDefinition
,TBL.object_id AS TableObjectID
,SKC.object_id AS SubObjectID 
FROM sys.tables TBL
 INNER JOIN sys.schemas SCH
 ON TBL.schema_id = SCH.schema_id 
 INNER JOIN sys.extended_properties SEP
 INNER JOIN sys.key_constraints SKC
 ON SEP.major_id = SKC.object_id 
 ON TBL.object_id = SKC.parent_object_id 
WHERE SKC.type_desc = N'PRIMARY_KEY_CONSTRAINT'

Unique constraints

SELECT 
'Unique Constraint' AS PropertyType
,SCH.name AS SchemaName
,TBL.name AS TableName
,SKC.name AS UniqueConstraintName
,SEP.name AS DescriptionType
,SEP.value AS DescriptionDefinition 
FROM sys.tables TBL
 INNER JOIN sys.schemas SCH
 ON TBL.schema_id = SCH.schema_id 
 INNER JOIN sys.extended_properties SEP
 INNER JOIN sys.key_constraints SKC
 ON SEP.major_id = SKC.object_id 
 ON TBL.object_id = SKC.parent_object_id 
WHERE SKC.type_desc = N'UNIQUE_CONSTRAINT'
 AND (SEP.value <> '1' AND SEP.value <> 1)

Check constraints

SELECT 
'Check Constraint' AS PropertyType
,SCH.name AS SchemaName
,TBL.name AS TableName
,CHK.name AS CheckConstraintName
,SEP.name AS DescriptionType
,SEP.value AS DescriptionDefinition 
FROM sys.extended_properties SEP
 INNER JOIN sys.check_constraints CHK
 ON SEP.major_id = CHK.object_id 
 INNER JOIN sys.schemas SCH
 INNER JOIN sys.tables TBL
 ON SCH.schema_id = TBL.schema_id 
 ON CHK.parent_object_id = TBL.object_id

Indexes

SELECT 
'Table Index' AS PropertyType
,SCH.name AS SchemaName
,TBL.name AS TableName
,SIX.name AS IndexName
,SEP.name AS DescriptionType
,SEP.value AS DescriptionDefinition 
FROM sys.indexes SIX
 INNER JOIN sys.extended_properties SEP
 ON SIX.object_id = SEP.major_id 
 AND SIX.index_id = SEP.minor_id 
 INNER JOIN sys.tables TBL
 INNER JOIN sys.schemas SCH
 ON TBL.schema_id = SCH.schema_id 
 ON SIX.object_id = TBL.object_id 
WHERE SEP.class_desc = N'INDEX' 
 AND SIX.is_primary_key = 0

Foreign Keys

SELECT 
'Foreign Key' AS PropertyType
,SCH.name AS SchemaName
,TBL.name AS TableName
,SFK.name AS ForeignKeyName 
,SEP.name AS DescriptionType
,SEP.value AS DescriptionDefinition 
FROM sys.extended_properties SEP
 INNER JOIN sys.foreign_keys SFK
 ON SEP.major_id = SFK.object_id 
 INNER JOIN sys.tables TBL
 ON SFK.parent_object_id = TBL.object_id 
 INNER JOIN sys.schemas SCH
 ON TBL.schema_id = SCH.schema_id

Defaults

SELECT 
'Default' AS PropertyType
,SCH.name AS SchemaName
,TBL.name AS TableName
,SDC.name AS DefaultConstraintName
,SEP.name AS DescriptionType
,SEP.value AS DescriptionDefinition 
FROM sys.default_constraints SDC
 INNER JOIN sys.schemas SCH
 INNER JOIN sys.tables TBL
 ON SCH.schema_id = TBL.schema_id 
 ON SDC.parent_object_id = TBL.object_id 
 INNER JOIN sys.extended_properties SEP
 ON SDC.object_id = SEP.major_id

Views

SELECT 
'View' AS PropertyType
,SCH.name AS SchemaName
,VIW.name AS ViewName
,SEP.name AS DescriptionType
,SEP.value AS DescriptionDefinition
FROM sys.extended_properties SEP
 INNER JOIN sys.views VIW
 ON SEP.major_id = VIW.object_id 
 INNER JOIN sys.schemas SCH
 ON VIW.schema_id = SCH.schema_id 
WHERE SEP.minor_id = 0

View Columns

SELECT 
'View Column' AS PropertyType
,SCH.name AS SchemaName
,VIW.name AS ViewName
,COL.name AS ColumnName
,SEP.name AS DescriptionType
,SEP.value AS DescriptionDefinition
FROM sys.extended_properties SEP
 INNER JOIN sys.columns COL
 ON SEP.major_id = COL.object_id 
 AND SEP.minor_id = COL.column_id 
 INNER JOIN sys.views VIW
 ON SEP.major_id = VIW.object_id 
 INNER JOIN sys.schemas SCH
 ON VIW.schema_id = SCH.schema_id
WHERE SEP.class = 1
 AND (SEP.value <> '1' AND SEP.value <> 1)

View Indexes

SELECT 
'View Index' AS PropertyType
,SCH.name AS SchemaName
,VIW.name AS ViewName
,SIX.name AS IndexName
,SEP.name AS DescriptionType
,SEP.value AS DescriptionDefinition 
FROM sys.indexes SIX
 INNER JOIN sys.extended_properties SEP
 ON SIX.object_id = SEP.major_id 
 AND SIX.index_id = SEP.minor_id 
 INNER JOIN sys.views VIW
 INNER JOIN sys.schemas SCH
 ON VIW.schema_id = SCH.schema_id 
 ON SIX.object_id = VIW.object_id 
WHERE SEP.class_desc = N'INDEX' 

Functions

SELECT 
'Function' AS PropertyType
,SCH.name AS SchemaName
,SOB.name AS FunctionName
,SEP.name AS DescriptionType
,SEP.value AS DescriptionDefinition
FROM sys.extended_properties SEP
 INNER JOIN sys.objects SOB
 ON SEP.major_id = SOB.object_id 
 INNER JOIN sys.schemas SCH
 ON SOB.schema_id = SCH.schema_id 
WHERE SOB.type_desc LIKE N'%FUNCTION%' 
 AND SEP.minor_id = 0

Function parameters

SELECT 
'Function Parameter' AS PropertyType
,SCH.name AS SchemaName
,OBJ.name AS ProcedureName
,PRM.name AS ParameterName
,SEP.name AS DescriptionType
,SEP.value AS DescriptionDefinition 
FROM sys.extended_properties SEP
 INNER JOIN sys.objects OBJ
 ON SEP.major_id = OBJ.object_id 
 INNER JOIN sys.schemas SCH
 ON OBJ.schema_id = SCH.schema_id 
 INNER JOIN sys.parameters PRM
 ON SEP.major_id = PRM.object_id 
 AND SEP.minor_id = PRM.parameter_id 
WHERE SEP.class_desc = N'PARAMETER'
 AND OBJ.type IN ('FN', 'IF', 'TF') 

Procedures

SELECT 
'Procedure' AS PropertyType
,SCH.name AS SchemaName
,PRC.name AS ProcedureName
,SEP.name AS DescriptionType
,SEP.value AS DescriptionDefinition 
FROM sys.extended_properties SEP
 INNER JOIN sys.procedures PRC
 ON SEP.major_id = PRC.object_id 
 INNER JOIN sys.schemas SCH
 ON PRC.schema_id = SCH.schema_id 
WHERE SEP.minor_id = 0

Procedure parameters

SELECT 
'Procedure Parameter' AS PropertyType
,SCH.name AS SchemaName
,SPR.name AS ProcedureName
,sys.parameters.name AS ParameterName
,SEP.name AS DescriptionType
,SEP.value AS DescriptionDefinition

FROM sys.extended_properties SEP
 INNER JOIN sys.procedures SPR
 ON SEP.major_id = SPR.object_id 
 INNER JOIN sys.schemas SCH
 ON SPR.schema_id = SCH.schema_id 
 INNER JOIN sys.parameters 
 ON SEP.major_id = sys.parameters.object_id 
 AND SEP.minor_id = sys.parameters.parameter_id

WHERE SEP.class_desc = N'PARAMETER'

DDL Triggers

SELECT 
'DDL Trigger' AS PropertyType
,TRG.name AS DDLTriggerNAme
,SEP.name AS DescriptionType
,SEP.value AS DescriptionDefinition 
FROM sys.extended_properties SEP
 INNER JOIN sys.triggers TRG
 ON SEP.major_id = TRG.object_id
WHERE TRG.parent_class_desc = N'DATABASE'

Table triggers

SELECT 
'Table Trigger' AS PropertyType
,SCH.name AS SchemaName
,TBL.name AS TableName
,TRG.name AS TRIGGER_NAME
,SEP.name AS DescriptionType
,SEP.value AS DescriptionDefinition 
FROM sys.tables TBL
 INNER JOIN sys.triggers TRG
 ON TBL.object_id = TRG.parent_id 
 INNER JOIN sys.extended_properties SEP
 ON TRG.object_id = SEP.major_id 
 INNER JOIN sys.schemas SCH
 ON TBL.schema_id = SCH.schema_id

View triggers

SELECT 
'View Trigger' AS PropertyType
,SCH.name AS SchemaName
,VIW.name AS TableName
,TRG.name AS TRIGGER_NAME
,SEP.name AS DescriptionType
,SEP.value AS DescriptionDefinition
FROM sys.views VIW
 INNER JOIN sys.triggers TRG
 ON VIW.object_id = TRG.parent_id 
 INNER JOIN sys.extended_properties SEP
 ON TRG.object_id = SEP.major_id 
 INNER JOIN sys.schemas SCH
 ON VIW.schema_id = SCH.schema_id

Partition Functions

SELECT 
'Partition Function' AS PropertyType
,PFN.name AS PartitionFunctionName
,SEP.name AS DescriptionType
,SEP.value AS DescriptionDefinition 
FROM sys.extended_properties SEP
 INNER JOIN sys.partition_functions PFN
 ON SEP.major_id = PFN.function_id

Partition Schemes

SELECT 
'Partition Scheme' AS PropertyType
,PSC.name AS PartitionSchemeName
,SEP.name AS DescriptionType
,SEP.value AS DescriptionDefinition
FROM sys.extended_properties SEP
 INNER JOIN sys.partition_schemes PSC
 ON SEP.major_id = PSC.function_id

Filegroups

SELECT 
'Filegroup' AS PropertyType
,DSP.name AS DataSpace
,SEP.name AS DescriptionType
,SEP.value AS DescriptionDefinition
FROM sys.extended_properties SEP
 INNER JOIN sys.data_spaces DSP
 ON SEP.major_id = DSP.data_space_id
WHERE DSP.type_desc = 'ROWS_FILEGROUP'

Filegroups and logical file

SELECT 
'Filegroup' AS PropertyType
,DSP.name AS DataSpaceName
,DBF.name AS LogicalFileName
,SEP.name AS DescriptionType
,SEP.value AS DescriptionDefinition 
FROM sys.extended_properties SEP
 INNER JOIN sys.database_files DBF
 ON SEP.major_id = DBF.file_id 
 INNER JOIN sys.data_spaces DSP
 ON DBF.data_space_id = DSP.data_space_id 
WHERE SEP.class_desc = N'DATABASE_FILE'

Notes:

• You can, of course, narrow the search for extended properties by adding a WHERE clause (or extending the existing one) for any of the code snippets given above. In practice this often means restricting the output to selected extended property "names" (the description type), or restricting the types of table or view to those following a specific naming pattern.
• You need the rights to read the database metadata tables - but I am assuming that as a SQL Server developer, that you have these rights.

System metadata views or fn_listextendedproperty?

Now you have seen both how to extract extended properties from the system metadata views as well as from fn_listextendedproperty, you are probably wondering which one is best to use. The answer is simply - you choose.

However, these are some advantages to making the extra effort to use the system views to access your extended properties. These include:

• Accessing more precise and specific lists of extended properties is simple SQL with system views, and does not require learning the idiosyncrasies of a system function.
• Generating the scripts to add, update or drop extended properties cannot be done directly using fn_listextendedproperty on its own, and you will be using CROSS APPLY to other system tables to generate creation (and update and drop) scripts. So why not just go straight to the system tables?

Anyway, I am certainly not saying that fn_listextendedproperty is not extremely useful, just that by understanding how extended properties are handled by SQL Server you can choose how best you want to manage them.

A Word template to create documentation from extended properties

Once you can extract all the extended properties from your databases, the question is then - what are you going to do with them? I imagine that nine times out of ten the answer will be: create documentation in MS Word. The set of T-SQL snippets given above will help you extract the extended properties, but you will then have to copy and paste them into a document, and finalise the formatting. This can be a little laborious, so I am including with this article a small MS Word macro, wrapped in a Word Template file, which will extract and format extended properties for you.

To use it:

1. Download the attached file "DocumentationFromEXProp.dot".
2. Double-click to open and create a document based on the template.
3. Confirm that you want the macro to be enabled (if necessary).
4. The startup dialog will display:

   

5. Enter the server name and the database name, and if you are using SQL Server security, and not integrated security, click "SQL Server login" and enter your login name and password.
6. Select the objects you wish to document.
7. Click "Create Documentation".

Assuming all goes well, you should see something like this:

Notes:

• You will need to enable macros to run this small piece of code.
• The template is in Word 97-2003 compatibility mode, in the hope that this will allow it to run with most versions of MS Word.
• This macro is very simple, and you may extend it as you think fit. To do this open the template without launching the startup macro (shift-double-click on the template file), and press [ALT]-[F11] to display the VBA editor.
• The template is currently configured to use the ActiveX data objects 2.8 library, and assumes that this is installed on your workstation. If you are using another version of the data objects library you will need to add this to the template instead (Tools=>References from the VBA environment).
• The macro adds three styles (ObjectTitle, ObjectTableTitle, ObjectTableText) to the document - if you have your own stylesheets defined for your documentation, you can rename these in the document and in the VBA code.
• Unchecking "Apply table formatting" will not apply styles or formatting when creating documentation.
• To retry the document creation, either reopen the template, or run the macro "DocumentationFromEXProp.dot". How you do this will depend on which version of Word you are using. However [Alt]-[F8] seems to do the trick on all versions that I have used.

Just to explain my reasoning as to why I chose to use VBA to do this, and not write a compiled .NET application:

• It is extremely portable.
• Nearly every SQL Server developer or DBA I know uses MS Word.
• It is very easy to modify.
• One single file, no projects or complex dependencies.

You may disagree with this approach, in which case feel free to rewrite and wrap in .NET - and post on SQL Server Central!

So, I hope that you will have fun with extended properties - and that you found this description useful.

The next article will deal with basic aspects of industrial-strength database documentation using Extended Properties - in other words how to modify and delete hundreds of comments at a time for a range of objects.