SQLServerCentral Article

Industrial-strength database documentation using Extended Properties

,

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

Rate

★ ★ ★ ★ ★ ★ ★ ★ ★ ★

4.8 (25)

You rated this post out of 5. Change rating

Share

Categories

Join the discussion and add your comment

Share

Rate

★ ★ ★ ★ ★ ★ ★ ★ ★ ★

4.8 (25)

You rated this post out of 5. Change rating

Related content

External Article

SQL Server Metadata Functions: The Basics

To be able to make full use of the system catalog to find out more about a database, you need to be familiar with the metadata functions. They save a great deal of time and typing when querying the metadata. Once you get the hang of these functions, the system catalog suddenly seems simple to use, as Robert Sheldon demonstrates in this article.

2016-03-28

6,652 reads