Two options to store user friendly column names in SQL Server

Problem

Report-writing often involves putting labels on columns for easy recognition by the end users. Sometimes these labels change as the business changes and different users adopt the system. Are there any easy ways to automate displaying user-friendly column names with a minimum amount of rework in reports? Check out this tip to learn more.

Solution

This article will review two approaches to storing user-friendly output in SQL Server itself. The first option that will be outlined is using SQL Server Extended Properties. The second option is using SQL Server Views.

---

SQL Server Extended Properties

SQL Server allows classes of documentation to be written into the database. See Using Extended Properties on Database Objects for the complete list. This tip will focus on columns and tables for use with user-readable output.
Starting with the basics, below is a script that creates a database, table and adds two column captions.

Extended Property creation code

use master go if db_id('SQLTips_UFOutput') > 0 drop database SQLTips_UFOutput go create database SQLTips_UFOutput go use SQLTips_UFOutput go create table person ( pers_id int identity(1,10) not null, pers_fname varchar(50) not null, pers_ssn varchar(12) not null, constraint PK_pers primary key (pers_id) ) insert into person values ('John', '123-45-6789'), ('Luke', '987-00-1249'), ('Janet', '232-34-3208') EXEC sp_addextendedproperty @name = N'Caption', @value = 'First name', @level0type = N'Schema', @level0name = dbo, @level1type = N'Table', @level1name = person, @level2type = N'Column', @level2name = pers_fname; GO EXEC sp_addextendedproperty @name = N'Caption', @value = 'Social Security number', @level0type = N'Schema', @level0name = dbo, @level1type = N'Table', @level1name = person, @level2type = N'Column', @level2name = pers_ssn; GO

Confirm the text is saved by calling a function with parameter values that drill down to the table.

Extended Property retrieval code
select * from fn_listextendedproperty( 'caption', N'schema', 'dbo', N'table', 'person', N'column', default )

Alright, so that gets the values into the database. How do we display them? Check out the code below.

Extended Property integration into result set

declare @dynSQL nvarchar(4000), -- SQL command the run built using the captions @colName varchar(50), -- SQL column name @colAlias varchar(50), -- Caption defined as an extendedproperty @comma bit -- flag used for proper SQL string-building declare colAlias cursor for select cast(exprop.objname as varchar), cast(exprop.value as varchar) from fn_listextendedproperty( 'caption', N'schema', 'dbo', N'table', 'person', 'column', default ) exprop -- if every column has an extended property; scroll down for a left join example inner join sys.columns syscol on cast(exprop.objname as varchar) collate SQL_Latin1_General_CP1_CI_AS = cast(syscol.name as varchar) collate SQL_Latin1_General_CP1_CI_AS -- these casts are explained below -- initialize output string set @dynSQL = 'select ' set @comma = 0 -- output each column name and alias open colAlias fetch from colAlias into @colName, @colAlias while @@fetch_status = 0 begin if @comma = 1 set @dynSQL = @dynSQL + ', ' set @dynSQL = @dynSQL + quotename(@colName) + ' as ' + quotename(@colAlias) set @comma = 1 fetch next from colAlias into @colName, @colAlias end close colAlias deallocate colAlias set @dynSQL = @dynSQL + ' from person' exec sp_executeSQL @dynSQL

The dynamic SQL above writes this statement that gets executed:

Results of dynamic SQL above
select [pers_fname] as [First name], [pers_ssn] as [Social Security number] from person

So why are there so many casts in the code above? They are used in response to this error message: Cannot resolve the collation conflict between "SQL_Latin1_General_CP1_CI_AS" and "Latin1_General_CI_AI" in the equal to operation. Good thing we're skilled mediators.
The above could be taken one step further to have the statement which writes the text "person" as a parameter and table name dynamic-generated as well. That would be all that's needed for user-friendly output once the captions have been added to tables.

---

Adding captions to every table seems redundant in cases where columns are fine as they are. This query uses a caption if there is one, otherwise the column name:

Retrieving Extended Properties or their column names when no captions exist

select coalesce(cast(exprop.objname as varchar) collate SQL_Latin1_General_CP1_CI_AS, syscol.name) as colname, coalesce(cast(exprop.value as varchar) collate SQL_Latin1_General_CP1_CI_AS, syscol.name) as colalias from sys.columns syscol left outer join fn_listextendedproperty( 'caption', N'schema', 'dbo', N'table', 'person', 'column', default ) exprop on cast(exprop.objname as varchar) collate SQL_Latin1_General_CP1_CI_AS = cast(syscol.name as varchar) collate SQL_Latin1_General_CP1_CI_AS where syscol.object_id = object_id('person')

Here are a few notes on this code:

• The source table is sys.columns which may include unneeded columns in the report.
• The coalesces simply pick the first non-null value, and because all data types in its invocation need to be the same, that 43-character cast in needed.
• To use this query, simply replace the above cursor query with it.

Suppose in looking at the output, the word "number" is too wide in "Social Security Number". Here's how to shorten the column name:

Extended Properties updating
exec sp_updateextendedproperty @name = N'Caption', @value = 'Social Security #', @level0type = N'Schema', @level0name = dbo, @level1type = N'Table', @level1name = person, @level2type = N'Column',@level2name = pers_ssn; GO

For completeness, here's how to remove an extended property:

Extended Properties deleting
exec sp_dropextendedproperty @name = N'Caption', @level0type = N'Schema', @level0name = dbo, @level1type = N'Table', @level1name = person, @level2type = N'Column',@level2name = pers_ssn; GO

One extra benefit of extended property method is their applicability to all SQL objects. For a complete list of objects, see Next Steps.

Extended Properties creation for a table
EXEC sp_addextendedproperty @name = N'Caption', @value = 'Company Personnel', @level0type = N'Schema', @level0name = dbo, @level1type = N'Table', @level1name = person; GO select * from fn_listextendedproperty(null, N'schema', 'dbo', N'table', 'person', default, default)

One unfortunate drawback of fn_listextendedproperty() is that for columns, it has to pick a specific table. For instance, the "N'table', default, default, default" parameters return all table properties. While the "N'table', default, N'column', default" parameters returns a NULL record set. This would have been ideal because creating a view with only the desired columns serves as a workaround since only relevant columns would exist.
I applaud anyone that has read this far as that is an amount of code that may need cut down to reach production given the pace of organization and level of commitment to documentation. This next method recreates the ability to have user-friendly column names with far less typing.

---

Views

If there's only an interest in column or table aliasing, it's also possible to use views as opposed to the extended properties, without as much overhead. Here is an example:

View creation for user-friendly columns
create view vuf_person as -- vuf = view user-friendly select pers_fname as [First name], pers_ssn as [Social Security number] from person go select * from vuf_person

To update the column name, simply ALTER the VIEW. Note: if a view is dropped and/or re-created, any previously issued security statements must be reassigned.

View updating for user-friendly columns
alter view vuf_person as select pers_fname as [First name], pers_ssn as [Social Security #] from person go select * from vuf_person

To emulate the object-level captioning of extended properties, name the view what's desired in the output:

View creation for user-friendly table names
create view [Personnel Report] as select pers_fname as [First name], pers_ssn as [Social Security #] from person go select * from [Personnel Report]

With this approach, user-friendly views could start with similar names, such "vuf_Personnel Report", and the reporting tools that lists available views could remove the first four characters.

Next Steps

• Decide what level of documentation to store in the database
• If only column- and table-level captions are needed, the view option is possible and requires less code
• Consider isolating new tables with user-friendly views that use human-readable aliases
• Use those views in SSRS and enjoy how the column names are user-friendly without any work
• Create an interface for users to allow updating the view column aliases that uses a dynamically-generated ALTER VIEW statement
• Read more about sp_addextendedproperty, sp_updateextendedproperty, sp_deleteextendedproperty and the complete list of extended properties
• Be creative. What else could these captions be used for? How else could they be organized? Are there any other extended properties that seem useful in your environment?