Select data from table with column name and description SQL Server

Question

I have a legacy table with all column named in an old way, the names don't make sense to others, but the table description contains column description, how to can select all data from the table, and combine with the column description?

UPDATED BELOW:

To get the Names and Columns Description

SELECT 
 COLUMN_NAME AS Name, 
 COLUMN_TEXT AS Description
FROM 
 [DB2-LINKED-SERVER].[BD2].QSYS2.SYSCOLUMNS 
WHERE  
 TABLE_NAME = 'ITMHED'

I got:

Name      Description
ITMNO     Item Number
ITMNM     Item Name
 .... 800+ rows more

Then I have another query:

SELECT * FROM [DB2-LINKED-SERVER].[BD2].ITMHED

It returned me:

ITMNO      ITMNM           ...800+ more columns
AB-001     Mountain Bike             ....

What I want to get:

Item Number      Item Name      ...800+ more columns
AB-001           Mountain Bike     .....

If I need only 2-3 column, I can manually rename them, but with that many record, I want to make it more readable for users. I need to generate a report from that.

I can't rename the columns. It's an old IBM BD2 database, don't know if it has views. — triston, Oct 08 '14 at 18:13
I am writing SQL query for it on sql management studio with t linked server to the DB2. — triston, Oct 08 '14 at 18:20
So...create a view in SQL server and users can get the data there. — Mr. Mascaro, Oct 08 '14 at 18:27
If others are going to be running your query, use alias names. — Dan Bracuk, Oct 08 '14 at 18:29

Drew · Answer 1 · 2014-10-08T21:06:51.077

1

SELECT 
 COLUMN_NAME AS Name + ' as '+ 
 COLUMN_TEXT AS Description + ','
FROM 
 [DB2-LINKED-SERVER].[BD2].QSYS2.SYSCOLUMNS 
WHERE  
 TABLE_NAME = 'ITMHED'

Could get the output from that and then insert it into the following:

select (insert the output from above here) from [DB2-LINKED-SERVER].[BD2].ITMHED

edited Oct 08 '14 at 21:06

answered Oct 08 '14 at 19:17

Drew

2,583
5
36
54

but there are over 1000 columns need description. – triston Oct 08 '14 at 20:15
yes, I already had the code to generate the columns names and description from a select statement to QSYS2.SYSCOLUMNS. but they are rows of column names, and description. I couldn't configure out a way to link the table to this list. – triston Oct 08 '14 at 20:26
Cool. If this answered your initial question or helped, would really love if you selected it as the answer – Drew Oct 08 '14 at 20:28
not really, I already got the column names and description. just can't link it to another select statement to the table I want to retrieve data from. but I will mark your answer as useful. thanks. – triston Oct 08 '14 at 20:32
Ahh gotcha. And thank you sir! Also, and if you know the column names and the table where those columns are and the alias you want to use, what issue are you having retrieving the data/mind maybe posting the select statement you are trying to use? – Drew Oct 08 '14 at 20:36
So if you're trying to get data from a table and use alias' for the column names based on information stored in another table, wouldn't that be all the information you would need to construct the proper statement? – Drew Oct 08 '14 at 20:38
Let us [continue this discussion in chat](http://chat.stackoverflow.com/rooms/62704/discussion-between-drew-and-triston). – Drew Oct 08 '14 at 21:02

Select data from table with column name and description SQL Server

1 Answers1