How to create a field name table and use it? SQL Server

Question

I have a database in SQL Server.

Every single field in the entire database is named like this 'F01, F02, F03..." all the way to 'F25000'

It's difficult to work with this database because I either have to guess what the field is by the data contained within the field, or I have to look up what the field definition is in an external CSV file.

I have a CSV file that has every single field defined with an alpha numeric description.

Can I create a table, maybe called "Fields", then add the Field and Description columns from the CSV file to that table? Then I'd like to refer to this Fields table in future queries where the column headers are replaced by the alpha numeric definition field found in the newly created Fields table? I think this is all possible.

How do I create the table and add the contents of the csv file to that table? CSV file contains just two columns, Field Number and Description.

Then how do I write the query to refer back to the fields table that I created? I just want to select * from the other tables in the database but replace the column headers with the field definitions.

Or create a view which maps to the proper names from the CSV, and write your queries against the view. — Mitch
– Mitch, Commented Aug 18, 2015 at 14:16
I assume you have a good reason for this database structure. Can you give any hints why it is so designed? — Gordon Linoff
– Gordon Linoff, Commented Aug 18, 2015 at 14:19
It's not a database that I designed, just one I support. There is no way I can change the database design. I just want to be able to refer to tables with the columns designed properly when I am trouble shooting and designing new queries and reports and such. I am ok with having one "defined" query per table that I refer to when I need it. — user1837575
– user1837575, Commented Aug 18, 2015 at 14:22
Use your CSV to build DDL to create views? You could probably easily use excel to build the F01 as <useful name> clauses. Assuming your alpha numeric descriptions don't have spaces. — Andrew
– Andrew, Commented Aug 18, 2015 at 14:24

Gordon Linoff · Accepted Answer · 2015-08-18 14:26:14Z

First, load the CSV file into a table. That will help.

The second problem is harder to solve. One method would be to take all existing tables (and with 25,000 fields, you have a lot of tables), and construct views for accessing them. Something like this:

select 'create view v_' + t.table_name +
       stuff((select cast(', ' + c.column_name + ' as [' + f.description + ']' as varchar(max))
              from information_schema.columns c join
                   fieldsname f
                   on f.field = c.column_name
              where c.table_name = t.table_name
              for xml path (''), type
              ).value('', 'varchar(max)'
                     ), 1, 1, ''
             ) as viewdef
from information_schema.tables t
where exists (select 1 from information_schema.columns where column_name like 'F[0-9]%');

(After debugging), you can run the generated code to get a view for each table that should make your life much easier.

Collectives™ on Stack Overflow

How to create a field name table and use it? SQL Server

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related