Two options to store user friendly column names in SQL Server

By:   |   Updated: 2011-06-29   |   Comments (16)   |   Related: > Views


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
)
sql server extened properties

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
results of dynamic sql

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')
retrieving extended properties

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
view creation for user friendly columns

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
updating for user friendly columns

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?


sql server categories

sql server webinars

subscribe to mssqltips

sql server tutorials

sql server white papers

next tip



About the author
MSSQLTips author Brent Shaub Brent Shaub has been creating, renovating and administering databases since 1998.

This author pledges the content of this article is based on professional experience and not AI generated.

View all my tips


Article Last Updated: 2011-06-29

Comments For This Article




Wednesday, December 3, 2014 - 7:43:28 PM - pharmkitteh Back To Top (35493)

Thanks Brent!  This is EXACTLY what I needed.  I am trying to automate all the old kludge at my job so staff aren't running mind-numbing jobs every X days so that they never have time to do anything else.  

Monday, December 1, 2014 - 8:14:06 AM - Shavya Back To Top (35439)

How to give alias name for trim(trailing '.' FROM trim(coalesce(column_name,'')))||'|'||

Tuesday, July 5, 2011 - 7:37:37 PM - Brent Shaub Back To Top (14134)

Hi Mark,

Yes, I was able to come up with a simple solution that I hope meets your need.  It seems that sql type variant used with is null returns null.  By casting to a varchar and trimming it, then finding that trimmed string's length, a NULL extended property value can be found.

 

select 

 

* from sys.extended_properties

where

 

len(ltrim(rtrim(cast(value as varchar)))) = 0

I would make the WHERE clause an AND (it can be in the same position in the query) so that the LEFT JOIN is still a LEFT JOIN.  Having a condition in a WHERE clause for a table that's LEFT JOINED functionally makes LEFT JOIN, INNER JOIN.

Tuesday, July 5, 2011 - 6:36:16 PM - Mark Back To Top (14133)

Kind of got this working with the following, but if I delete the description in the field (in SSMS designer) it does not return to NULL. I tried to add a clause to find empty descriptions (or prop.value = ''), but that doesn;t return the columns where I delete the description in table design view.

 

 

SET

 

 

QUOTED_IDENTIFIER

OFF

EXEC

 

 

sys.sp_MSforeachdb

"IF '?' IN ('Estimating', 'Orders')

SELECT '?' AS [Database], QUOTENAME(sch.name) + '.' + QUOTENAME(tbl.name) AS [Table], QUOTENAME(col.name) AS [Column], QUOTENAME(CAST(prop.value as nvarchar)) AS [Desc]

FROM [?].sys.tables tbl

INNER JOIN [?].sys.schemas sch ON sch.schema_id = tbl.schema_id

INNER JOIN [?].sys.columns col ON tbl.object_id = col.object_id

LEFT JOIN [?].sys.extended_properties prop ON prop.major_id = col.object_id

AND prop.minor_id = col.column_id

AND prop.class = 1

AND prop.name = 'MS_Description'

WHERE prop.value IS NULL or prop.value = ''

ORDER By 'Database', 'Table', 'Column'"

Any ideas?

Friday, July 1, 2011 - 5:33:12 AM - Brent Shaub Back To Top (14115)

I am impressed with these, Scott.  I forgot about xp_msForEachDb which appears to have made it into the supported procs in SQL 2008; that's good to know.  (I've been using SQL 2000 until a few months ago.  Eyes, eyes are adjusting to the light.)

In the second example, which I believe is a clever combination of skills, I've run into dynamic SQL strings exceeding 4000 characters, a limitation which appears to be removed with the MAX nvarchar length of SQL 2008.  Another step forward towards automation.

Thanks for these.

Thursday, June 30, 2011 - 11:32:35 AM - Scott C Back To Top (14113)

The sp_MSForeachdb procedure can do this for you.  This will return one result set for every database in the list.

SET QUOTED_IDENTIFIER OFF
EXEC sys.sp_MSforeachdb "IF '?' IN ('DB1', 'DB2', 'DB3')
SELECT  '?' AS [Database],
        QUOTENAME(sch.name) + '.' + QUOTENAME(tbl.name) AS [Table],
        QUOTENAME(col.name) AS [Column]
FROM    [?].sys.tables tbl
        INNER JOIN [?].sys.schemas sch ON sch.schema_id = tbl.schema_id
        INNER JOIN [?].sys.columns col ON tbl.object_id = col.object_id
        LEFT JOIN [?].sys.extended_properties prop ON prop.major_id = col.object_id
                                                  AND prop.minor_id = col.column_id
                                                  AND prop.class = 1
                                                  AND prop.name = 'ExtendedDescription'
WHERE   prop.value IS NULL"

To get all the results in one dataset is a little more complicated.  I've broken the query string into multiple lines so all the code will be visible in this forum, but you should remove all line breaks from the string literal before executing.  The line feed characters in the line breaks will be OK but the carriage returns will be encoded as "
" by FOR XML.  On the other hand, you could format the query any way you like and add another REPLACE to get rid of the encoded carriage returns.

SET QUOTED_IDENTIFIER OFF
DECLARE @cmd NVARCHAR(MAX)
SET @cmd = STUFF((SELECT REPLACE(" UNION ALL SELECT '?' AS [Database], 
QUOTENAME(sch.name) + '.' + QUOTENAME(tbl.name) AS [Table], 
col.name AS [Column] FROM [?].sys.tables tbl 
INNER JOIN [?].sys.schemas sch ON sch.schema_id = tbl.schema_id 
INNER JOIN [?].sys.columns col ON tbl.object_id = col.object_id 
LEFT JOIN [?].sys.extended_properties prop ON prop.major_id = col.object_id 
AND prop.minor_id = col.column_id AND prop.class = 1 AND prop.name = 'ExtendedDescription' 
WHERE prop.value IS NULL", '?', name)
FROM sys.databases WHERE name IN ('PacMed', 'SandBox') FOR XML PATH('')), 1, 11, '') 
+ ' ORDER BY [Column]'
EXEC (@cmd) 

Thursday, June 30, 2011 - 10:32:54 AM - Brent Shaub Back To Top (14112)

Hi Mark,

My understanding is that extendedProperties are in a specific database schema and that scanning extendedProperties for other database schemas is like looking for files in folders at the same level.  In other words, at a minimum, the query will need to start in master to have visibility to all the databases and their schemas.  Scott's suggestion shows finding extendedProps can be easily done within a database's schema.  My hunch is that with an outer query to grab the database names, one could combine four-part identifiers in dynamic SQL to comb a server for extended properties (or a lack thereof).

Thursday, June 30, 2011 - 9:46:44 AM - Mark Back To Top (14111)

Can anyone suggest some code to list all comuns for all tables of a names set of db's that are missing extended descriptions - pseudo code

 

SELECT ColumnName FROM AllTables WHERE DBName in ('DB1', 'DB2', 'DB3') AND ExtendedDescription IS NULL

Thursday, June 30, 2011 - 9:27:55 AM - Brent Shaub Back To Top (14110) 
Hi Scott,
Notepad worked with the caveat of the width > 600px which only affected the one line.
Thanks again for posting your code, and confirming I reviewed it correctly.
I believe the stuff(for xml path) is a notable improvement over the cursor.

Thursday, June 30, 2011 - 9:25:08 AM - Scott C Back To Top (14109)

That looks good (other than cropping the longer lines), but still gets mangled if you copy&paste to Management Studio.  I had better luck using View Source and copying it from there.  Then you can restore the original spaces and line breaks by replacing the HTML tags.

Thursday, June 30, 2011 - 9:04:03 AM - Scott C Back To Top (14108)

Your analysis is correct.  Your reformatting is close but I'm going to try posting the code again.  This time I copied it to Notepad first to convert it to plain text.

DECLARE @tbl NVARCHAR(500) = 'dbo.person'
DECLARE @cmd NVARCHAR(4000)
-- Show all columns
SET @cmd = 'SELECT '+ STUFF((
         SELECT ',' + ISNULL(QUOTENAME(CAST(ext.value AS VARCHAR(256))) COLLATE DATABASE_DEFAULT + '=', '')
                 + QUOTENAME(col.name)
         FROM sys.columns col
         LEFT JOIN sys.extended_properties ext ON ext.major_id = col.object_id AND ext.minor_id = col.column_id AND ext.name = 'Caption'
         WHERE col.object_id = OBJECT_ID(@tbl)
         ORDER BY col.column_id
         FOR XML PATH('')), 1, 1, '') + ' FROM ' + @tbl
EXECUTE sys.sp_executesql @cmd
-- Show only aliased columns
SET @cmd = 'SELECT '+ STUFF((
        SELECT ',' + QUOTENAME(CAST(ext.value AS VARCHAR(256))) COLLATE DATABASE_DEFAULT
                + '=' + QUOTENAME(col.name)
            FROM sys.columns col
            INNER JOIN sys.extended_properties ext ON ext.major_id = col.object_id AND ext.minor_id = col.column_id
            WHERE col.object_id = OBJECT_ID(@tbl) AND ext.name = 'Caption'
            ORDER BY col.column_id
            FOR XML PATH('')), 1, 1, '') + ' FROM ' + @tbl
EXECUTE sys.sp_executesql @cmd

Wednesday, June 29, 2011 - 8:47:07 PM - Brent Shaub Back To Top (14106)

Bugger, it's cropping the width.

-- XML Path('') makes the result rows into a string of nodes 
	with each one's node name what's in the path('NODENAME') part
-- Having an empty string results in values butting against each other, as the < and > are not drawn; sweet
-- The select ',' precedes each value with a comma, so it's making a comma-separated list that starts with a comma
-- The STUFF replaces the first comma with an empty string
-- The dynamic SQL is to use because the table name is defined in a variable.  
	select * from @tbl will error. (Though where col1 = @val works fine.)
 

Wednesday, June 29, 2011 - 8:44:23 PM - Brent Shaub Back To Top (14105)

Thank you for the reply, Scott.  There are a handful of new ideas that I've outlined below.  Feel free to chime in if I've misunderstood anything.

-- XML Path('') makes the result rows into a string of nodes with each one's node name what's in the path('NODENAME') part
-- Having an empty string results in values butting against each other, as the < and > are not drawn; sweet
-- The select ',' precedes each value with a comma, so it's making a comma-separated list that starts with a comma
-- The STUFF replaces the first comma with an empty string
-- The dynamic SQL is to use because the table name is defined in a variable.  select * from @tbl will error. (Though where col1 = @val works fine.)
 
Your suggestions makes the code simpler and concise. I was not aware that one could say [colalias]=[col]. I had been using [col] as [colalias] exclusively.
 
 
As for the formatting, I bumped into this too.  Try setting the dropdown just above the edit textarea to Preformatted before pasting.

Wednesday, June 29, 2011 - 8:35:01 PM - Greg Robidoux Back To Top (14104)

Hi Scott, not sure what happened to the code you pasted.  I tried to reformat your code, so let me know if this is not correct code. 

DECLARE @tbl NVARCHAR(500) = 'Person.Address'
DECLARE @cmd NVARCHAR(4000)-- Show all columns
SET @cmd = 'SELECT ' + STUFF((SELECT ',' + ISNULL(QUOTENAME(CAST(ext.value AS VARCHAR(256))) COLLATE DATABASE_DEFAULT + '=', '') + QUOTENAME(col.name)         
FROM sys.columns col         
LEFT JOIN sys.extended_properties ext ON ext.major_id = col.object_id AND ext.minor_id = col.column_id AND ext.name = 'Caption'        
WHERE col.object_id = OBJECT_ID(@tbl)         
ORDER BY col.column_id         
FOR XML PATH('')), 1, 1, '') + ' FROM ' + @tbl

EXECUTE sys.sp_executesql @cmd-- Show only aliased columns

Wednesday, June 29, 2011 - 10:56:25 AM - Scott C Back To Top (14103)

Yikes, that looked like normal code when I posted it.  You'd think a SQL forum would let you post code without mangling it.

Wednesday, June 29, 2011 - 10:52:23 AM - Scott C Back To Top (14102)
The drawbacks of fn_listextendedproperty() that you noted can be addressed by using sys.extended_properties instead.
The cursor can be eliminated by using XML concatenation.
DECLARE
 @tbl NVARCHAR(500) = 'dbo.person'
DECLARE
 @cmd NVARCHAR(4000)
-- Show all columns
SET
 @cmd = 'SELECT '+ STUFF((
         SELECT ',' + ISNULL(QUOTENAME(CAST(ext.value AS VARCHAR(256))) COLLATE DATABASE_DEFAULT + '=', '')
                 +                 +
 QUOTENAME(col.name)
          FROM          FROM
 sys.columns col
          LEFT          LEFT
 JOIN sys.extended_properties ext ON ext.major_id = col.object_id AND ext.minor_id = col.column_id AND ext.name = 'Caption'
          WHERE          WHERE
 col.object_id = OBJECT_ID(@tbl)
          ORDER          ORDER
 BY col.column_id
          FOR          FOR
 XML PATH('')), 1, 1, '')
      +      +
 ' FROM ' + @tbl
EXECUTE
 sys.sp_executesql @cmd
-- Show only aliased columns
SET
 @cmd = 'SELECT '+ STUFF((
         SELECT ',' + QUOTENAME(CAST(ext.value AS VARCHAR(256))) COLLATE DATABASE_DEFAULT
                   + '=' + QUOTENAME(col.name)
          FROM sys.columns col
          INNER JOIN sys.extended_properties ext ON ext.major_id = col.object_id AND ext.minor_id = col.column_id
          WHERE col.object_id = OBJECT_ID(@tbl) AND ext.name = 'Caption'
          ORDER BY col.column_id
          FOR XML PATH('')), 1, 1, '')
      + ' FROM ' + @tbl
EXECUTE
 sys.sp_executesql @cmd
 














get free sql tips
agree to terms