Renaming generic column names with friendly names in a lookup table

Database
Fan

Renaming generic column names with friendly names in a lookup table

Table A  contains millions of transacions with hundreds of columns.  Here is a snippet of the generic Column Names.

ACCT_NUM

NAME_KEY_XID                   
FLOAT_1                      
FLOAT_2                      
FLOAT_3                       

STR64_1                      
STR64_2    

INT_1                        
INT_2                        
INT_3                     
             

 

Table B: is a reference table  with 600 rows that define the friendly name of the COLUMN_XID.  Example:    

Friendly_NAME_XID             NAME_KEY_XID          COLUMN_XID
_DBT_RollStart_24HrDtTm  20160909111101             FLOAT_1
_ddaLedgerBalEma              20160909111101             STR64_1 
      

QUESTION:  How do I rename Table A: Column Names with Table B: Friendly_Name_XIDs? Keep in mind there are hundreds of column names.   I need a systematic solution that uses the Friendly Name KEY to tie the columns together.  Any suggestions?

 

 

 

 

 

4 REPLIES
Junior Contributor

Re: Renaming generic column names with friendly names in a lookup table

Who created such a "data model"?

How do the users know about the actual names, do they look it up every time?

 

What do you mean by "rename"?

Actually renaming the table columns or aliasing within a Select?

 

For rename you must run ALTER TABLE for every column (iirc you can't rename indexed columns).

 

For aliases there's no other way than Dynamic SQL replacing the stupid names with the friendly names.

 

Any generic solution will be based on a Stored Procedure.

Fan

Re: Creating alias columns by linking to " friendly names " in a lookup table

Yes, I meant creating aliasing within a Select (not rename the column in the table- sorry!).   Currently, all end users are required to reference a cheat sheet to manually decode  Table A columns with aliases. 

 

I assumed DBAs  were playing a cruel joke on us by providing  "friendly name" Table B to interpret the columns of table A. Nothing friendly about it. Apparently, if a column defitinion changes, a new row with a datestamp in the key gets updated in Table B.  

 

 

To compound the complexity, a few columns in Table A are concatenated with a mixture of fixed length dates, strings and integers that require a substring and casting in every query.  The idea was to save space and cram as much transactional data into one field as possible.

 

Good intentions gone bad in  my humble opinion.  I will search for examples on how to write a procedure. 

 

Thanks!  

Junior Contributor

Re: Creating alias columns by linking to " friendly names " in a lookup table

It should be possible to create a view for each base table to map the names and split the concatenated columns. As a generic solution it's done in a SP and whenever a definition changes it must be called again.
Fan

Re: Creating alias columns by linking to " friendly names " in a lookup table

Thank you very much!