- Extended properties: the post-it note of SQL Server
- The description of objects
- Adding the description of columns
- The child objects
- Parameters
- Seeing what extended properties exist for a database
- Conclusions
The Series
This article is part of the Stairway Series: Stairway to Exploring Database Metadata
For the working database programmer, the metadata provides a means to program more rapidly because you can find out a great deal about the database from querying the metadata. These queries enable you to find out how the database is set up, configured, and what the properties are for the various objects. This stairway series aims to demystify all these metadata details and to explain how you can query and use this metadata to more effectively manage your SQL Server Databases.
In this stairway, we would like to appeal to a reasonably wide range of expertise and knowledge in SQL Server. I assume that if you have managed to get this far, you have a reasonable working knowledge of database objects and other types of metadata; in other words, you have a reasonable idea of what a view, table, function, procedure, schema, constraint and so on actually is.
In this level, I’ll introduce Extended Properties. As with the previous levels, I want to illustrate as much as possible with practical and immediately useful code. As always, I will not tackle the topic of changing metadata. I have described elsewhere, in the article ‘Reading, Writing, and Creating SQL Server Extended Properties’, how to change the contents of a description held in extended properties.
Extended Properties: The Post-It Notes of SQL Server.
Extended properties are like sticky notes that you can attach to a variety of database objects. Once you are familiar with them, you’ll find them to be enormously useful; not only for documentation but for team interaction, general reminders, keeping tabs on database versions, and the revision number of objects. In fact, many development tasks that must be automated can be aided by extended properties. You can have as many categories of extended properties as you want. Most people just use the category called ‘MS_Description’ in order to add comments to tables and their child objects, because the table creation scripts where you might otherwise put them aren’t preserved within the database. However, there are a wide range of other uses, and the values are held as SQL Variants so they can be used with a variety of datatypes.
Extended properties can be attached to objects, but also to other database constructs that aren’t categorised within SQL Server as objects. A metadata object is anything that has an entry in sys.objects. There are plenty of familiar things in the metadata that aren’t objects though: Columns, for example, aren’t objects, nor are indexes. Parameters aren’t either; so none of these objects have an object_id value and there is no single existing numeric key that is guaranteed to uniquely identify every component of a database.
The Description of Objects
Let’s start relatively simply. Here we list out all the objects in the database (such as functions, procedures, tables, queues and constraints) and include all the extended properties that use the name ‘MS_Description’, which is the conventional name for simple documentation. This guarantees us just one row per object.
SELECT --objects CASE WHEN ob.parent_object_id > 0 --if it is a child object THEN OBJECT_SCHEMA_NAME(ob.parent_object_id) --add the parent + '.' + OBJECT_NAME(ob.parent_object_id) + '.' + ob.name ELSE OBJECT_SCHEMA_NAME(ob.object_id) + '.' + ob.name END AS 'Object_name', COALESCE(ep.value, '') AS 'Value' --display the property if there FROM sys.objects ob LEFT OUTER JOIN sys.extended_properties ep ON ep.major_id = ob.object_id --class of 1 means Object or column AND ep.class = 1 AND ep.minor_id = 0 AND ep.name='MS_Description' WHERE ob.is_ms_shipped = 0 -- leave out all the system stuff ORDER BY [Object_name] -- and order by the object name
This gives results similar to the following (using the Adventureworks2014 database on SQL Server2016)
We’ve chosen to return all objects, which is why I’ve used a right outer join between the sys.objects and the sys.extended_properties views. When I ran this query I got 532 rows returned, but I’m not showing them all in the figure above. Returning just those objects that already have comments would be easier, by using an inner join.
These filter conditions in the ON clause, ‘ep.class = 1 AND ep.minor_id = 0’, are curious. What Microsoft are doing is a well-known ‘code smell’ called the polymorphic join. A polymorphic join involves a foreign key that contains not just an id but an indicator of which table it refers to. In this case the ID is ep.minor_id, and ep.minor_id indicates the table it needs to join to. Obviously, this device is not relational and cannot be enforced by a constraint, but this isn’t a problem here because these are views and you cannot create a foreign key constraint in such circumstances.
The conditions ‘ep.class = 1 AND ep.minor_id = 0’ are more complicated than this because ep.class of 1 means either a table or a column. A column isn’t an object but an attribute. An ep.minor_id with a value of 0 therefore means the table itself whereas a value greater than zero is a column that is contained in a different view called sys.columns . What, then if we wish to list columns as well as objects?
Adding the description of columns
It’s not just tables that have columns, but views and Table-valued functions as well. Procedures don’t have columns. In this query, we add columns to the database objects. When you’re using this query, you’d generally want to add a WHERE clause to restrict the result to just those objects you’re interested in.
SELECT --objects AND columns
CASE WHEN parent_object_id>0 --if it is a child object
THEN OBJECT_SCHEMA_NAME(parent_object_id)--add the parent
+ '.'+OBJECT_NAME(parent_object_id)+'.'+name
ELSE OBJECT_SCHEMA_NAME(object_id)+'.'+name END
AS 'Object_Name',
COALESCE(value, '') AS 'Value' --display the property if there
FROM
(
SELECT ep.value, ob.object_id, ob.Parent_Object_id,ob.name
FROM sys.objects ob
LEFT OUTER JOIN sys.extended_properties ep
ON ep.major_id = ob.object_id --class= 1 means Object or column
AND ep.class = 1 AND ep.minor_id = 0 AND ep.name='MS_Description'
WHERE ob.is_ms_shipped = 0
UNION ALL --AND now we add the columns
SELECT ep.value, 0 , col.Object_id, col.name
FROM sys.columns col
LEFT OUTER JOIN sys.extended_properties ep
ON ep.major_id = col.object_id --1 means Object or column
AND ep.class = 1 AND ep.minor_id = column_id AND ep.name='MS_Description'
WHERE ObjectPropertyEx(col.OBJECT_ID,'IsMSShipped')=0
) ObjsAndCols(value, Object_id, Parent_Object_id,name)
ORDER BY [Object_Name]
This query returned 1294 rows when I ran it, and included columns and their extended properties.
With this sort of routine, we can get a long way with inspecting and searching through our database to determine what tables, functions, views, queues and columns have extended properties; documentation in this case.
The Child Objects
We can examine an object in more detail if it is a ‘parent object’ (Either a user table or a service queue can, and probably will, have dependent objects, and will be referenced by them as a parent object). Some of the objects in the sys.objects view are actually child objects. These include check constraints, default constraints, foreign key constraints, primary key constraints, SQL triggers and unique constraints. These can all have extended properties attached to them.
Default Constraints, Foreign Key Constraints and Check Constraints.
Those default constraints, foreign key constraints and check constraints we’ve been listing don’t show the column they belong to. That is because foreign key constraints and check constraints can have more than one column. These are table constraints and are associated directly to the table rather than a particular column. You can find out whether a constraint is a column-level constraint via the function:
OBJECTPROPERTYEX(<object_id_of_constraint,'CnstIsColumn')
In the next query, we examine all DEFAULT constraints in a particular table, along with any extended property descriptions they may have. DEFAULT constraints are always at a column level so we don’t need to use the OBJECTPROPERTY function shown above.
SELECT OBJECT_SCHEMA_NAME(dc.parent_object_id) --the schema name
+ '.' + OBJECT_NAME(dc.parent_object_id) --the table name
+ '.' + col.name + '(' + dc.name + ')' AS path, --the names of column and default
CONVERT(VARCHAR(100), COALESCE(ep.value, '')) AS Description --the extended property as string
FROM sys.default_constraints dc --where the column they belong to is held
INNER JOIN sys.columns col
ON dc.parent_object_id = col.object_id
AND dc.parent_column_id = col.column_id
LEFT OUTER JOIN sys.extended_properties ep
ON ep.major_id = dc.object_id
AND ep.class = 1
AND ep.name = 'MS_Description'
AND ep.minor_id = 0
WHERE dc.parent_object_id = OBJECT_ID('Sales.SalesOrderDetail');
Here are the three rows that were returned when I ran the query:
The Documentation of Foreign Keys
The foreign keys are handled in a very similar way, except that we need to access two other views to get the information we need. We will want to see both the column-level foreign-key constraints as well as the table-level ones, and they’ll need to be separate queries, combined with UNION. I’ve made the code more verbose than strictly necessary so you can break this code in two to list just column-level or table-level constraints. We’ll try the code out on a table that happens to have both a table-level foreign key constraints as well as a column-level constraint.
SELECT --firstly we'll do the column foreign key constraints
OBJECT_SCHEMA_NAME(fk.parent_object_id) --the schema name
+ '.' + OBJECT_NAME(fk.parent_object_id) --the table name
+ '.' + col.name + '(' + fk.name + ')' AS Path, --the names of column and foreign key
CONVERT(VARCHAR(100), COALESCE(ep.value, '')) AS Description, --the extended property as string
CONVERT(BIT,1) AS 'Column-based'
FROM sys.foreign_keys fk --the foreign keys are listed here, together with details
INNER JOIN sys.foreign_key_columns fkc --separate view as there can be several columns
ON fk.object_id = fkc.constraint_object_id
INNER JOIN sys.columns col
ON fk.parent_object_id = col.object_id
AND fkc.parent_column_id = col.column_id
LEFT OUTER JOIN sys.extended_properties ep
ON ep.major_id = fk.object_id
AND ep.class = 1
AND ep.name = 'MS_Description'
AND ep.minor_id = 0
WHERE OBJECTPROPERTYEX(fkc.constraint_object_id, 'CnstIsColumn') = 1
AND fk.parent_object_id = OBJECT_ID('Sales.SalesOrderDetail')
UNION ALL
SELECT --then we do the table-level constraints
OBJECT_SCHEMA_NAME(fk.parent_object_id) --the schema name
+ '.' + OBJECT_NAME(fk.parent_object_id) --the table name
+ '(' + fk.name + ')' AS path, --the name of the foreign key
CONVERT(VARCHAR(100), COALESCE(ep.value, '')) AS Description, --the extended property as string
CONVERT(BIT,0) AS 'Column-based'
FROM sys.foreign_keys fk
LEFT OUTER JOIN sys.extended_properties ep
ON ep.major_id = fk.object_id
AND ep.class = 1
AND ep.name = 'MS_Description'
AND ep.minor_id = 0
WHERE OBJECTPROPERTYEX(fk.object_id, 'CnstIsColumn') = 0
AND fk.parent_object_id = OBJECT_ID('Sales.SalesOrderDetail');
The query returned two rows when I ran it; the first row was for a column-level constraint and the second was for a table-level constraint.
Check Constraints
We can do the same thing to get a close look at the check constraints. With the default and check constraints you’d normally wish to see the definition as well as the extended property. This just means adding the ‘definition’ field. (foreign keys don’t have a definition, of course). Here again, I’ve made the query rather more loquacious than strictly necessary so you can cut and paste as necessary.
SELECT --firstly we'll do the column check constraints
OBJECT_SCHEMA_NAME(cc.parent_object_id) --the schema name
+ '.' + OBJECT_NAME(cc.parent_object_id) --the table name
+ '.' + col.name + '(' + cc.name + ')' AS path, --the names of column and check constraint
CONVERT(VARCHAR(100), COALESCE(ep.value, '')) AS Description, --the extended property as string
CONVERT(BIT,1) AS 'Column-based'
FROM sys.check_constraints cc --the check constraints are listed here, together with details
INNER JOIN sys.columns col
ON cc.parent_object_id = col.object_id
AND cc.parent_column_id = col.column_id
LEFT OUTER JOIN sys.extended_properties ep
ON ep.major_id = cc.object_id
AND ep.class = 1
AND ep.name = 'MS_Description'
AND ep.minor_id = 0
WHERE OBJECTPROPERTYEX(cc.object_id, 'CnstIsColumn') = 1
AND cc.parent_object_id = OBJECT_ID('production.BillOfMaterials')
UNION ALL
SELECT --then we do the table-level constraints
OBJECT_SCHEMA_NAME(cc.parent_object_id) --the schema name
+ '.' + OBJECT_NAME(cc.parent_object_id) --the table name
+ '(' + cc.name + ')' AS path, --the name of the check
CONVERT(VARCHAR(100), COALESCE(ep.value, '')) AS Description, --the extended property as string
CONVERT(BIT,0) AS 'Column-based'
FROM sys.check_constraints cc --the check constraints are listed here, together with details
LEFT OUTER JOIN sys.extended_properties ep
ON ep.major_id = cc.object_id
AND ep.class = 1
AND ep.name = 'MS_Description'
AND ep.minor_id = 0
WHERE OBJECTPROPERTYEX(cc.object_id, 'CnstIsColumn') = 0
AND cc.parent_object_id = OBJECT_ID('production.BillOfMaterials');
Notice that class=7 and the minor_id is matched with the index_id. The output below shows some of the 175 rows I got back:
Indexes
Tables also have indexes, of course, and these can and should have documentation attached to them. This code will list all the indexes in the database along with any description. You can, of course refine it to select a particular table.
SELECT --indexes OBJECT_SCHEMA_NAME(ix.object_id)+'.'+OBJECT_NAME(ix.object_id)+'.'+ix.name AS Path, CONVERT(VARCHAR(100), COALESCE(ep.value, '')) AS Description --the extended property as string FROM sys.indexes ix LEFT OUTER JOIN sys.extended_properties ep ON ep.major_id=ix.OBJECT_ID AND class=7 AND ep.name = 'MS_Description' AND ep.minor_id=ix.index_id WHERE type <>0 --don't include heaps AND OBJECTPROPERTYEX(ix.object_id, 'IsMsShipped') = 0;
Notice that class=7 and the minor_id is matched with the index_id. The output below shows some of the 175 rows I got back:
Descriptions of Unique and Primary Key Constraints
Unique and primary key constraints are some of the first things you usually check in a database. I always like to know whether they are column-based or table based when I’m reading the descriptions. Often, tables gain primary keys by the simple use of the phrase PRIMARY KEY after the column definition to define the constraint. Again, a single-column candidate key is constrained by a UNIQUE key constraint in the definition of the column. That is perfectly good practice and makes life less tedious for the developer. It is nice to see immediately whether a key constraint is multi-column or single column. This complicates the code a little bit, but then I’ve done it for you so you don’t have to.
SELECT --Unique and Primary keys OBJECT_SCHEMA_NAME(IndexColumns.object_id) + '.' + OBJECT_NAME(IndexColumns.object_id) + '.' --if the index is on a single column then show it as a column-based constraint + CASE WHEN IndexColumns.colcount = 1 THEN COL_NAME(IndexColumns.object_id, IndexColumns.mincol) + '.' ELSE '' END + kc.name AS Path, IndexColumns.colcount AS columns, kc.type, CONVERT(VARCHAR(100), COALESCE(ep.value, '')) AS Description --the extended property as string FROM sys.key_constraints kc INNER JOIN (SELECT COUNT(*) AS colcount, --the number of columns in the index MIN(index_columns.column_id) AS mincol, --the first column index_columns.object_id, --the table that is indexed index_columns.index_id --the index within the table FROM sys.index_columns GROUP BY index_columns.object_id, index_columns.index_id ) IndexColumns ON kc.parent_object_id = IndexColumns.object_id AND kc.unique_index_id = IndexColumns.index_id LEFT OUTER JOIN sys.extended_properties ep ON ep.major_id = kc.object_id AND ep.class = 1 AND ep.name = 'MS_Description';
This code will give you something like the following resultswith AdventureWorks. (My query returned 72 rows.)
Parameters
We started with a routine that listed all your database objects, including any extended properties. We then focussed our attention on the details of tables mainly because most of the complexity of using extended properties lies with tables. However, there are other objects that have attributes that can and should have descriptions provided by extended properties. The most obvious one is parameters, which a number of different types of objects can have. Here you can list out all the objects with parameters in a database along with their discription.
SELECT --Parameters OBJECT_SCHEMA_NAME(par.object_id) + '.'+OBJECT_NAME(par.object_id)+'.'+par.name AS Name, CONVERT(VARCHAR(100), COALESCE(ep.value, '')) AS Description --the extended property as string FROM sys.parameters par LEFT OUTER join sys.extended_properties ep ON ep.major_id=par.Object_id AND class=2 AND ep.minor_id=par.parameter_id WHERE par.parameter_id>0 --don't include the return value AND OBJECTPROPERTYEX(par.object_id, 'IsMsShipped') = 0;
This will give you the following result (I got 49 rows).
Seeing what extended properties exist for a database.
At this point, you will be wondering whether there is a way of just getting a view of all the existing documentation and descriptions for your database.. So far, we’ve taken the approach of listing everything that exists that can have documentation, whether or not anyone has put the documentation in. We are going to now just list just everything that actually already has documentation. This involves quite a long query, because so many different things can be documented; but it is modular. I’ve introduced another column, which tells us what sort of ‘thing’ it is that has a description. This is important to know when it comes to updating the description, but you’ll need to read the article ‘Reading, Writing, and Creating SQL Server Extended Properties’ for more information about that.
SELECT --we start off by listing objects AND columns that are documented CASE WHEN ob.parent_object_id > 0 THEN OBJECT_SCHEMA_NAME(ob.parent_object_id) + '.' + OBJECT_NAME(ob.parent_object_id) + '.' + ob.name ELSE OBJECT_SCHEMA_NAME(ob.object_id) + '.' + ob.name END + CASE WHEN ep.minor_id > 0 THEN '.' + col.name ELSE '' END AS path, 'schema' + CASE WHEN ob.parent_object_id > 0 THEN '/table' ELSE '' END + '/' +CASE WHEN ob.type_desc LIKE '%constraint%' THEN CASE WHEN OBJECTPROPERTYEX(ob.object_id, 'CnstIsColumn') = 1 THEN 'column_level_' ELSE 'table_level_'END ELSE '' END +CASE WHEN ob.type IN ( 'TF', 'FN', 'IF', 'FS', 'FT' ) THEN 'function' WHEN ob.type IN ( 'P', 'PC', 'RF', 'X' ) THEN 'procedure' WHEN ob.type IN ( 'U', 'IT' ) THEN 'table' WHEN ob.type = 'SQ' THEN 'queue' ELSE LOWER(ob.type_desc) END + CASE WHEN col.column_id IS NULL THEN '' ELSE '/column' END AS thing, ep.value FROM sys.extended_properties ep INNER JOIN sys.objects ob ON ep.major_id = ob.object_id AND ep.class = 1 LEFT OUTER JOIN sys.columns col ON ep.major_id = col.object_id AND ep.class = 1 AND ep.minor_id = col.column_id AND ep.name = 'MS_Description' UNION ALL SELECT --indexes OBJECT_SCHEMA_NAME(ix.object_id) + '.' + OBJECT_NAME(ix.object_id) + '.' + ix.name, 'schema/' + CASE WHEN OBJECTPROPERTYEX(ix.object_id, 'BaseType') = 'U' THEN 'table' ELSE 'view' END + '/index', ep.value FROM sys.extended_properties ep INNER JOIN sys.indexes ix ON ep.major_id = ix.object_id AND ep.class = 7 AND ep.minor_id = ix.index_id WHERE ep.name = 'MS_Description' UNION ALL SELECT --Parameters OBJECT_SCHEMA_NAME(ob.object_id) + '.' + OBJECT_NAME(ob.object_id) + '.' + par.name, 'schema/' + LOWER(ob.type_desc) + '/parameter', ep.value FROM sys.extended_properties ep INNER JOIN sys.objects ob ON ep.major_id = ob.object_id AND ep.class = 2 INNER JOIN sys.parameters par ON ep.major_id = par.object_id AND ep.class = 2 AND ep.minor_id = par.parameter_id WHERE ep.name = 'MS_Description' UNION ALL SELECT --schemas sch.name, 'schema', ep.value FROM sys.extended_properties ep INNER JOIN sys.schemas sch ON ep.class = 3 AND ep.name = 'MS_Description' AND ep.major_id = sch.schema_id UNION ALL --Database SELECT DB_NAME(), '', ep.value FROM sys.extended_properties ep WHERE ep.class = 0 UNION ALL --XML Schema Collections SELECT SCHEMA_NAME(xc.schema_id) + '.' + xc.name, 'schema/xml_Schema_collection', ep.value FROM sys.extended_properties ep INNER JOIN sys.xml_schema_collections xc ON ep.class = 10 AND ep.name = 'MS_Description' AND ep.major_id = xc.xml_collection_id UNION ALL SELECT --Database Files df.name, 'database_file', ep.value FROM sys.extended_properties ep INNER JOIN sys.database_files df ON ep.class = 22 AND ep.name = 'MS_Description' AND ep.major_id = df.file_id UNION ALL SELECT --Data Spaces ds.name, 'dataspace', ep.value FROM sys.extended_properties ep INNER JOIN sys.data_spaces ds ON ep.class = 20 AND ep.name = 'MS_Description' AND ep.major_id = ds.data_space_id UNION ALL SELECT --USER dp.name, 'database_principal', ep.value FROM sys.extended_properties ep INNER JOIN sys.database_principals dp ON ep.class = 4 AND ep.name = 'MS_Description' AND ep.major_id = dp.principal_id UNION ALL SELECT --PARTITION FUNCTION pf.name, 'partition_function', ep.value FROM sys.extended_properties ep INNER JOIN sys.partition_functions pf ON ep.class = 21 AND ep.name = 'MS_Description' AND ep.major_id = pf.function_id UNION ALL SELECT --REMOTE SERVICE BINDING rsb.name, 'remote service binding', ep.value FROM sys.extended_properties ep INNER JOIN sys.remote_service_bindings rsb ON ep.class = 18 AND ep.name = 'MS_Description' AND ep.major_id = rsb.remote_service_binding_id UNION ALL SELECT --Route rt.name, 'route', ep.value FROM sys.extended_properties ep INNER JOIN sys.routes rt ON ep.class = 19 AND ep.name = 'MS_Description' AND ep.major_id = rt.route_id UNION ALL SELECT --Service sv.name COLLATE DATABASE_DEFAULT, 'service', ep.value FROM sys.extended_properties ep INNER JOIN sys.services sv ON ep.class = 17 AND ep.name = 'MS_Description' AND ep.major_id = sv.service_id UNION ALL SELECT -- 'CONTRACT' svc.name, 'service_contract', ep.value FROM sys.service_contracts svc INNER JOIN sys.extended_properties ep ON ep.class = 16 AND ep.name = 'MS_Description' AND ep.major_id = svc.service_contract_id UNION ALL SELECT -- 'MESSAGE TYPE' smt.name, 'message_type', ep.value FROM sys.service_message_types smt INNER JOIN sys.extended_properties ep ON ep.class = 15 AND ep.name = 'MS_Description' AND ep.major_id = smt.message_type_id UNION ALL SELECT -- 'assembly' asy.name, 'assembly', ep.value FROM sys.assemblies asy INNER JOIN sys.extended_properties ep ON ep.class = 5 AND ep.name = 'MS_Description' AND ep.major_id = asy.assembly_id /*UNION ALL SELECT --'CERTIFICATE' cer.name,'certificate', value from sys.certificates cer INNER JOIN sys.extended_properties ep ON class=? AND ep.name = 'MS_Description' AND ep.major_id=cer.certificate_id UNION ALL SELECT --'ASYMMETRIC KEY' amk.name,'asymmetric_key', value SELECT * from sys.asymmetric_keys amk INNER JOIN sys.extended_properties ep ON class=? AND ep.name = 'MS_Description' AND ep.major_id=amk.asymmetric_key_id SELECT --'SYMMETRIC KEY' smk.name,'symmetric_key', value from sys.symmetric_keys smk INNER JOIN sys.services sv ON class=? AND ep.name = 'MS_Description' AND ep.major_id=smk.symmetric_key_id */UNION ALL SELECT -- 'PLAN GUIDE' pg.name, 'plan_guide', ep.value FROM sys.plan_guides pg INNER JOIN sys.extended_properties ep ON ep.class = 27 AND ep.name = 'MS_Description' AND ep.major_id = pg.plan_guide_id ORDER BY thing,path;
This rather long query gives you a very informative result, assuming that someone has bothered to add the appropriate MS_Descriptions Extended properties! In the case of my AdventureWorks database, it returns 1232 rows, which is a lot of information.
Conclusions
In this stairway level I hope I’ve shown you everything necessary to view database objects and things that can have extended properties assigned to them, and if they have such properties, what they are. I haven’t tried to show you how to use them, or even how to write to them. For writing to them I use SQL Doc, a tool by Redgate which makes it all pretty civilized by displaying every piece of metadata to which a description can be applied, and providing an entry text box to allow you to do so. If you are stuck with SSMS, it is still possible, but there is no joy in the task. To do it via a stored procedure is tiresome, and requires you to know a lot about the object you are attaching an extended-property sticky note to.
Once you have extended properties, they make a lot of difference to many database tasks, but that is a topic for another article.
