select * into 中间表 from openrowset('MSDASQL',
'Driver=Microsoft Visual FoxPro Driver;SourceType=DBF;SourceDB=c:\',
'select * from [aa.DBF]')
select a.name 列名,b.name 类型,a.length 长度, a.isnullable 是否为空 from syscolumns a, systypes b,sysobjects d where a.xtype=b.xusertype and a.id=d.id and d.xtype='U' and d.name='中间表'
select top 1 SQL_VARIANT_PROPERTY(列1,'BaseType'),SQL_VARIANT_PROPERTY(列2,'BaseType'),SQL_VARIANT_PROPERTY(列3,'BaseType') from openrowset('MSDASQL',
'Driver=Microsoft Visual FoxPro Driver;SourceType=DBF;SourceDB=c:\',
'select * from [aa.DBF]')
--根据Dbase表生成临时表
select * into #tb from
OPENROWSET('MICROSOFT.JET.OLEDB.4.0'
,'dBase IV;DATABASE=C:\','select * from [地区资料.dbf] where 1=2')
--根据临时表查询表结构
SELECT 字段序号=a.colorder,
字段名=a.name,
标识=case when COLUMNPROPERTY( a.id,a.name,'IsIdentity')=1 then '√'else '' end,
类型=b.name,
占用字节数=a.length,
长度=COLUMNPROPERTY(a.id,a.name,'PRECISION'),
小数位数=isnull(COLUMNPROPERTY(a.id,a.name,'Scale'),0),
允许空=case when a.isnullable=1 then '√'else '' end
FROM tempdb..syscolumns a
left join tempdb..systypes b on a.xtype=b.xusertype
where object_id('tempdb..#tb')=id
order by a.id,a.colorder
select top 1 SQL_VARIANT_PROPERTY(列1),SQL_VARIANT_PROPERTY(列2),SQL_VARIANT_PROPERTY(列3) from openrowset('MSDASQL',
'Driver=Microsoft Visual FoxPro Driver;SourceType=DBF;SourceDB=c:\',
'select * from [aa.DBF]')