Table A contains millions of transacions with hundreds of columns. Here is a snippet of the generic Column Names.
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?
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.
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.