Multiple sets of column names and the capability of dynamic, interchangeable columns

I believe you can retrieve the description from a column from the syscolumns table.

you can get the descriptions from sysproperties, and create a view based on these.

You can do this using dynamic SQL as follows :
one major limitation is that the max length of the SQL is 8000 chars. If you have 800 cols, this will not leave you much room for descriptions. If this is a limitation, you will have to cludge a few scripts together - change the SQL to only select a certain range of cols - say
WHERE colid BETWEEN 1 and 100
then print the SQL, rather than execute it. Copy & paste the result into a window, when you have done this for each set, merge the results into a single statement and execute it.

declare @SQL varchar(8000)
set @SQL = 'create view MyExtendedView as select '

select  @SQL = @SQL + col.name + ' AS [' +  convert(varchar(1000),isnull(value, col.name)) + '], '
from syscolumns col left outer join sysproperties prop
on col.id = prop.id
and col.colid = prop.smallid
and prop.name = 'MS_Description'
where col.id = (
select id from sysobjects where xtype = 'U' and name = 'MyTable')
order by colid

set @SQL = left(@SQL, len(@SQL)-1)
set @SQL = @SQL + 'from MyTable'

print @SQL

or, maybe a bit neater, just do a select statment, then copy the results of this select statement, put a
'CREATE VIEW MyView AS SELECT ' at the front of it, and 'FROM MyTable' at the end of it, and execute it

select  col.name + ' AS [' +  convert(varchar(1000),isnull(value, col.name)) + '], '
from syscolumns col left outer join sysproperties prop
on col.id = prop.id
and col.colid = prop.smallid
and prop.name = 'MS_Description'
where col.id = (
select id from sysobjects where xtype = 'U' and name = 'MyTable')
order by colid

Agree with the above, but I would use the information_schema.columns table instead of linking to the system tables directly.....

Brett

Would it be easier if I had say a table that had the colorder, and the long and short names for the columns themselves?  Then I could possibly join the table to the 'descriptions' table which would contain both sets of names, and read in the column names off that table.  It seems like it would be more of a procedure to update the syscolumns table though...

first-off - don't update the syscolumns table! this is where SQLServer stores the schema, but it should never be madified directly. When you create a table, alter column names, add descriptions etc, the DB automatically maintains syscolumns table.
The query I give you is the way to basically link between the column names and the descriptions - it is a but ugly, but there is no real saving in you creating a seperate table, and inserting the data. In the end, you will need to creat a view anyway. There is no way to join a table to a set of descriptions as such. The way you do that is through views.

Here's the official Microsoft methods to manage table and column descriptions. For details and full syntax, see SQL Server Books Online.

sp_addextendedproperty --Adds a new extended property to a database object.
sp_updateextendedproperty --Updates the value of an existing extended property.
sp_dropextendedproperty --Drops an existing extended property.
fn_listextendedproperty --View table and column descriptions.

-------EXAMPLES-------

--Add a table description for a table named "Test"
EXEC sp_addextendedproperty @name = 'caption', @value ='This is a user description for a table',
       @level0type = 'user', @level0name = 'dbo', @level1type = 'table', @level1name = 'Test'

--Add a column description for a column named "id" in a table named "Test"
EXEC sp_addextendedproperty @name = 'caption', @value ='This is a user description for a column',
       @level0type = 'user', @level0name = dbo, @level1type = 'table', @level1name = 'Test',
       @level2type = 'column', @level2name = 'id'

--List all table descriptions for a table named "Test"
SELECT * FROM ::fn_listextendedproperty (NULL, 'user', 'dbo', 'table', 'Test', default, default)

--List all column descriptions for a table named "Test"
SELECT * FROM ::fn_listextendedproperty (NULL, 'user', 'dbo', 'table', 'Test', 'column', default)


/* The direct methods of listing descriptions (NOT RECOMMENDED due to potential future changes to system tables) */

--direct method of getting table descriptions
SELECT o.Name as TableName, p.Value as TableDescription
FROM sysobjects o JOIN sysproperties p ON o.id = p.id
WHERE o.name = 'Test' AND p.type = 3

--direct method of getting column descriptions
SELECT o.Name as TableName, c.Name as ColumnName, p.Value as ColumnDescription
FROM sysobjects o JOIN syscolumns c ON o.id = c.id JOIN sysproperties p ON c.colid = p.smallid AND c.id = p.id
WHERE o.name = 'Test'

MS_Description is a default value supplied by EM, so prop.name = 'MS_Description' won't work if you create custom names for your values. Using custom names allows you to create multiple descriptions for the same object, like for each table, you could have:

DescriptionType, Description
Caption, Customer addresses
Notes, Don't use this table for supplier addresses. Use the Supplier_Address table instead.

I definately want to avoid using dynamic sql with such a large table.  I've decided from the comments posted here that I'll just created two separate tables - one with the long name and one with the short, since nothing will really have to change over time (I'll probably make them both procedures).  For the most part I'll need the short names anyway.

What I'd hoped for was a system variable of somesort that I could toggle between a pair of column names, but since this is not easily possible I'll go about it the "long" way initially, but at a single time commitment.

Thanks for the replies.

Ok, I understand where you are coming from.  Fortunately I have the descriptions already, so what I'll do is create 2 separate views/stored procedures that will either be built periodically (procedure) or be dynamic (view).