Want to win a PS4? Go Premium and enter to win our High-Tech Treats giveaway. Enter to Win

x
?
Solved

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

Posted on 2004-09-07
11
Medium Priority
?
301 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 4
  • 3
  • 2
  • +2
11 Comments
 
LVL 11

Expert Comment

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

Expert Comment

by:BillAn1
ID: 12000444
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
ID: 12000473
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
Visualize your virtual and backup environments

Create well-organized and polished visualizations of your virtual and backup environments when planning VMware vSphere, Microsoft Hyper-V or Veeam deployments. It helps you to gain better visibility and valuable business insights.

 
LVL 34

Expert Comment

by:arbert
ID: 12000484
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
ID: 12000543
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
 
LVL 17

Expert Comment

by:BillAn1
ID: 12000581
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
ID: 12000917
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
ID: 12001084
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
ID: 12001120
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 2000 total points
ID: 12001179
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
ID: 12001384
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

Technology Partners: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Ever wondered why sometimes your SQL Server is slow or unresponsive with connections spiking up but by the time you go in, all is well? The following article will show you how to install and configure a SQL job that will send you email alerts includ…
I have a large data set and a SSIS package. How can I load this file in multi threading?
Familiarize people with the process of utilizing SQL Server functions from within Microsoft Access. Microsoft Access is a very powerful client/server development tool. One of the SQL Server objects that you can interact with from within Microsoft Ac…
Viewers will learn how to use the SELECT statement in SQL and will be exposed to the many uses the SELECT statement has.

636 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