How about using extended properties instead?

This approach will use field names from extended properties, or the original field name if the property is missing:

create table customer
  ( Cust_cd char(4) not null primary key
  , Cust_lnm varchar(50)
  , Cust_fnm varchar(50)
)
GO

CREATE VIEW vwReport1 AS
SELECT cust_cd, cust_lnm, cust_fnm FROM customer
GO

exec sp_addextendedproperty 'INTERNAL_ELEMENT_LABEL', '"Customer Code"', 'USER', 'dbo', 'VIEW', 'vwReport1', 'COLUMN', 'cust_cd'
exec sp_addextendedproperty 'CLIENT_ELEMENT_LABEL', 'Code', 'USER', 'dbo', 'VIEW', 'vwReport1', 'COLUMN', 'cust_cd'
exec sp_addextendedproperty 'INTERNAL_ELEMENT_LABEL', '"Customer Last Name"', 'USER', 'dbo', 'VIEW', 'vwReport1', 'COLUMN', 'cust_lnm'
exec sp_addextendedproperty 'CLIENT_ELEMENT_LABEL', '"Last Name"', 'USER', 'dbo', 'VIEW', 'vwReport1', 'COLUMN', 'cust_lnm'
exec sp_addextendedproperty 'INTERNAL_ELEMENT_LABEL', '"Customer First Name"', 'USER', 'dbo', 'VIEW', 'vwReport1', 'COLUMN', 'cust_fnm'
exec sp_addextendedproperty 'CLIENT_ELEMENT_LABEL', '"First Name"', 'USER', 'dbo', 'VIEW', 'vwReport1', 'COLUMN', 'cust_fnm'
GO

CREATE PROC REPORT1
   @USERLABEL CHAR(1) = 'N'
AS
BEGIN

SET NOCOUNT ON
declare @cmd nvarchar(4000), @property nvarchar(100)

set @property = CASE @USERLABEL WHEN 'N' THEN 'INTERNAL_ELEMENT_LABEL' ELSE 'CLIENT_ELEMENT_LABEL' END

select @cmd = ISNULL(@cmd + ',','') + rtrim(column_name) + ' AS ' + ISNULL(cast(value as varchar), rtrim(column_name))
from information_schema.columns a
left join ::fn_listextendedproperty(@property, 'user', 'dbo', 'view', 'vwReport1', 'COLUMN', NULL) b on a.column_name = b.objname
where a.table_name = 'vwReport1'
order by ordinal_position

set @cmd = 'SELECT ' + @cmd + ' FROM vwReport1'
exec (@cmd)

SET NOCOUNT OFF
END
go

exec report1 'n'
exec report1 'y'

I forgot to mention that this uses extended properties tied to each view column, so different views can apply different labels to the same column name.

Also, the report procedure could accept the view name as a second parameter so that only one procedure is needed to run any number of reports.  Only the views and extended properties must be defined to create a new report.

It is good if you have simple select. But with extended properties:

1. Change is more complicated

2. Flexebility is limited

If more complicated stored procedure is created with temp tables, temp variables, calculated values then extended properties are useless. But data dictionary table anyway available.

Sorry, previous post is mine as well

It is good if you have simple select. But with extended properties:

1. Change is more complicated

2. Flexebility is limited

If more complicated stored procedure is created with temp tables, temp variables, calculated values then extended properties are useless. But data dictionary table anyway available.

I attached the label properties to views to allow the flexibility to include computed columns and other complex queries.  If you wanted to get more creative you could use this scheme to relable the columns of a table-returning function.

If you want to use the data dictionary approach to relable the fields of the results returned by a stored procedure, the data dictionary logic has to be built into every stored procedure.  If you use some imagination you could figure out how to do the same with extended properties.  There is probably some level of complexity where this approach becomes too difficult to implement, but I don't think delivering static reports to Access (see original article) will reach that level.

The main advantage is that the labels are attached to a specific field of a specific view.  There is no confusion about which label belongs to which identically-named column.  If columns or entire views are dropped, their properties go away.  If columns are renamed, their properties are retained.  Keeping the data dictionary in sync with schema changes, and knowing which data dictionary entries are obsolete, sounds like a maintenance nightmare.

In some cases the data dictionary approach may be easier.  If the 200 databases mentioned in the article have similar structures, with many fields using the same labels in each, one dictionary would probably be easier to manage.  On the other hand, if the 5-6 new databases created each month use an existing database as a template, it is fairly easy to have extended properties scripted and copied to the new database.

You say more complicated and less flexible, I say more organized, robust, and self-documenting.  The flexibility is limited only by your imagination, and as for complicated, anyone maintaining over 200 databases should be able to creatively query the sysproperties table to generate all the sp_addextendedproperty commands they need.  I think the choice would depend on the amount of maintenance each approach required.

You may be right, but unfortunately, we using extended properties for the applications. So, we already have 20-25 properties on each table (4-5 per column). To create some more (especially for the existing databases) will require revalidation (FDA requirements). Single data dictionary table in stand along database gives me a lot of flexibilities without messing up with existing environment. Anyway thanks for the advice