Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 

Industrial-strength database documentation using Extended Properties

By Adam Aspin,

In the previous three articles we saw how to:

However, all this was done in a somewhat "artisanal" manner, and required a certain amount of manual labour to define properties. This could easily lead to both mistakes and oversights, apart from being labour-intensive in the extreme as each property was documented individually.

So let us now take this process to its next logical step, and script out the code required to document a selection of database objects, so that the following aims are achieved:

  • A core set of objects of a type which can be documented are found and extracted.
  • The core code to create extended properties for the objects is created, so that only the comments themselves need to be added.

All this will be done using T-SQL, and the database metadata tables.

I am not going to suggest documenting any and every object that can be documented using extended properties. Once again I am going to restrict the code to cover a subset of objects which I document in my databases, so the selection is both arbitrary and personal. You may, of course, extend the code which is attached to include any other objects that you wish to document which support extended properties.

This article will end with a script which goes through a reduced subset of database objects, and lists out :

  • Those objects which are not documented, with the necessary code to add the extended property for each object.
  • Any existing extended properties on this subset of database objects, with the necessary code to modify the extended property for each object.

Once again, I will group the extended properties by collections of object types, in the following order:

  • Schemas
  • Tables (including table columns, constraints, indexes and triggers)
  • Views (including view columns, indexes and triggers)
  • Stored Procedures (including parameters).
  • Functions (including parameters).
  • Remaining database objects (Partition functions, partition schemes, DDL triggers).

This approach is not essential to produce extended properties - I just find it useful to select and group the objects in a way which makes it easier to get a coherent overview of the objects to be documented.

So let's start simply by adding a "Description" extended property to all schemas in a database. The T-SQL to prepare the code for this is:

SELECT 
'EXEC sys.sp_addextendedproperty @level0type = N''SCHEMA'', @level0name = [' + SCH.name + '] ,@name = N''Description'', @value = N'''''
FROM sys.schemas SCH
WHERE principal_id = 1

When you execute this snippet, you should get:

EXEC sys.sp_addextendedproperty @level0type = N'SCHEMA', @level0name = [dbo] ,@name = N'Description', @value = N''
EXEC sys.sp_addextendedproperty @level0type = N'SCHEMA', @level0name = [Sales] ,@name = N'Description', @value = N''

So all you have to do is to copy and paste the resulting script into an SSMS query window, add the text for the extended property comment (between the last two single quotes, for @value = N'') and execute the script.

Notes:

  • I am suggesting here a simple extended property name of "Description". Feel free to alter this to suit your classification of extended properties.
  • You will need to remember to escape any single quotes in the comment to two single quotes - this is T-SQL code, so the normal rules apply.
  • You need the rights to read the database metadata tables - but I am assuming that as a SQL Server developer, this is a given.

So, with the principles established, let's look at scripting out the T-SQL to document whole swathes of database objects. For these snippets I will not show the output - you can do that by running them against your databases!

Tables

SELECT 
'EXEC sys.sp_addextendedproperty @level0type = N''SCHEMA'', @level0name = [' + SCH.name + '] ,@level1type = N''TABLE'', @level1name =[' + TBL.name + '], @name = N''Description'', @value = N'''''
FROM sys.schemas SCH
INNER JOIN sys.tables TBL
ON SCH.schema_id = TBL.schema_id

Table Columns

SELECT TOP (100) PERCENT
'EXEC sys.sp_addextendedproperty @level0type = N''SCHEMA'', @level0name = [' + SCH.name + '] ,@level1type = N''TABLE'', @level1name =[' + TBL.name + '] ,@level2type = N''COLUMN'', @level2name = [' + COL.name + '], @name = N''Description'', @value = N'''''
FROM sys.schemas SCH
INNER JOIN sys.tables TBL
ON SCH.schema_id = TBL.schema_id
INNER JOIN sys.columns COL
ON TBL.object_id = COL.object_id
ORDER BY SCH.name, TBL.name, COL.column_id

Unique Constraints

SELECT TOP (100) PERCENT 
'EXEC sys.sp_addextendedproperty @level0type = N''SCHEMA'', @level0name = [' + SCH.name + '] ,@level1type = N''TABLE'', @level1name =[' + TBL.name + '] ,@level2type = N''CONSTRAINT'', @level2name = [' + SKC.name + '], @name = N''Description'', @value = N'''''
FROM sys.schemas SCH
INNER JOIN sys.tables TBL
ON SCH.schema_id = TBL.schema_id
INNER JOIN sys.key_constraints SKC
ON TBL.object_id = SKC.parent_object_id
WHERE SKC.type_desc = N'UNIQUE_CONSTRAINT'
ORDER BY SCH.name, TBL.name

Primary Key Constraints

SELECT TOP (100) PERCENT 
'EXEC sys.sp_addextendedproperty @level0type = N''SCHEMA'', @level0name = [' + SCH.name + '] ,@level1type = N''TABLE'', @level1name =[' + TBL.name + '] ,@level2type = N''CONSTRAINT'', @level2name = [' + SKC.name + '], @name = N''Description'', @value = N'''''
FROM sys.schemas SCH
INNER JOIN sys.tables TBL
ON SCH.schema_id = TBL.schema_id
INNER JOIN sys.key_constraints SKC
ON TBL.object_id = SKC.parent_object_id
WHERE SKC.type_desc = N'PRIMARY_KEY_CONSTRAINT'
ORDER BY SCH.name, TBL.name

Default Constraints

SELECT TOP (100) PERCENT 
'EXEC sys.sp_addextendedproperty @level0type = N''SCHEMA'', @level0name = [' + SCH.name + '] ,@level1type = N''TABLE'', @level1name =[' + TBL.name + '] ,@level2type = N''CONSTRAINT'', @level2name = [' + SDC.name + '], @name = N''Description'', @value = N'''''
FROM sys.schemas SCH
INNER JOIN sys.tables TBL
ON SCH.schema_id = TBL.schema_id
INNER JOIN sys.default_constraints SDC
ON TBL.object_id = SDC.parent_object_id
ORDER BY SCH.name, TBL.name

Check constraints

SELECT TOP (100) PERCENT 
'EXEC sys.sp_addextendedproperty @level0type = N''SCHEMA'', @level0name = [' + SCH.name + '] ,@level1type = N''TABLE'', @level1name =[' + TBL.name + '] ,@level2type = N''CONSTRAINT'', @level2name = [' + CHK.name + '], @name = N''Description'', @value = N'''''
FROM sys.schemas SCH
INNER JOIN sys.tables TBL
ON SCH.schema_id = TBL.schema_id
INNER JOIN sys.check_constraints CHK
ON TBL.object_id = CHK.parent_object_id
ORDER BY SCH.name, TBL.name

Foreign key constraints

SELECT TOP (100) PERCENT 
'EXEC sys.sp_addextendedproperty @level0type = N''SCHEMA'', @level0name = [' + SCH.name + '] ,@level1type = N''TABLE'', @level1name =[' + TBL.name + '] ,@level2type = N''CONSTRAINT'', @level2name = [' + SFK.name + '], @name = N''Description'', @value = N'''''
FROM sys.schemas SCH
INNER JOIN sys.tables TBL
ON SCH.schema_id = TBL.schema_id
INNER JOIN sys.foreign_keys SFK
ON TBL.object_id = SFK.parent_object_id
ORDER BY SCH.name, TBL.name

Table Indexes

SELECT 
'EXEC sys.sp_addextendedproperty @level0type = N''SCHEMA'', @level0name = [' + SCH.name + '] ,@level1type = N''TABLE'', @level1name =[' + TBL.name + '] ,@level2type = N''INDEX'', @level2name = [' + SIX.name + '], @name = N''Description'', @value = N'''''
FROM sys.indexes SIX
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 SIX.is_primary_key = 0
AND SIX.is_unique = 0

Table triggers

SELECT TOP (100) PERCENT 
'EXEC sys.sp_addextendedproperty @level0type = N''SCHEMA'', @level0name = [' + SCH.name + '] ,@level1type = N''TABLE'', @level1name =[' + TBL.name + '] ,@level2type = N''TRIGGER'', @level2name = [' + TRG.name + '], @name = N''Description'', @value = N'''''
FROM sys.schemas SCH
INNER JOIN sys.tables TBL
ON SCH.schema_id = TBL.schema_id
INNER JOIN sys.triggers TRG
ON TBL.object_id = TRG.parent_id
ORDER BY SCH.name, TBL.name

Views

SELECT 
'EXEC sys.sp_addextendedproperty @level0type = N''SCHEMA'', @level0name = [' + SCH.name + '] ,@level1type = N''VIEW'', @level1name =[' + VIW.name + '], @name = N''Description'', @value = N'''''
FROM sys.schemas SCH
INNER JOIN sys.views VIW
ON SCH.schema_id = VIW.schema_id

View Columns

SELECT TOP (100) PERCENT
'EXEC sys.sp_addextendedproperty @level0type = N''SCHEMA'', @level0name = [' + SCH.name + '] ,@level1type = N''VIEW'', @level1name =[' + VIW.name + '] ,@level2type = N''COLUMN'', @level2name = [' + COL.name + '], @name = N''Description'', @value = N'''''
FROM sys.schemas SCH
INNER JOIN sys.views VIW
ON SCH.schema_id = VIW.schema_id
INNER JOIN sys.columns COL
ON VIW.object_id = COL.object_id
ORDER BY SCH.name, VIW.name, COL.column_id

View indexes

SELECT 
'EXEC sys.sp_addextendedproperty @level0type = N''SCHEMA'', @level0name = [' + SCH.name + '] ,@level1type = N''VIEW'', @level1name =[' + VIW.name + '] ,@level2type = N''INDEX'', @level2name = [' + SIX.name + '], @name = N''Description'', @value = N'''''
FROM sys.indexes SIX
INNER JOIN sys.views VIW
INNER JOIN sys.schemas SCH
ON VIW.schema_id = SCH.schema_id
ON SIX.object_id = VIW.object_id

View triggers

SELECT TOP (100) PERCENT 
'EXEC sys.sp_addextendedproperty @level0type = N''SCHEMA'', @level0name = [' + SCH.name + '] ,@level1type = N''VIEW'', @level1name =[' + VIW.name + '] ,@level2type = N''TRIGGER'', @level2name = [' + TRG.name + '], @name = N''Description'', @value = N'''''
FROM sys.schemas SCH
INNER JOIN sys.views VIW
ON SCH.schema_id = VIW.schema_id
INNER JOIN sys.triggers TRG
ON VIW.object_id = TRG.parent_id
ORDER BY SCH.name, VIW.name

Stored Procedures

SELECT 
'EXEC sys.sp_addextendedproperty @level0type = N''SCHEMA'', @level0name = [' + SCH.name + '] ,@level1type = N''PROCEDURE'', @level1name =[' + PRC.name + '], @name = N''Description'', @value = N'''''
FROM sys.schemas AS SCH
INNER JOIN sys.procedures PRC
ON SCH.schema_id = PRC.schema_id

Stored Procedure Parameters

SELECT TOP (100) PERCENT 
'EXEC sys.sp_addextendedproperty @level0type = N''SCHEMA'', @level0name = [' + SCH.name + '] ,@level1type = N''PROCEDURE'', @level1name =[' + PRC.name + '] ,@level2type = N''PARAMETER'', @level2name = [' + PRM.name + '], @name = N''Description'', @value = N'''''
FROM sys.schemas AS SCH
INNER JOIN sys.procedures PRC
ON SCH.schema_id = PRC.schema_id
INNER JOIN sys.parameters PRM
ON PRC.object_id = PRM.object_id
ORDER BY PRM.parameter_id

Functions

SELECT TOP (100) PERCENT 
'EXEC sys.sp_addextendedproperty @level0type = N''SCHEMA'', @level0name = [' + SCH.name + '] ,@level1type = N''FUNCTION'', @level1name =[' + OBJ.name + '], @name = N''Description'', @value = N'''''
FROM sys.schemas AS SCH
INNER JOIN sys.objects OBJ
ON SCH.schema_id = OBJ.schema_id
WHERE OBJ.is_ms_shipped = 0
AND OBJ.type_desc LIKE N'%FUNCTION%'

Function Parameters

SELECT TOP (100) PERCENT 
'EXEC sys.sp_addextendedproperty @level0type = N''SCHEMA'', @level0name = [' + SCH.name + '] ,@level1type = N''FUNCTION'', @level1name =[' + OBJ.name + '] ,@level2type = N''PARAMETER'', @level2name = [' + PRM.name + '], @name = N''Description'', @value = N'''''
FROM sys.schemas AS SCH
INNER JOIN sys.objects OBJ
ON SCH.schema_id = OBJ.schema_id
INNER JOIN sys.parameters PRM
ON OBJ.object_id = PRM.object_id
WHERE OBJ.is_ms_shipped = 0
AND OBJ.type_desc LIKE N'%FUNCTION%'
AND PRM.name IS NOT NULL
AND LEN(PRM.name) > 0

DDL Triggers

SELECT TOP (100) PERCENT 
'EXEC sys.sp_addextendedproperty @level0type = N''TRIGGER'', @level0name = [' + TRG.name + '] ,@name = N''Description'', @value = N'''''
FROM sys.triggers TRG
WHERE TRG.parent_class_desc = N'DATABASE'

Partition functions

SELECT
'EXEC sys.sp_addextendedproperty @level0type = N''PARTITION FUNCTION'', @level0name = [' + PFN.name + '] ,@name = N''Description'', @value = N'''''
FROM sys.partition_functions PFN

Partition Schemes

SELECT
'EXEC sys.sp_addextendedproperty @level0type = N''PARTITION SCHEME'', @level0name = [' + PSC.name + '] ,@name = N''Description'', @value = N'''''
FROM sys.partition_schemes PSC

Notes:

  • In these examples, I have chosen to use the "subset" metadata tables (such as sys.tables and sys.views) rather than go for the high-level sys.objects table. This is a personal choice, and you are free to rewrite the code snippets to use sys.objects (or other approaches, such as the backward-compatibility views sys.sysobjects etc). The reason is that I find it simpler to use a table called, for example, sys.tables, rather than have to remember the WHERE clause for tables in sys.objects. Still - you choose!
  • You can always produce subsets of database object types by adding (or extending) the WHERE clause in the metadata selection code. For instance, if you have a naming convention where all tables concerning your customers start with "cst", the table script could be:

    SELECT 
    'EXEC sys.sp_addextendedproperty @level0type = N''SCHEMA'', @level0name = [' + SCH.name + '] ,@level1type = N''TABLE'', @level1name =[' + TBL.name + '], @name = N''Description'', @value = N'''''
    FROM sys.schemas SCH
    INNER JOIN sys.tables TBL
    ON SCH.schema_id = TBL.schema_id
    WHERE TBL.name = 'cst%'

And so on!

Real world database documentation

The code snippets given above are great when you are faced with a database where no extended properties have been added - so they are ideal for a first set of properties. However, in the real world, things are rarely this simple. Often you have to document a set of database objects where partial documentation exists. So, to finish, let's look at a process which will:

  • Use the metadata tables to identify all the objects which exist, and prepare the "sp_addextendedproperty" stored procedure, as shown above.
  • Detect which objects already have extended properties for the classification (@value) you have chosen - and prepare them using the "sp_updateextendedproperty" stored procedure. This will avoid error messages, as well as letting you see and modify the existing extended properties for an object.
  • Allow you to select which object types to create extended properties for. After all, you might only want to restrain your documentary zeal, and only comment on, say, tables and views, not their other documentable attributes such as columns, indexes etc.

I have not included all the objects handled in the code snippets given above I this script, but stuck to a core of:

  • Tables
  • Views
  • Stored Procedures
  • Functions
  • Table Columns
  • View Columns
  • Indexes
  • Constraints
Once again, the code is easily extended (based on the snippets already given in this series of articles plus your own additions), should you wish to add further objects from amongst those which can be documented.
-----------------------------------------------------------------------
-- User-defined variables
-- Set these to 1 or 0 to indicate whether you wish to output the elements
-----------------------------------------------------------------------
DECLARE @OutputTables BIT
DECLARE @OutputViews BIT
DECLARE @OutputProcedures BIT
DECLARE @OutputFunctions BIT
DECLARE @OutputTableColumns BIT
DECLARE @OutputViewColumns BIT
DECLARE @OutputIndexes BIT
DECLARE @OutputConstraints BIT
SET @OutputTables = 1
SET @OutputViews = 1
SET @OutputProcedures = 1
SET @OutputFunctions = 1
SET @OutputTableColumns = 1
SET @OutputViewColumns = 1
SET @OutputIndexes = 1
SET @OutputConstraints = 1
-----------------------------------------------------------------------
-- Process variables
-----------------------------------------------------------------------
DECLARE @ObjectType NVARCHAR(50) = ''
DECLARE @SecondaryObjectType NVARCHAR(50) = ''
-----------------------------------------------------------------------
-- Create the temporary table to hold the scripts and metadata
-----------------------------------------------------------------------
IF OBJECT_ID('tempdb..#ModifyCreate') IS NOT NULL
DROP TABLE tempdb..#ModifyCreate;
CREATE TABLE #ModifyCreate (PrimaryObjectType VARCHAR(25), SecondaryObjectType VARCHAR(25), SchemaName NVARCHAR(128), PrimaryObjectName NVARCHAR(128), SecondaryObjectName NVARCHAR(128), Classification NVARCHAR(128), DescriptionText NVARCHAR(1700), SQLText NVARCHAR(2500))
-----------------------------------------------------------------------
-- Output scripts for Table objects
-----------------------------------------------------------------------
IF @OutputTables = 1
BEGIN
SET @ObjectType = 'Table'
INSERT INTO #ModifyCreate
SELECT
@ObjectType AS PrimaryObjectType
,CAST(NULL AS VARCHAR(25)) AS SecondaryObjectType
,SCH.name AS SchemaName
,CAST(TBL.name AS NVARCHAR(128)) AS PrimaryObjectName
,CAST(NULL AS VARCHAR(25)) AS SecondaryObjectName
,EX.DescriptionType AS Classification
,CAST(EX.DescriptionDefinition AS NVARCHAR(128)) AS DescriptionText
,CASE
WHEN EX.DescriptionType IS NULL THEN 'sp_addextendedproperty @level0type = N''Schema'', @level0name = [' + SCH.name + '], @level1type = ''' + @ObjectType + ''', @level1name = [' + TBL.name + '], @name = N'''', @value = '''';'
ELSE 'sp_updateextendedproperty @level0type = N''Schema'', @level0name = [' + SCH.name + '], @level1type = ''' + @ObjectType + ''', @level1name = [' + TBL.name + '], @name = N''' + EX.DescriptionType + ''', @value = ''' + CAST(EX.DescriptionDefinition AS NVARCHAR(1700)) + ''';'
END AS SQLText
FROM sys.tables TBL
INNER JOIN sys.schemas SCH
ON TBL.schema_id = SCH.schema_id
LEFT OUTER JOIN
(
SELECT DISTINCT
SEP.name AS DescriptionType
,SEP.value AS DescriptionDefinition
,SEP.major_id
FROM sys.extended_properties SEP
WHERE SEP.class = 1
AND SEP.minor_id = 0
AND (SEP.value <> '1' AND SEP.value <> 1)
) EX
ON TBL.object_id = EX.major_id
END -- Tables
-----------------------------------------------------------------------
-- Output scripts for View objects
-----------------------------------------------------------------------
IF @OutputViews = 1
BEGIN
SET @ObjectType = 'View'
INSERT INTO #ModifyCreate
SELECT
@ObjectType AS PrimaryObjectType
,CAST(NULL AS VARCHAR(25)) AS SecondaryObjectType
,SCH.name AS SchemaName
,CAST(VIW.name AS NVARCHAR(128)) AS PrimaryObjectName
,CAST(NULL AS VARCHAR(25)) AS SecondaryObjectName
,EX.DescriptionType AS Classification
,CAST(EX.DescriptionDefinition AS NVARCHAR(128)) AS DescriptionText
,CASE
WHEN EX.DescriptionType IS NULL THEN 'sp_addextendedproperty @level0type = N''Schema'', @level0name = [' + SCH.name + '], @level1type = ''' + @ObjectType + ''', @level1name = [' + VIW.name + '], @name = N'''', @value = '''';'
ELSE 'sp_updateextendedproperty @level0type = N''Schema'', @level0name = [' + SCH.name + '], @level1type = ''' + @ObjectType + ''', @level1name = [' + VIW.name + '], @name = N''' + EX.DescriptionType + ''', @value = ''' + CAST(EX.DescriptionDefinition AS NVARCHAR(1700)) + ''';'
END AS SQLText
FROM sys.views VIW
INNER JOIN sys.schemas SCH
ON VIW.schema_id = SCH.schema_id
LEFT OUTER JOIN
(
SELECT DISTINCT
SEP.name AS DescriptionType
,SEP.value AS DescriptionDefinition
,SEP.major_id
FROM sys.extended_properties SEP
WHERE SEP.class = 1
AND SEP.minor_id = 0
AND (SEP.value <> '1' AND SEP.value <> 1)
) EX
ON VIW.object_id = EX.major_id
END -- Views
-----------------------------------------------------------------------
-- Output scripts for Stored procedure objects
-----------------------------------------------------------------------
IF @OutputProcedures = 1
BEGIN
SET @ObjectType = 'Procedure'
INSERT INTO #ModifyCreate
SELECT
@ObjectType AS PrimaryObjectType
,CAST(NULL AS VARCHAR(25)) AS SecondaryObjectType
,SCH.name AS SchemaName
,CAST(PRC.name AS NVARCHAR(128)) AS PrimaryObjectName
,CAST(NULL AS VARCHAR(25)) AS SecondaryObjectName
,EX.DescriptionType AS Classification
,CAST(EX.DescriptionDefinition AS NVARCHAR(128)) AS DescriptionText
,CASE
WHEN EX.DescriptionType IS NULL THEN 'sp_addextendedproperty @level0type = N''Schema'', @level0name = [' + SCH.name + '], @level1type = ''' + @ObjectType + ''', @level1name = [' + PRC.name + '], @name = N'''', @value = '''';'
ELSE 'sp_updateextendedproperty @level0type = N''Schema'', @level0name = [' + SCH.name + '], @level1type = ''' + @ObjectType + ''', @level1name = [' + PRC.name + '], @name = N''' + EX.DescriptionType + ''', @value = ''' + CAST(EX.DescriptionDefinition AS NVARCHAR(1700)) + ''';'
END AS SQLText
FROM sys.procedures PRC
INNER JOIN sys.schemas SCH
ON PRC.schema_id = SCH.schema_id
LEFT OUTER JOIN
(
SELECT DISTINCT
SEP.name AS DescriptionType
,SEP.value AS DescriptionDefinition
,SEP.major_id
FROM sys.extended_properties SEP
WHERE SEP.class = 1
AND SEP.minor_id = 0
AND (SEP.value <> '1' AND SEP.value <> 1)
) EX
ON PRC.object_id = EX.major_id
END -- Procedures
-----------------------------------------------------------------------
-- Output scripts for Function objects
-----------------------------------------------------------------------
IF @OutputFunctions = 1
BEGIN
SET @ObjectType = 'Function'
INSERT INTO #ModifyCreate
SELECT
@ObjectType AS PrimaryObjectType
,CAST(NULL AS VARCHAR(25)) AS SecondaryObjectType
,SCH.name AS SchemaName
,CAST(OBJ.name AS NVARCHAR(128)) AS PrimaryObjectName
,CAST(NULL AS VARCHAR(25)) AS SecondaryObjectName
,EX.DescriptionType AS Classification
,CAST(EX.DescriptionDefinition AS NVARCHAR(128)) AS DescriptionText
,CASE
WHEN EX.DescriptionType IS NULL THEN 'sp_addextendedproperty @level0type = N''Schema'', @level0name = [' + SCH.name + '], @level1type = ''' + @ObjectType + ''', @level1name = [' + OBJ.name + '], @name = N'''', @value = '''';'
ELSE 'sp_updateextendedproperty @level0type = N''Schema'', @level0name = [' + SCH.name + '], @level1type = ''' + @ObjectType + ''', @level1name = [' + OBJ.name + '], @name = N''' + EX.DescriptionType + ''', @value = ''' + CAST(EX.DescriptionDefinition AS NVARCHAR(1700)) + ''';'
END AS SQLText
FROM sys.objects OBJ
INNER JOIN sys.schemas SCH
ON OBJ.schema_id = SCH.schema_id
LEFT OUTER JOIN
(
SELECT DISTINCT
SEP.name AS DescriptionType
,SEP.value AS DescriptionDefinition
,SEP.major_id
FROM sys.extended_properties SEP
WHERE SEP.class = 1
AND SEP.minor_id = 0
AND (SEP.value <> '1' AND SEP.value <> 1)
) EX
ON OBJ.object_id = EX.major_id

WHERE OBJ.type_desc = 'SQL_SCALAR_FUNCTION'
END -- Functions
-----------------------------------------------------------------------
-- Output scripts for Table Column objects
-----------------------------------------------------------------------
IF @OutputTableColumns = 1
BEGIN
SET @ObjectType = 'Table'
SET @SecondaryObjectType = 'Column'
INSERT INTO #ModifyCreate
SELECT
@ObjectType AS PrimaryObjectType
,@SecondaryObjectType AS SecondaryObjectType
,SCH.name AS SchemaName
,CAST(TBL.name AS NVARCHAR(128)) AS PrimaryObjectName
,CAST(COL.name AS NVARCHAR(128)) AS SecondaryObjectName
,EX.DescriptionType AS Classification
,CAST(EX.DescriptionDefinition AS NVARCHAR(128)) AS DescriptionText
,CASE
WHEN EX.DescriptionType IS NULL THEN 'sp_addextendedproperty @level0type = N''Schema'', @level0name = [' + SCH.name + '], @level1type = ''' + @ObjectType + ''', @level1name = [' + TBL.name + '], @level2type = N''' + ISNULL(@SecondaryObjectType,'') + ''', @level2name = [' + ISNULL(COL.name,'') + '], @name = N'''', @value = '''';'
ELSE 'sp_updateextendedproperty @level0type = N''Schema'', @level0name = [' + SCH.name + '], @level1type = ''' + @ObjectType + ''', @level1name = [' + TBL.name + '], @level2type = N''' + ISNULL(@SecondaryObjectType,'') + ''', @level2name = [' + ISNULL(COL.name,'') + '], @name = N''' + EX.DescriptionType + ''', @value = ''' + CAST(EX.DescriptionDefinition AS NVARCHAR(1700)) + ''';'
END AS SQLText
FROM sys.tables TBL
INNER JOIN sys.schemas SCH
ON TBL.schema_id = SCH.schema_id
INNER JOIN sys.columns COL
ON COL.object_id = TBL.object_id
LEFT OUTER JOIN
(
SELECT DISTINCT
SEP.name AS DescriptionType
,SEP.value AS DescriptionDefinition
,SEP.major_id
,SEP.minor_id
FROM sys.extended_properties SEP
WHERE SEP.class = 1
) EX
ON TBL.object_id = EX.major_id
AND COL.column_id = EX.minor_id
END -- Table Columns
-----------------------------------------------------------------------
-- Output scripts for View Column objects
-----------------------------------------------------------------------
IF @OutputViewColumns = 1
BEGIN
SET @ObjectType = 'View'
SET @SecondaryObjectType = 'Column'
INSERT INTO #ModifyCreate
SELECT
@ObjectType AS PrimaryObjectType
,@SecondaryObjectType AS SecondaryObjectType
,SCH.name AS SchemaName
,CAST(VIW.name AS NVARCHAR(128)) AS PrimaryObjectName
,CAST(COL.name AS NVARCHAR(128)) AS SecondaryObjectName
,EX.DescriptionType AS Classification
,CAST(EX.DescriptionDefinition AS NVARCHAR(128)) AS DescriptionText
,CASE
WHEN EX.DescriptionType IS NULL THEN 'sp_addextendedproperty @level0type = N''Schema'', @level0name = [' + SCH.name + '], @level1type = ''' + @ObjectType + ''', @level1name = [' + VIW.name + '], @level2type = N''' + ISNULL(@SecondaryObjectType,'') + ''', @level2name = [' + ISNULL(COL.name,'') + '], @name = N'''', @value = '''';'
ELSE 'sp_updateextendedproperty @level0type = N''Schema'', @level0name = [' + SCH.name + '], @level1type = ''' + @ObjectType + ''', @level1name = [' + VIW.name + '], @level2type = N''' + ISNULL(@SecondaryObjectType,'') + ''', @level2name = [' + ISNULL(COL.name,'') + '], @name = N''' + EX.DescriptionType + ''', @value = ''' + CAST(EX.DescriptionDefinition AS NVARCHAR(1700)) + ''';'
END AS SQLText
FROM sys.views VIW
INNER JOIN sys.schemas SCH
ON VIW.schema_id = SCH.schema_id
INNER JOIN sys.columns COL
ON COL.object_id = VIW.object_id
LEFT OUTER JOIN
(
SELECT DISTINCT
SEP.name AS DescriptionType
,SEP.value AS DescriptionDefinition
,SEP.major_id
,SEP.minor_id
FROM sys.extended_properties SEP
WHERE SEP.class = 1
) EX
ON VIW.object_id = EX.major_id
AND COL.column_id = EX.minor_id
END -- View Columns
-----------------------------------------------------------------------
-- Output scripts for Indexes
-----------------------------------------------------------------------
IF @OutputIndexes = 1
BEGIN
SET @SecondaryObjectType = 'Index'
INSERT INTO #ModifyCreate
SELECT
CASE
WHEN OBJ.type_desc = N'USER_TABLE' THEN 'Table'
WHEN OBJ.type_desc = N'VIEW' THEN 'View'
END AS PrimaryObjectType
,@SecondaryObjectType AS SecondaryObjectType
,SCH.name AS SchemaName
,CAST(OBJ.name AS NVARCHAR(128)) AS PrimaryObjectName
,CAST(SIX.name AS NVARCHAR(128)) AS SecondaryObjectName
,EX.DescriptionType AS Classification
,CAST(EX.DescriptionDefinition AS NVARCHAR(128)) AS DescriptionText
,CASE
WHEN EX.DescriptionType IS NULL THEN 'sp_addextendedproperty @level0type = N''Schema'', @level0name = [' + SCH.name + '], @level1type = ''' + CASE WHEN OBJ.type_desc = N'USER_TABLE' THEN 'Table' WHEN OBJ.type_desc = N'VIEW' THEN 'View' END + ''', @level1name = [' + OBJ.name + '], @level2type = N''' + ISNULL(@SecondaryObjectType,'') + ''', @level2name = [' + ISNULL(SIX.name,'') + '], @name = N'''', @value = '''';'
ELSE 'sp_updateextendedproperty @level0type = N''Schema'', @level0name = [' + SCH.name + '], @level1type = ''' + CASE WHEN OBJ.type_desc = N'USER_TABLE' THEN 'Table' WHEN OBJ.type_desc = N'VIEW' THEN 'View' END + ''', @level1name = [' + OBJ.name + '], @level2type = N''' + ISNULL(@SecondaryObjectType,'') + ''', @level2name = [' + ISNULL(SIX.name,'') + '], @name = N''' + EX.DescriptionType + ''', @value = ''' + CAST(EX.DescriptionDefinition AS NVARCHAR(1700)) + ''';'
END AS SQLText
FROM sys.objects OBJ
INNER JOIN sys.schemas SCH
ON OBJ.schema_id = SCH.schema_id
INNER JOIN sys.indexes SIX
ON OBJ.object_id = SIX.object_id
LEFT OUTER JOIN
(
SELECT DISTINCT
SEP.name AS DescriptionType
,SEP.value AS DescriptionDefinition
,SEP.major_id
,SEP.minor_id
FROM sys.extended_properties SEP
WHERE SEP.class_desc = N'INDEX'
) EX
ON OBJ.object_id = EX.major_id
AND SIX.index_id = EX.minor_id
WHERE OBJ.type_desc IN ('USER_TABLE','VIEW')
AND SIX.is_primary_key = 0
AND SIX.is_unique = 0
AND SIX.is_unique_constraint = 0
AND SIX.name IS NOT NULL
END -- Indexes
-----------------------------------------------------------------------
-- Output scripts for Constraints
-----------------------------------------------------------------------
IF @OutputConstraints = 1
BEGIN
SET @ObjectType = 'Table'
SET @SecondaryObjectType = 'Constraint'
INSERT INTO #ModifyCreate
SELECT
CASE
WHEN OBJ.type_desc = N'USER_TABLE' THEN 'Table'
WHEN OBJ.type_desc = N'VIEW' THEN 'View'
END AS PrimaryObjectType
,@SecondaryObjectType AS SecondaryObjectType
,SCH.name AS SchemaName
,CAST(OBJ.name AS NVARCHAR(128)) AS PrimaryObjectName
,CAST(SIX.name AS NVARCHAR(128)) AS SecondaryObjectName
,EX.DescriptionType AS Classification
,CAST(EX.DescriptionDefinition AS NVARCHAR(128)) AS DescriptionText
,CASE
WHEN EX.DescriptionType IS NULL THEN 'sp_addextendedproperty @level0type = N''Schema'', @level0name = [' + SCH.name + '], @level1type = ''' + @ObjectType + ''', @level1name = [' + OBJ.name + '], @level2type = N''' + ISNULL(@SecondaryObjectType,'') + ''', @level2name = [' + ISNULL(SIX.name,'') + '], @name = N'''', @value = '''';'
ELSE 'sp_updateextendedproperty @level0type = N''Schema'', @level0name = [' + SCH.name + '], @level1type = ''' + @ObjectType + ''', @level1name = [' + OBJ.name + '], @level2type = N''' + ISNULL(@SecondaryObjectType,'') + ''', @level2name = [' + ISNULL(SIX.name,'') + '], @name = N''' + EX.DescriptionType + ''', @value = ''' + CAST(EX.DescriptionDefinition AS NVARCHAR(1700)) + ''';'
END AS SQLText
FROM sys.objects OBJ
INNER JOIN sys.schemas SCH
ON OBJ.schema_id = SCH.schema_id
INNER JOIN sys.indexes SIX
ON OBJ.object_id = SIX.object_id
LEFT OUTER JOIN
(
SELECT DISTINCT
SEP.name AS DescriptionType
,SEP.value AS DescriptionDefinition
,SEP.major_id
,SEP.minor_id
FROM sys.extended_properties SEP
WHERE SEP.class_desc = N'INDEX'
) EX
ON OBJ.object_id = EX.major_id
AND SIX.index_id = EX.minor_id
WHERE OBJ.type_desc = 'USER_TABLE'
AND (SIX.is_primary_key = 1 OR SIX.is_unique = 1 OR SIX.is_unique_constraint = 1)
END -- Constraints
-----------------------------------------------------------------------
-- Output scripts
-----------------------------------------------------------------------
SELECT * FROM #ModifyCreate
-----------------------------------------------------------------------

Notes:

  • This script will work with SQL Server 2005 and SQL Server 2008 - if you are working with SQL Server 2008 then you could replace the list parsing with a table-valued parameter.
  • As the system metadata tables use the sql_variant data type, you have to cast this as NVARCHAR .
  • The code can be tweaked fairly easily to isolate extended properties by extended property name, simply through extending the WHERE clause of the subquery on the sys.extended_properties view to specify a "name = ''" clause.

As it is given, the script only outputs the SQL to add or update extended properties. However, by tweaking the columns which you select from the temporary table "#ModifyCreate", you can script out a complete overview of the objects which will be documented.

So, by extending the SELECT clause to:

SELECT PrimaryObjectType, SecondaryObjectType, SchemaName, PrimaryObjectName, SecondaryObjectName, Classification, DescriptionText, SQLText
FROM #ModifyCreate

You could produce something like this:

So that's all for the moment, and I hope that you can use this code when documenting your databases.

Resources:

PrepareDocumentableObjects.sql | GetCurrentAndMissingExtendedProperties.sql
Total article views: 7651 | Views in the last 30 days: 4
 
Related Articles
BLOG

We Interrupt Your Regular SQL Blogging For This Announcement…

EXEC sys.sp_addextendedproperty     @name = N'IsWifePregnant',     @value = N'True',   ...

FORUM

CTE and inner join

CTE INNER JOIN

FORUM

Coalesce on inner queries

Coalesce on inner queries

FORUM

Inner Join

More than 3 tables Inner Join

FORUM

Error Rolling back inner transaction

Error Rolling back inner transaction

Tags
 
Contribute

Join the most active online SQL Server Community

SQL knowledge, delivered daily, free:

Email address:  

You make SSC a better place

As a member of SQLServerCentral, you get free access to loads of fresh content: thousands of articles and SQL scripts, a library of free eBooks, a weekly database news roundup, a great Q & A platform… And it’s our huge, buzzing community of SQL Server Professionals that makes it such a success.

Join us!

Steve Jones
Editor, SQLServerCentral.com

Already a member? Jump in:

Email address:   Password:   Remember me: Forgotten your password?
Steve Jones