Solved

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

Posted on 2004-09-07
11
287 Views
Last Modified: 2012-06-21
I'd like to have 2 sets of column names for a particular table.  One set will include long, descriptive titles and the other will use short, Reporting Services/Report friendly/Spaceless column names.  How can I do this (I have approx 800 columns)? Is there a way I can use the 'Description' feature located when you 'Design' a table in Enterprise Manager to assign either the long or short name and then have it appear as the column name?

Ideas appreciated.
0
Comment
Question by:aaron240
  • 4
  • 3
  • 2
  • +2
11 Comments
 
LVL 11

Expert Comment

by:Otana
Comment Utility
I believe you can retrieve the description from a column from the syscolumns table.
0
 
LVL 17

Expert Comment

by:BillAn1
Comment Utility
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


0
 
LVL 17

Expert Comment

by:BillAn1
Comment Utility
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
0
 
LVL 34

Expert Comment

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

Brett
0
 

Author Comment

by:aaron240
Comment Utility
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...
0
Get up to 2TB FREE CLOUD per backup license!

An exclusive Black Friday offer just for Expert Exchange audience! Buy any of our top-rated backup solutions & get up to 2TB free cloud per system! Perform local & cloud backup in the same step, and restore instantly—anytime, anywhere. Grab this deal now before it disappears!

 
LVL 17

Expert Comment

by:BillAn1
Comment Utility
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.
0
 
LVL 15

Expert Comment

by:jdlambert1
Comment Utility
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'
0
 
LVL 15

Expert Comment

by:jdlambert1
Comment Utility
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.
0
 

Author Comment

by:aaron240
Comment Utility
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.

0
 
LVL 17

Accepted Solution

by:
BillAn1 earned 500 total points
Comment Utility
No, I think you are missing the point of the suggestion. It is not to create 2 seperate tables - or to use Dynamic SQL to query your table. It is to create a view on the table with  different names.

Because of the large size of your table, I suggested you use Dynamic SQL to create the view in the first instance, but this is a one-off task.
Also, you asked whether it was possible to generat the alternate names from the descriptions which you have already created? The dynamic SQL would help in generating this for you.
If you have not already created the descriptions, and just wat to create an alternate set of descriptions, then you can simply do it creating a view. Once done, you have the view which you can query as if it was seperate table.
It is 'easily possible'. Certainly a lot easier than manually creating a table with 800 columns....and there is no maintenance, once done. If you create two tables, how will you be able to keep them in sync?

0
 

Author Comment

by:aaron240
Comment Utility
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).

0

Featured Post

6 Surprising Benefits of Threat Intelligence

All sorts of threat intelligence is available on the web. Intelligence you can learn from, and use to anticipate and prepare for future attacks.

Join & Write a Comment

When you hear the word proxy, you may become apprehensive. This article will help you to understand Proxy and when it is useful. Let's talk Proxy for SQL Server. (Not in terms of Internet access.) Typically, you'll run into this type of problem w…
This article explains how to reset the password of the sa account on a Microsoft SQL Server.  The steps in this article work in SQL 2005, 2008, 2008 R2, 2012, 2014 and 2016.
This video shows, step by step, how to configure Oracle Heterogeneous Services via the Generic Gateway Agent in order to make a connection from an Oracle session and access a remote SQL Server database table.
This videos aims to give the viewer a basic demonstration of how a user can query current session information by using the SYS_CONTEXT function

772 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

8 Experts available now in Live!

Get 1:1 Help Now