Solved

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

Posted on 2004-09-07
11
288 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
 
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
Best Practices: Disaster Recovery Testing

Besides backup, any IT division should have a disaster recovery plan. You will find a few tips below relating to the development of such a plan and to what issues one should pay special attention in the course of backup planning.

 
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

Is Your Active Directory as Secure as You Think?

More than 75% of all records are compromised because of the loss or theft of a privileged credential. Experts have been exploring Active Directory infrastructure to identify key threats and establish best practices for keeping data safe. Attend this month’s webinar to learn more.

Question has a verified solution.

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

Introduction SQL Server Integration Services can read XML files, that’s known by every BI developer.  (If you didn’t, don’t worry, I’m aiming this article at newcomers as well.) But how far can you go?  When does the XML Source component become …
Load balancing is the method of dividing the total amount of work performed by one computer between two or more computers. Its aim is to get more work done in the same amount of time, ensuring that all the users get served faster.
Using examples as well as descriptions, and references to Books Online, show the documentation available for date manipulation functions and by using a select few of these functions, show how date based data can be manipulated with these functions.
Viewers will learn how to use the SELECT statement in SQL and will be exposed to the many uses the SELECT statement has.

929 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

17 Experts available now in Live!

Get 1:1 Help Now