SOLVED! Get Database Column Names

[mightycpa]

Found a solution here: viewtopic.php?f=2&t=7000&p=30839&hilit= ... mes#p30839

Hi,

I know that if you query a DB, you can get results back like this:

variablename_1_FIELD1
variablename_1_FIELD2
variablename_1_FIELD3

My question is, can you programatically turn those column names into variable values somehow?

Thanks

[JRL]

I don't have a pat answer just some possibilities. I had the same problem and figured out how in my situation a couple years back.

Depending on the database you're querying there might be a trick to get the column names. For example see THIS on stackoverflow.com. I have a Pervasive database that I deal with at work and there is one special table that contains all the table names and each of the column name in each table.

Sometimes the connection string asks if you have headers or no headers. If that is the case for you you might say "no headers" then the column names show up as the first set of variables in the array.

I can't test it right now but I think that the VBScript Marcus posted HERE will write the column names when it posts the requested data to file. If it does you could use it then read line one from the file and Separate by comma to get each column name.

[mightycpa]

Thanks JRL,

I'm writing a script that allows me to restore data from a limited set of tables in one environment (PROD) to another (DEV). In this case, we're using SQL Server, so there are other way to get the table names, but we also use DB2 and Oracle and other DB's, so I wanted this to be generic.

I'm doing this because I have another script that goes through all the text and SQL files in a folder, and extracts every table name used by the SQL, Stored Procedures, whatever text files in that folder. I'm going to take that output, and then feed it to this script to create a job I can run overnight that puts current data in the tables I need. The next morning, I'll be able to support whatever set of SQL I happen to be working on. I'm also going to do filters (like FY_CD = 2015) that would limit the amount of data that I'm moving, and keep the filter consistent where that column exists in any table in my restore set.

So that's the intent. Does that make sense?

What might be cool is the ability to copy one array to another, because that might take care of this, and have other uses too. I was hoping I could do something like this:

Code: Select all

Let>SEL_SQL=SELECT TOP 1 * FROM SOME TABLE
DBConnect>Provider=SQLOLEDB.1;Integrated Security=SSPI;Persist Security Info=True;Initial Catalog=BLAH..,DEV_CP7
DBQuery>DEV_CP7,%SEL_SQL%,REC_FOUND,NumRecs,NumFields,1
DBClose>DEV_CP7

ArrayDim>New_Array,%NumFields%
// wouldn't it be nice if we could copy either array element names or values
Let>VAREXPLICIT=1
Let>New_Array=REC_FOUND
Let>VAREXPLICIT=0

and on from there...

Another way that we don't have of doing it would be

Code: Select all

For

... elements in the results array...
a) to be able to iterate through a non-sequential array
and 
b) to be able to access array element names somehow

Each

which might also have additional usefulness above and beyond this.

Thanks for the suggestions, I did take the VBScript I found, and modified it just a bit to allow me to get my column names. I just tested it on SQL Server, DB2 and Oracle, and all three work without additional code, so that's goodness.

In the case of all three of those, I can write to a CSV, then run each DB's loader utility to load a CSV so that the script isn't slowed down by having to create and run a bazillion inserts one at a time to get the job done.