Stairway to Exploring Database Metadata

SQL Server Extended Properties: Stairway to Exploring Database Metadata Level 7

,

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.

This article is part of the parent stairway Stairway to Exploring Database Metadata

Rate

5 (3)

Share

Share

Rate

5 (3)