Solved

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

Posted on 2004-09-07
11
290 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
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
Migrating Your Company's PCs

To keep pace with competitors, businesses must keep employees productive, and that means providing them with the latest technology. This document provides the tips and tricks you need to help you migrate an outdated PC fleet to new desktops, laptops, and tablets.

 
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 500 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

PRTG Network Monitor: Intuitive Network Monitoring

Network Monitoring is essential to ensure that computer systems and network devices are running. Use PRTG to monitor LANs, servers, websites, applications and devices, bandwidth, virtual environments, remote systems, IoT, and many more. PRTG is easy to set up & use.

Question has a verified solution.

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

Suggested Solutions

International Data Corporation (IDC) prognosticates that before the current the year gets over disbursing on IT framework products to be sent in cloud environs will be $37.1B.
The Delta outage: 650 cancelled flights, more than 1200 delayed flights, thousands of frustrated customers, tens of millions of dollars in damages – plus untold reputational damage to one of the world’s most trusted airlines. All due to a catastroph…
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 the fundamental information of how to create a table.

810 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