Create VFP Script to Create VFP Table Structure like SQL

[stanlyn]

Hi,

Is there any way to create a VFP script for use in creating a new table, exactly as the original. Much like "script to file" in sqlserver.

Thanks,
Stanley

 

[Dan Freeman]

You mean you want to replicate the structure of an existing table?

Yeah, that's pretty easy.

Start with Afields(), which gives you an array of the structure you need to build. That gives you all information you need to build the command, assuming of course you know what command you intend to build.

Loop through the array, I usually use Textmerge to member, and a CASE structure to handle variances of the data type.

I'd just upload the program I'm actually using (which is actually limited to a few data types), but I don't have access to it right now and won't until next Monday.

But it's really not that difficult to do. It's one of those things that's more difficult to describe than to do.

 

[stanlyn]

Hi Dan,

Yea, I've been using alter, afields(), DBSetProp, index, and create for years. I was hoping for something as fast and complete as MSSQL gives us when we script the structure to a file. The script is instantly done and ready to execute on a different system and instantly you have it all, exactly as the original.

So, is it fair to say that VFP has no such tools?

Thanks,
Stanley

 

[Dan Freeman]

Yes, and no.

You can use gendbc.prg (in the tools folder) to generate ALL of the code to create ALL of the tables/views/indexes in a DBC, but if you want just one it's quite a bit of work to delete all the other stuff.

 

[stanlyn]

Thanks Dan, I'll try the gendbc and see how difficult it is... I've heard about it before and because I've never used it, I forgot about it... Time to try it... If it creates it serially, then it shouldn't be that hard, should it? Any gotchas?

Thanks, Stanley

 

[stanlyn]

Hi Dan,

Just tried it and it looks very complete and its fast. It does create it serially which makes it easy to find and lift out whatever routine is needed. All the table creation, indexes and extended table properties are all together, making it easy to "highlight and execute" or copy/paste. Excellent...

Thanks,
Stanley

 

[Olaf Doschke]

It has a slight problem, it creates all tables in one folder, if you have structured your DBC and DBFs in directories, that is lost.
There is a cummunity edition gendbcx, I actually don't know if it still fixes some issues, but surely not that of the folder. Anyway, doesn't matter, if you just pick out some code.

There is one obvious other solution: Copy files and ZAP. Most easy with free dbfs. Not so easy with DBFs pointing to DBCs, as the DBCs also need to contain some meta data.

But there still is one very easy way: COPY TO dbffile with it's optional DATABASE clause and several others ("NAME ..." for long names, and/or "WITH CDX" to copy indexes). I use COPY TO ...needed clauses... FOR .F. to create empty copies of very large tables, and that works easiest.

Edit: There is one tricky thing to do, if you want to copy the structure of a very large table: USE thetable.dbf, then GO BOTTOM, then COPY TO file REST ...needed clauses... FOR .F. -it's the REST clause making it a fast copy, rushmore seems to fail to optimize FOR .F. in this case, or I just did it for making sure it'll work fast.

Bye, Olaf.