--初始化数据处理语句
select @sql='bcp "'+@dbname+'..'
,@tbstation=len(@sql)+1
,@sql=@sql
+case when @isout=1 then '" out' else '" in' end
+' "'+@path
,@filestation=len(@sql)+1
,@sql=@sql
+'.txt" /c' +' /S"'+@servername
+case when isnull(@username,'')='' then ''
else '" /U"'+@username end
+'" /P"'+isnull(@password,'')+'"'
--数据导入/导出处理
--定义数据处理的游标
set @sql1='declare #tb cursor for select name from '
+@dbname+'..sysobjects where xtype=''U'''
+case when isnull(@tbname,'')='' then ''
else ' and name in ('''+replace(@tbname,',',''',''')+''')' end
exec(@sql1)
open #tb
fetch next from #tb into @tbname
while @@fetch_status=0
begin
select @sql1=stuff(@sql,@tbstation,0,@tbname)
,@sql1=stuff(@sql1,@filestation+len(@tbname),0,@tbname)
exec master..xp_cmdshell @sql1,no_output
fetch next from #tb into @tbname
end
close #tb
deallocate #tb
go
set nocount on
declare @name sysname
declare #cursor cursor for select [name] from 库名.dbo.sysobjects where 条件 --此条件根据你的表名规律进行取适当条件
open #cursor
fetch next from #cursor into @name
while @@fetch_status=0
begin
exec ('exec master..xp_cmdshell ''bcp 库名.dbo.'+@name+' out c:\'+@name+'.txt -c -t , -Usa -Pmypassword''')
fetch next from #cursor into @name
end
set nocount off