{"id":190,"date":"2006-12-01T00:00:00","date_gmt":"2006-12-01T00:00:00","guid":{"rendered":"https:\/\/test.simple-talk.com\/uncategorized\/towards-the-self-documenting-sql-server-database\/"},"modified":"2021-05-11T15:57:47","modified_gmt":"2021-05-11T15:57:47","slug":"towards-the-self-documenting-sql-server-database","status":"publish","type":"post","link":"https:\/\/www.red-gate.com\/simple-talk\/databases\/sql-server\/tools-sql-server\/towards-the-self-documenting-sql-server-database\/","title":{"rendered":"Towards the Self-Documenting SQL Server Database"},"content":{"rendered":"

Last updated on 7th October 2016<\/p>\n

\n

Extended Properties in SQL Server<\/h2>\n

With SQL Server, it is strange how some of the most radical improvements that have been introduced over the years have been slipped in almost unnoticed. Conversely, the features that were plastered all over the marketing brochures are sometimes the ones that turn out to be dead ends.<\/p>\n

A feature that fits into the former category is the extended properties<\/strong> of databases. Introduced quietly with SQL Server 2000, one could have easily missed them but they have proved, after all, to be of great value to the developer. Extended properties are the route to creating self-documenting databases. I use the term ‘self-documenting’ in the sense that one can attach the documentation directly to the database itself and its objects, rather than create a separate document. Basically, you use the extended properties of data objects to apply your own properties to the metadata.<\/p>\n

Using extended properties<\/h3>\n

The classic example is to use extended properties of an object to append a description of that object, be it a trigger, stored procedure, function, table, view, parameter, index, constraint or column. One can also use extended properties to document details such as the date of revision, author, input mask, caption, history, rendering-style, and so on.<\/p>\n

One of the classic general tricks that programmers have used in the past to add documentation to source code is to structure the comments of source code by inserting predefined markers to indicate the category of information (revision date, for example) to any application that generates the documentation (such as Javadocs). This can’t be done in SQL Server as source is only maintained in the database for certain objects such as procedures and functions. Since the introduction of extended properties, such tricks would be unnecessary anyway.<\/p>\n

The advantage of using the extended properties is that the documentation, notes, and so on stay with the databases and can be used as a “live documentation” mechanism. They are backed up with the database, and scripted out with the build scripts.<\/p>\n

Despite their obvious utility, Microsoft has treated the feature with a curious lack of enthusiasm. There is the smell of ‘wet paint’ about the design. Extended properties allow you to document your database objects but it has been left to third-party utilities such as SQL Doc<\/a> and DBDesc<\/a> to exploit the use of these properties for generating the full documentation of the database from the database itself.<\/p>\n

A consequence of Microsoft’s indifference to extended properties is that they forgot to include them in the replication synchronisation process. You have to do it manually (a tool such as SQL Compare<\/a> will synchronise them properly). Also, they neglected to provide an Information_Schema<\/strong> view of the extended properties, which would have made to make it easier to access them from SQL.<\/p>\n

Another difficulty is that some third-party software vendors have used the extended properties for other purposes, such as storing parameters for entity-relationship diagrams. This makes it difficult for utilities that extract the documentation as there is no standard property name other than MS_Description<\/strong>.<\/p>\n

Creating extended properties via code<\/h3>\n

Microsoft provides one extended property, MS_Description<\/strong>, which can be used from both Enterprise Manager and SSMS to provide a description of the object to which it is bound. Further, the Diagram Designer provides a description field, accessible via the custom view, which provides an easy way of viewing and editing the documentation of the columns.<\/p>\n

However, extended properties are just about providing basic descriptions of objects. They are a lot more versatile that that. The designers of extended properties sensibly placed no restrictions on the properties that one could attribute to database objects. It is perfectly OK, for example, to provide extra metadata to assist the application layer in rendering or querying the data.<\/p>\n

When writing the documentation for objects, it is generally quickest to use the facilities within Microsoft’s own tools to add basic descriptions, but beyond that there eventually comes a time that one has to use stored procedures to add documentation.<\/p>\n

At the basic level, in SQL Server 2000, all extended properties are stored in sysproperties, but are accessed by a number of stored procedures.<\/p>\n\n\n\n\n\n\n
\n

sp_addextendedproperty<\/strong><\/p>\n<\/td>\n

\n

Adds a new extended property to a database object<\/p>\n<\/td>\n<\/tr>\n

\n

sp_dropextendedproperty<\/strong><\/p>\n<\/td>\n

\n

Removes an extended property from a database object<\/p>\n<\/td>\n<\/tr>\n

\n

sp_updateextendedproperty<\/strong><\/p>\n<\/td>\n

\n

Updates the value of an existing extended property<\/p>\n<\/td>\n<\/tr>\n

\n

fn_listextendedproperty<\/strong><\/p>\n<\/td>\n

\n

Retrieves the value of an extended property or the list of all extended properties from a database object<\/p>\n<\/td>\n<\/tr>\n<\/tbody>\n<\/table>\n

These stored procedures are clumsy to use and hardly encourage the programmer into using extended properties. However, a few examples might make their use a bit clearer:<\/p>\n

\/*we add the extended property to provide a description to the  dbo.Customer.InsertionDate column *\/\r\nEXECUTE sys.sp_addextendedproperty 'MS_Description',\r\n  'the date at which the row was created', 'schema', 'dbo', 'table',\r\n  'Customer', 'column', 'InsertionDate';\r\n-- alternative syntax for SQL 2005\r\nEXECUTE sys.sp_addextendedproperty 'MS_Description',\r\n  'the date at which the row was created', 'schema', 'sales', 'table',\r\n  'Customer', 'column', 'ModifiedDate';\r\n\/* and then update the description of the  dbo.Customer.InsertionDate column  *\/\r\nEXECUTE sys.sp_updateextendedproperty 'MS_Description',\r\n  'the full date at which the row was created', 'schema', 'dbo', 'table',\r\n  'Customer', 'column', 'InsertionDate';\r\n\/* we can list this column *\/\r\nSELECT *\r\n  FROM::fn_listextendedproperty('MS_Description', 'schema', 'dbo', 'table', 'Customer', 'column', 'InsertionDate');\r\n\/* or all the properties for the table column of dbo.Customer*\/\r\nSELECT *\r\n  FROM::fn_listextendedproperty(DEFAULT, 'schema', 'dbo', 'table', 'Customer', 'column', DEFAULT);\r\n\/* And now we drop the MS_Description property of   dbo.Customer.InsertionDate column *\/\r\nEXECUTE sys.sp_dropextendedproperty 'MS_Description', 'schema', 'dbo',\r\n  'table', 'Customer', 'column', 'InsertionDate';<\/pre>\n
A database-documenting stored procedure<\/h3>\n
I find the stored procedures described in the previous section unintuitive and don’t exactly tempt the programmer into adding documentation. For a start, if you try to add an extended property that already exists, you get an error. If you update an extended property that doesn’t exist, you get an error. If you put an extended property on an object that doesn’t exist, you get no error or return code to tell you.\u00a0 From SQL Server 2005 onwards, a system view was added to make it easier to read extended properties but this doesn’t help with adding, deleting or updating them. I’d prefer something that described the object and its hierarchy in a more conventional way. For example if one wanted to alter the description of a surname<\/strong> column in a Customer<\/strong> table then it should be ‘dbo.Customer.Surname.MS_Description<\/strong>‘ using the ‘schema<\/strong>.table.column<\/strong>‘ hierarchy.<\/p>\n
In order to make things easier, I created a simple ‘helper’ stored procedure which simplifies the access to Microsoft’s system stored procedures, but doesn’t try to replace them. If you provide the description of the object and the hierarchy, then it displays what is there. If you provide a value, it either assigns it or, if you want, appends it to the end of the current value.<\/p>\n
The stored procedure autosenses which version of SQL Server it is on, and loads with the valid object hierarchies for the operating system. It checks the hierarchy you give it to see if it is valid. This list is rather handy, so the stored procedure also includes a feature that provides the hierarchy as a table.<\/p>\n
The full source code for this sp_DBDoc<\/strong> stored procedure is provided in the source code<\/strong> for this article. It is designed to reside in the MASTER database so it can be used as a utility in any database on the server, but you must be db_owner in any database you want to use it for.<\/p>\n
Create the stored procedure and let’s try it out. First, let’s see what hierarchies can have extended properties:<\/p>\n
EXEC sp_DbDoc '','','','possible'\r\n<\/pre>\n
or, alternatively:<\/p>\n
sp_DbDoc @Function='possible'\r\n<\/pre>\n
You will see that there are rather a lot – and that the possible objects that can have attributes attached to them were greatly expanded in SQL 2005, and just four were added since (those picked out in bold are available in both 2000 and 2005, but ‘user’ became ‘schema’ from 2005 onwards):<\/p>\n\n\n\n\n\n\n\n\n\n\n\n\n\n\n\n\n\n\n\n\n\n\n\n\n
\n
assembly<\/p>\n<\/td>\n
\n
Schema.Service<\/p>\n<\/td>\n
\n
user.function.Constraint<\/strong><\/p>\n<\/td>\n<\/tr>\n
\n
contract<\/p>\n<\/td>\n
\n
Schema.Synonym<\/p>\n<\/td>\n
\n
user.function.Parameter<\/strong><\/p>\n<\/td>\n<\/tr>\n
\n
Event Notification<\/p>\n<\/td>\n
\n
Schema.Table<\/p>\n<\/td>\n
\n
user.Procedure<\/strong><\/p>\n<\/td>\n<\/tr>\n
\n
fileGroup.Logical file Name<\/p>\n<\/td>\n
\n
Schema.Table.Column<\/p>\n<\/td>\n
\n
user.Procedure.Parameter<\/strong><\/p>\n<\/td>\n<\/tr>\n
\n
Message type<\/p>\n<\/td>\n
\n
Schema.Table.Constraint<\/p>\n<\/td>\n
\n
user.Queue<\/p>\n<\/td>\n<\/tr>\n
\n
partition Function<\/p>\n<\/td>\n
\n
Schema.Table.Index<\/p>\n<\/td>\n
\n
user.Queue.Event Notification<\/p>\n<\/td>\n<\/tr>\n
\n
partition Scheme<\/p>\n<\/td>\n
\n
Schema.Table.Trigger<\/p>\n<\/td>\n
\n
User.Rule<\/strong><\/p>\n<\/td>\n<\/tr>\n
\n
Remote Service Binding<\/p>\n<\/td>\n
\n
Schema.Type<\/p>\n<\/td>\n
\n
user.Service<\/p>\n<\/td>\n<\/tr>\n
\n
route<\/p>\n<\/td>\n
\n
Schema.View<\/p>\n<\/td>\n
\n
user.Synonym<\/p>\n<\/td>\n<\/tr>\n
\n
Schema<\/p>\n<\/td>\n
\n
Schema.View.Trigger<\/p>\n<\/td>\n
\n
User.Table<\/strong><\/p>\n<\/td>\n<\/tr>\n
\n
schema.aggregate<\/p>\n<\/td>\n
\n
Schema.View.column<\/p>\n<\/td>\n
\n
User.Table.Column<\/strong><\/p>\n<\/td>\n<\/tr>\n
\n
schema.Default<\/p>\n<\/td>\n
\n
Schema.View.index<\/p>\n<\/td>\n
\n
User.Table.Constraint<\/strong><\/p>\n<\/td>\n<\/tr>\n
\n
schema.function<\/p>\n<\/td>\n
\n
Schema.XML Schema Collection<\/p>\n<\/td>\n
\n
User.Table.Index<\/strong><\/p>\n<\/td>\n<\/tr>\n
\n
schema.function.column<\/p>\n<\/td>\n
\n
Service<\/p>\n<\/td>\n
\n
User.Table.Trigger<\/strong><\/p>\n<\/td>\n<\/tr>\n
\n
schema.function.Constraint<\/p>\n<\/td>\n
\n
trigger<\/p>\n<\/td>\n
\n
user.Type<\/p>\n<\/td>\n<\/tr>\n
\n
schema.function.Parameter<\/p>\n<\/td>\n
\n
type<\/p>\n<\/td>\n
\n
User.View<\/strong><\/p>\n<\/td>\n<\/tr>\n
\n
schema.Procedure<\/p>\n<\/td>\n
\n
user<\/strong><\/p>\n<\/td>\n
\n
User.View.column<\/strong><\/p>\n<\/td>\n<\/tr>\n
\n
schema.Procedure.Parameter<\/p>\n<\/td>\n
\n
user.aggregate<\/p>\n<\/td>\n
\n
User.View.index<\/strong><\/p>\n<\/td>\n<\/tr>\n
\n
schema.Queue<\/p>\n<\/td>\n
\n
user.Default<\/strong><\/p>\n<\/td>\n
\n
User.View.Trigger<\/strong><\/p>\n<\/td>\n<\/tr>\n
\n
schema.Queue.Event Notification<\/p>\n<\/td>\n
\n
user.function<\/strong><\/p>\n<\/td>\n
\n
User.XML Schema Collection<\/p>\n<\/td>\n<\/tr>\n
\n
schema.Rule<\/p>\n<\/td>\n
\n
user.function.column<\/strong><\/p>\n<\/td>\n
Asymmetric Key<\/td>\n<\/tr>\n
\n
Certificate<\/p>\n<\/td>\n
\n
Plan guide<\/p>\n<\/td>\n
\n
Synonym<\/p>\n<\/td>\n<\/tr>\n<\/tbody>\n<\/table>\n
Let’s start very simply. Let’s just create a description for the entire database:<\/p>\n
EXEC sp_dbDoc  '','','This is a sample database that illustrates how extended properties can be assigned to objects'\r\n<\/pre>\n
Nothing exciting here so let’s add a ‘revision date’ property:<\/p>\n
EXEC sp_dbDoc  '','revisionDate','20 Nov 2006: Built the first iteration'\r\n<\/pre>\n
Now we want to add the new version, rather than replace the existing value:<\/p>\n
EXEC sp_dbDoc  '','revisionDate','21 Nov 2006: Fixed warning message  in  build script','append'\r\n<\/pre>\n
We can list all extended properties and values for columns of a given table, for example the customer<\/strong> table:<\/p>\n
EXEC sp_dbDoc 'user.table.column','dbo.customer' -- SQL 2000 EXEC sp_dbDoc 'schema.table.column','sales.customer' -- alternative in SQL 2005 onwards\r\n<\/pre>\n
Assigning a standard MS_Documentation<\/strong> property and value is easy:<\/p>\n
\/* either alter or create an entry for the MS_Documentation property for insertionDate  *\/sp_dbDoc  'user.table.column','dbo.customer.insertionDate','This logs the date that the row was inserted'\r\n<\/pre>\n
Or you can add your own property. By way of example, we assign a suggested convert style for a date field:<\/p>\n
\/* either alter or create an entry for the ConvertStyle property  for insertionDate *\/  EXEC sp_dbDoc  'user.table.column','dbo.customer.insertionDate.ConvertStyle',113 \r\n<\/pre>\n
In the following code you’ll find several more examples for this procedure included as comments.<\/p>\n
USE master;\r\nIF EXISTS (SELECT 1 FROM sys.objects WHERE objects.name = 'sp_DbDoc')\r\nDROP PROCEDURE sp_DBDoc;\r\nGO\r\nCREATE PROCEDURE sp_DBDoc @hierarchy VARCHAR(40) = '',\r\n  @object VARCHAR(40) = '',\r\n  @value SQL_VARIANT = NULL, --'This column tells you the date of insertion',\r\n  @Function VARCHAR(20) = 'assign' \/*\r\n--lists all extended properties and values for columns of the 'customer' table.\r\nsp_DbDoc 'schema.table.column','person.person'\r\nsp_DbDoc '','','This database is for testing purposes only' --specify the default Extended property\r\nsp_DbDoc '','','','delete' --delete the MS_Description Extended property\r\nsp_DbDoc '','','','possible' --list out all possible hierarchies supporting EPs\r\n--or\r\nsp_DbDoc @Function='possible'--list out all possible hierarchies supporting EPs\r\n\r\n--lists all extended properties and values for columns of the 'customer' table.\r\nsp_DbDoc 'schema.table.column','person.contactType'--error on SQL Server 2000\r\n\r\n--either alter or create an entry for the MS_Documentation property for insertionDate\r\nsp_DbDoc  'user.table.column','dbo.customer.insertionDate','This logs the date that the row was inserted'\r\n\r\n--either alter or create an entry for the ConvertStyle property for insertionDate\r\nsp_DbDoc  'schema.table.column','dbo.customer.insertionDate.ConvertStyle',113\r\n\r\n--either alter or create an entry for the MS_Documentation property for title\r\nsp_DbDoc  'schema.table.column','dbo.customer.title','e.g. Mr Mrs, Madame, sir, etc'\r\n\r\n--either alter or create an entry for the 'Range' property for DOB\r\nsp_DbDoc  'schema.table.column','dbo.customer.DOB.range','01 Jan 1920'\r\n\r\nsp_DbDoc 'schema.procedure','dbo.spGetCustomer',\r\n\t'This procedure gets the documentation '\r\n\r\nsp_DbDoc 'schema.procedure.parameter','dbo.spGetCustomer.@customer_ID',\r\n\t'This is the ID of the customer'\r\n\r\n--create a description for the database\r\nsp_DbDoc  '','','This is a sample database that illustrates how extended properties can be assigned to objects'\r\n\r\nExecute sp_DbDoc  'Database','version','3.2.2 (removed incomprehensible CTEs)','append'\r\n\r\n--add a new property called 'revision date'\r\nsp_DbDoc  '','revisionDate','20 Nov 2006'\r\n--append to the value string in the property 'revision date'\r\nsp_DbDoc  '','revisionDate','-Fixed warning message (21 Nov 2006)','append'\r\n*\/\r\nAS\r\nDECLARE @Hits INT;\r\nDECLARE @Level0 VARCHAR(40);\r\nDECLARE @Level1 VARCHAR(40);\r\nDECLARE @Level2 VARCHAR(40);\r\nDECLARE @Name0 VARCHAR(40);\r\nDECLARE @Name1 VARCHAR(40);\r\nDECLARE @Name2 VARCHAR(40);\r\nDECLARE @ErrorMessage VARCHAR(40);\r\nDECLARE @LowestVersion INT;\r\nDECLARE @HighestVersion INT;\r\nDECLARE @V9 INT;\r\nDECLARE @Split INT;\r\nDECLARE @Split2 INT;\r\nDECLARE @Split3 INT;\r\nDECLARE @Property VARCHAR(80);\r\nDECLARE @PropertyForAmendment VARCHAR(80);\r\nDECLARE @SQLVersion INT;\r\nDECLARE @Return INT;\r\nDECLARE @Database VARCHAR(40);\r\nSET NOCOUNT ON;\r\nSELECT @Return = 0, @Database = DB_NAME();\r\nIF @hierarchy = 'Database'\r\nSELECT @hierarchy = '';\r\nDECLARE @hierarchies TABLE\r\n  (\r\n    Hierarchy VARCHAR(40) UNIQUE,\r\n    Level0 VARCHAR(40),\r\n    Level1 VARCHAR(40),\r\n    Level2 VARCHAR(40),\r\n    LowestVersion INT,\r\n    HighestVersion INT\r\n);\r\nINSERT INTO @hierarchies\r\n  SELECT TheHierarchies.Hierarchy, TheHierarchies.Level0,\r\n    TheHierarchies.Level1, TheHierarchies.Level2,\r\n    TheHierarchies.LowestVersion, TheHierarchies.HighestVersion\r\n  FROM\r\n  (\r\n  VALUES('', NULL, NULL, NULL, 8, NULL), --database \r\n    ('Asymmetric Key', 'Asymmetric Key', NULL, NULL, 10, NULL),\r\n    ('Certificate', 'Certificate', NULL, NULL, 10, NULL),\r\n    ('Plan guide', 'plan guide', NULL, NULL, 10, NULL),\r\n    ('Synonym', 'Synonym', NULL, NULL, 10, NULL),\r\n    ('schema.aggregate', 'schema', 'aggregate', NULL, 9, NULL),\r\n    ('contract', 'Contract', NULL, NULL, 9, NULL), --service broker\r\n    ('assembly', 'assembly', NULL, NULL, 9, NULL),\r\n  --('database','database',NULL,NULL, 8, NULL),\r\n    ('schema.Default', 'schema', 'Default', NULL, 9, NULL),\r\n    ('Event Notification', 'Event Notification', NULL, NULL, 9, NULL),\r\n    ('fileGroup', 'fileGroup', NULL, NULL, 9, NULL),\r\n    ('fileGroup.Logical file Name', 'fileGroup', 'Logical file Name', NULL, 9, NULL),\r\n\t('schema.function', 'Schema', 'Function', NULL, 9, NULL),\r\n    ('schema.function.column', 'Schema', 'Function', 'Column', 9, NULL),\r\n    ('schema.function.Constraint', 'Schema', 'Function', 'Constraint', 9,NULL),\r\n    ('schema.function.Parameter', 'Schema', 'Function', 'Parameter', 9, NULL),\r\n    ('Message type', 'Message type', NULL, NULL, 9, NULL),\r\n    ('partition Function', 'partition Function', NULL, NULL, 9, NULL),\r\n    ('partition Scheme', 'partition Scheme', NULL, NULL, 9, NULL),\r\n    ('schema.Procedure', 'schema', 'Procedure', NULL, 9, NULL),\r\n    ('schema.Procedure.Parameter', 'schema', 'Procedure', 'Parameter', 9, NULL),\r\n    ('schema.Queue', 'schema', 'Queue', NULL, 9, NULL), --Service Broker\r\n    ('schema.Queue.Event Notification', 'schema', 'Queue',\r\n    'Event Notification', 9, NULL ), --Service Broker\r\n    ('Remote Service Binding', 'Remote Service Binding', NULL, NULL, 9, NULL), --Service Broker\r\n    ('route', 'route', NULL, NULL, 9, NULL), --Service Broker\r\n    ('schema.Rule', 'schema.Rule', NULL, NULL, 9, NULL),\r\n    ('Schema', 'Schema', NULL, NULL, 9, NULL),\r\n    ('Service', 'Service', NULL, NULL, 9, NULL),\r\n    ('Schema.Service', 'Schema', 'Service', NULL, 9, NULL),\r\n    ('Schema.Synonym', 'Schema', 'Synonym', NULL, 9, NULL),\r\n    ('Schema.Table', 'Schema', 'Table', NULL, 9, NULL),\r\n    ('Schema.Table.Column', 'Schema', 'Table', 'Column', 9, NULL),\r\n    ('Schema.Table.Constraint', 'Schema', 'Table', 'Constraint', 9, NULL),\r\n    ('Schema.Table.Index', 'Schema', 'Table', 'Index', 9, NULL),\r\n    ('Schema.Table.Trigger', 'Schema', 'Table', 'Trigger', 9, NULL),\r\n    ('Symmetric Key', 'Symmetric Key', NULL, NULL, 9, NULL),\r\n    ('trigger', 'trigger', NULL, NULL, 9, NULL), --DDL Triggers only (alters any trigger on the database)\r\n    ('type', 'type', NULL, NULL, 9, NULL), --backward compatibility only\r\n    ('Schema.Type', 'Schema', 'Type', NULL, 9, NULL),\r\n    ('Schema.View', 'Schema', 'View', NULL, 9, NULL),\r\n    ('Schema.View.column', 'Schema', 'View', 'column', 9, NULL),\r\n    ('Schema.View.index', 'Schema', 'View', 'index', 9, NULL),\r\n    ('Schema.View.Trigger', 'Schema', 'View', 'Trigger', 9, NULL),\r\n    ('Schema.XML Schema Collection', 'Schema', 'XML Schema Collection', NULL,\r\n    9, NULL\r\n  )\r\n  ) TheHierarchies(Hierarchy, Level0, Level1, Level2, LowestVersion,\r\n  HighestVersion\r\n  );\r\n--find what version of SQL Server you are using \r\nSELECT @SQLVersion = @@microsoftversion \/ POWER(2, 24);\r\n--abandon the procedure if this version is not supported\r\nIF @SQLVersion NOT BETWEEN 8 AND 14\r\n  BEGIN\r\n  RAISERROR('sorry but sp_DbDoc is not written for SQL Server %s',16, 1, @SQLVersion );\r\n  RETURN 1;\r\n  END;\r\n--if we just want to list out what is possible by way of extended properties    \r\nIF @Function = 'possible'\r\n  BEGIN\r\n  SELECT [@hierarchies].Hierarchy\r\n    FROM @hierarchies\r\n    WHERE @SQLVersion BETWEEN [@hierarchies].LowestVersion AND COALESCE([@hierarchies].HighestVersion, 14)\r\n    ORDER BY [@hierarchies].Level1, [@hierarchies].Level0;\r\n  RETURN 0;\r\n  END;\r\n--find out the appropriate values for the levels when calling the built-in EPs    \r\nSELECT @Level0 = [@hierarchies].Level0, @Level1 = [@hierarchies].Level1,\r\n  @Level2 = [@hierarchies].Level2,\r\n  @LowestVersion = [@hierarchies].LowestVersion,\r\n  @HighestVersion = COALESCE([@hierarchies].HighestVersion, 14)\r\n  FROM @hierarchies\r\n  WHERE [@hierarchies].Hierarchy LIKE @hierarchy;\r\nSELECT @Hits = @@RowCount;\r\n--was it ambiguous?\r\nIF @Hits <> 1\r\n  BEGIN\r\n  SELECT @ErrorMessage = CASE WHEN @Hits > 1 THEN 'Ambiguous' ELSE 'Unknown' END;\r\n  RAISERROR('%s hierarchy ''%s''', 16, 1, @ErrorMessage, @hierarchy);\r\n  RETURN 1;\r\n  END;\r\n--is what you've chosen appropriate for our version?    \r\nIF(@SQLVersion NOT BETWEEN @LowestVersion AND @HighestVersion)\r\n  BEGIN\r\n  RAISERROR('sorry but %s hierarchy not in SQL Server %d',\r\n       16, 1, @hierarchy, @SQLVersion );\r\n  RETURN 1;\r\n  END;\r\n--work out the name of the property \r\nSELECT @Split = CHARINDEX('.', @object + '.', 1);\r\nSELECT @Split2 = CHARINDEX('.', @object + '....', @Split + 1);\r\nSELECT @Split3 = CHARINDEX('.', @object + '....', @Split2 + 1);\r\nSELECT @Property =\r\n  CASE \r\n     WHEN LEN(@object) > 0 THEN LEFT(@object, @Split - 1)ELSE NULL\r\n  END;\r\nIF @Level0 IS NOT NULL\r\nSELECT @Name0 = @Property, @Property = NULL;\r\nSELECT @Property =\r\n  CASE WHEN @Split BETWEEN 1 AND LEN(@object) \r\n    THEN SUBSTRING( @object,@Split + 1, @Split2 - @Split - 1) \r\n\tELSE @Property\r\n  END;\r\nIF @Level1 IS NOT NULL\r\nSELECT @Name1 = @Property, @Property = NULL;\r\nSELECT @Property =\r\n  CASE WHEN @Split2 BETWEEN @Split AND LEN(@object) \r\n  THEN SUBSTRING( @object,@Split2 + 1, @Split3 - @Split2- 1) \r\n  ELSE @Property\r\n  END;\r\nIF @Level2 IS NOT NULL\r\nSELECT @Name2 = @Property, @Property = NULL;\r\nSELECT @Property =\r\n  CASE WHEN @Split3 BETWEEN @Split2 AND LEN(@object) \r\n  THEN SUBSTRING( @object,@Split3 + 1,2000) ELSE @Property\r\n  END;\r\nDECLARE @ObjectPath VARCHAR(100)\r\nSELECT @Objectpath=COALESCE(@name0+'.','')+COALESCE(@name1+'.','')+COALESCE(@name2,'')\r\nIF COALESCE(@Objectpath,'') <> ''\r\n  BEGIN\r\n  IF NOT EXISTS\r\n    (SELECT * FROM sys.objects WHERE objects.object_id = OBJECT_ID(@Objectpath))\r\n    BEGIN\r\n    RAISERROR('sorry but ''%s'' doesn''t seem to be there in %s',\r\n\t    16, 1, @Objectpath,@Database);\r\n    RETURN 1;\r\n    END;\r\n  END;\r\nDECLARE @existing TABLE(value SQL_VARIANT);\r\nSELECT @PropertyForAmendment = COALESCE(@Property, 'MS_Description');\r\n--remember what is already there \r\nINSERT INTO @existing(value)\r\n  SELECT value\r\n  FROM::fn_listextendedproperty\r\n   (@PropertyForAmendment, @Level0, @Name0, @Level1, @Name1, @Level2, @Name2);\r\nIF @value IS NOT NULL OR @Function = 'delete'\r\nIF EXISTS (SELECT 1 FROM @existing)\r\n  BEGIN --it is likely to be an update\r\n  IF(SELECT COUNT(*) FROM @existing) > 1\r\n    BEGIN\r\n    RAISERROR('Ambiguous property spec', 16, 1);\r\n    RETURN 1;\r\n    END;\r\n  IF @Function = 'delete'\r\n    BEGIN\r\n    EXECUTE @Return = sys.sp_dropextendedproperty @PropertyForAmendment,\r\n      @Level0, @Name0, @Level1, @Name1, @Level2, @Name2;\r\n    END;\r\n  ELSE\r\n    BEGIN\r\n    IF @Function = 'append'\r\n    SELECT @value =\r\n      (SELECT CAST([@existing].value AS VARCHAR(8000))FROM @existing) + ', '\r\n      + CAST(@value AS VARCHAR(8000));\r\n    EXECUTE @Return = sys.sp_updateextendedproperty @PropertyForAmendment,\r\n      @value, @Level0, @Name0, @Level1, @Name1, @Level2, @Name2;\r\n    END;\r\n  END;\r\nELSE --whether append or add\r\n  BEGIN\r\n  EXECUTE @Return = sys.sp_addextendedproperty @PropertyForAmendment, @value,\r\n    @Level0, @Name0, @Level1, @Name1, @Level2, @Name2;\r\n  END;\r\nIF @Return <> 0\r\n  BEGIN\r\n  RAISERROR('sorry but there was an error with %s', 16, 1, @object);\r\n  RETURN 1;\r\n  END;\r\n\r\n--whatever else you do, show what is now there\r\nSELECT COALESCE(objname, 'Database') AS object, name, value\r\n  FROM::fn_listextendedproperty(@Property, @Level0, @Name0, @Level1, @Name1, @Level2, @Name2);\r\n--Select @Property,@Level0, @name0, @Level1, @name1, @level2, @name2\r\nGO\r\nIF NOT EXISTS\r\n  (SELECT 1\r\n     FROM sys.objects\r\n     WHERE objects.name = 'sp_DbDoc' AND objects.is_ms_shipped = 1\r\n)\r\nEXEC sys.sp_MS_marksystemobject 'sp_DbDoc';\r\n<\/pre>\n
Of course, ultimately, extended properties are just one of the various means of ensuring that your databases are well-documented and easily understood, such as using long descriptive object names.<\/p>\n<\/div>\n","protected":false},"excerpt":{"rendered":"
William Brewer investigates an unsung hero in SQL Server, extended properties, and shows how to use them to create self-documenting SQL Server databases.…<\/p>\n","protected":false},"author":213195,"featured_media":0,"comment_status":"open","ping_status":"closed","sticky":false,"template":"","format":"standard","meta":{"_acf_changed":false,"footnotes":""},"categories":[143532],"tags":[4168,4531,4530,4150,4151,4213,4254,4183],"coauthors":[6796],"class_list":["post-190","post","type-post","status-publish","format-standard","hentry","category-tools-sql-server","tag-database","tag-documenting-databases","tag-extended-properties","tag-sql","tag-sql-server","tag-sql-tools","tag-stored-procedures","tag-t-sql"],"acf":[],"_links":{"self":[{"href":"https:\/\/www.red-gate.com\/simple-talk\/wp-json\/wp\/v2\/posts\/190","targetHints":{"allow":["GET"]}}],"collection":[{"href":"https:\/\/www.red-gate.com\/simple-talk\/wp-json\/wp\/v2\/posts"}],"about":[{"href":"https:\/\/www.red-gate.com\/simple-talk\/wp-json\/wp\/v2\/types\/post"}],"author":[{"embeddable":true,"href":"https:\/\/www.red-gate.com\/simple-talk\/wp-json\/wp\/v2\/users\/213195"}],"replies":[{"embeddable":true,"href":"https:\/\/www.red-gate.com\/simple-talk\/wp-json\/wp\/v2\/comments?post=190"}],"version-history":[{"count":8,"href":"https:\/\/www.red-gate.com\/simple-talk\/wp-json\/wp\/v2\/posts\/190\/revisions"}],"predecessor-version":[{"id":68682,"href":"https:\/\/www.red-gate.com\/simple-talk\/wp-json\/wp\/v2\/posts\/190\/revisions\/68682"}],"wp:attachment":[{"href":"https:\/\/www.red-gate.com\/simple-talk\/wp-json\/wp\/v2\/media?parent=190"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/www.red-gate.com\/simple-talk\/wp-json\/wp\/v2\/categories?post=190"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/www.red-gate.com\/simple-talk\/wp-json\/wp\/v2\/tags?post=190"},{"taxonomy":"author","embeddable":true,"href":"https:\/\/www.red-gate.com\/simple-talk\/wp-json\/wp\/v2\/coauthors?post=190"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}