先在数据库中取出所有的用户表,具体方法在 MASTER 数据库中sysobjects表中找出。然后再读出每个表中的字段和数据
下面是我以前做的,你参考一下: f = FreeFile '//取得空闲的文件号
Open M_file_name For Output As f
'//在这里打开数据库
rs.Open "SELECT * From sysobjects WHERE xtype = 'u' AND sysstat = 67"
For i = 0 To rs.recordcount
If table_arr(i) <> "" Then '//判断数据变量中是否有数据
f = FreeFile
rs.Open "select * from " + table_arr(i) + ""
table_arr(i) = table_arr(i) & ".txt"
Open table_arr(i) For Output As f
Do While Not rs.EOF
For fld_num = 1 To rs.Fields.Count - 1 '//取得每个字段
If IsNull(rs.Fields(fld_num)) Then
file_con = file_con & "Null" & Space(rs.Fields(fld_num).DefinedSize - 1)
Else
file_con = file_con & Trim(rs.Fields(fld_num)) & Space(rs.Fields(fld_num).DefinedSize - Len(Trim(rs.Fields(fld_num))) + 3)
End If
Next Print #f, file_con '//将一条记录输出到一行
rs.MoveNext
file_con = ""
Loop
Close f
rs.Close
下面是我以前做的,你参考一下: f = FreeFile '//取得空闲的文件号
Open M_file_name For Output As f
'//在这里打开数据库
rs.Open "SELECT * From sysobjects WHERE xtype = 'u' AND sysstat = 67"
For i = 0 To rs.recordcount
If table_arr(i) <> "" Then '//判断数据变量中是否有数据
f = FreeFile
rs.Open "select * from " + table_arr(i) + ""
table_arr(i) = table_arr(i) & ".txt"
Open table_arr(i) For Output As f
Do While Not rs.EOF
For fld_num = 1 To rs.Fields.Count - 1 '//取得每个字段
If IsNull(rs.Fields(fld_num)) Then
file_con = file_con & "Null" & Space(rs.Fields(fld_num).DefinedSize - 1)
Else
file_con = file_con & Trim(rs.Fields(fld_num)) & Space(rs.Fields(fld_num).DefinedSize - Len(Trim(rs.Fields(fld_num))) + 3)
End If
Next Print #f, file_con '//将一条记录输出到一行
rs.MoveNext
file_con = ""
Loop
Close f
rs.Close
drop table ##temptable
go
set nocount onselect obj.name as tables,
col.name as columns,
stype.name as type,
col.length as length,
nullif(col.xprec,0) as precesion,
col.scale as scale,
case col.isnullable
when 1 then 'yes'
else 'no'
end as allownull,
col.colstat as identyinto ##temptable
from sysobjects obj,
syscolumns col,
systypes as stypewhere col.id = obj.id
and obj.xtype = 'U' --类型为用户类型
and col.xtype = stype.xtype
and stype.xtype = stype.xusertype
go
--select * from ##temptable
EXEC master..xp_cmdshell 'bcp ##temptable out c:\temp1.txt -c -q -S"hdlm" -U"sa" -P"admin"'
go