FoxPro Schema from SQL? 1

[gcoast]

Does anyone know if there is a way to get the schema of a FoxPro .dbf table using SQL? I'm looking into using Open query but haven't found a way to perform a FoxPro command i.e. List Structure, to return this info.

Why you may ask, well we have hundreds of legacy FoxPro tables that are due to be imported into SQL and I have been asked to compile a SQL query showing this information %-(

Thanks in advance,

gc

 

[NathanGriffiths]

I don't think you'll be able to do this from SQL Server directly. You could try something like this maybe;

select *
into #temptbl
from openquery([<foxpro tables>],'select * from <table>')
where 1=0

This will create a temporary SQL table from the foxpro table, then you can do

EXEC tempdb.dbo.sp_help #temptbl

The data types will be not be Foxpro data types though, but the ones assigned by the ODBC or SQL when it imported the data (e.g. numeric will probably become float etc.), but you get the column names and sequence correctly.

or you could get into the tempdb sysobjects, syscolumns and systypes tables directly to make your own report.

HTH

Nathan

----------------------------------------
Want to get a good response to your question? Read this FAQ! -> faq183-874
----------------------------------------

 

[gcoast]

Nathan,

Thanks for your quick response I think I will be able to use your suggestion.

gc