SQL Clone
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 

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

By Phil Factor,

The Series

This article is part of the Stairway Series: Stairway to Exploring Database Metadata

In addition to the data that our clients and customers store in a database, there is a tremendous amount of meta data, 'data about data', that describes how the database is set up, configured, and what the properties are for the various objects. This stairway aims to demystify and explain how you can query and use this meta data to more effectively manage your SQL Server Databases.

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 Stairway to Exploring Database Metadata Stairway

Sign up to our RSS feed and get notified as soon as we publish a new level in the Stairway! Rss

Total article views: 893 | Views in the last 30 days: 7
 
Related Articles
SCRIPT

Conversion of rule objects to column check constraints

This script can convert the usage of bound rule objects in tables to column check constraints

FORUM

Adding identity property on primary key column

Adding identity property on primary key column

BLOG

Column and table constraints

Column and Table Constraints Constraints can be column constraints or table constraints.A column co...

FORUM

SQLDMO Column Description property

sqldmo doesn't seem to expose the DESCRIPTION property of a column. note: this can be edited in SQL...

FORUM

Where is place to store description of Columns?

Looking for table that storing information of column description

Tags
metadata    
stairway series    
 
Contribute