SQL: List all columns in a SQL Server table with their extended properties

I wish SQL Server had more metadata associated with columns in tables, and with objects in general. The closest thing we have to that is the use of extended properties.

Other Databases

In other databases that I've worked with, there are richer properties held for each column. For example in Progress, as well as the normal data type and nullability, there were options like:

  • Formatting mask (how this column is normally formatted)
  • Prompt (what question to automatically ask the user – gives you a chance to explain what you're asking)

and much more. This type of thing drastically cut down the amount of time it took to build applications using the database, and provided a great level of consistency.

When I first started working with SQL Server, I was struck by how little of this type of info was present.

Extended Properties

SQL Server offers extended properties. You can do this sort of thing with them, but I find the following issues:

  • Scripting tools often omit them. They not part of the core info about a column and it's easy to end up without them.
  • There aren't standards for what you put in them e.g. there's no agreed standard that says what to call a description property, or more interesting perhaps, a property that explains why an index exists.

Some databases I work with do have them though. And I wish they were easier to work with. I often want to list them. The following dynamic code will find all the columns in the DB and return basic info about them. And the pivot that follows it will provide a column in the output for each extended property name that's present. For each column, it will show the values for any of these properties.

The main image above shows the output from the WideWorldImporters database.

If you don't want the output with the properties pivoted, you could also use the code that I put in the following post.

 

Leave a Reply

Your email address will not be published. Required fields are marked *