BCP工具过滤数据表的问题,请各位大神帮忙看下!全部分奉上!
用BCP工具导出数据表,默认的是全部导出,现在需要做修改,需要过滤导出表。脚本应该怎么修改呢、?
大概思路所有需要导出的表放在一个配置文件里面,读取配置文件,给个列表,然后按照列表来导出。
请大神们帮忙看下,谢谢了!
下面是BCP工具脚本select_allDB_table_Ms.sql的代码:
EXEC sp_configure 'show advanced options', 1GO-- 重新配置RECONFIGUREGO-- 启用xp_cmdshellEXEC sp_configure 'xp_cmdshell', 1GO--重新配置RECONFIGUREGOcreate proc sp_test @path varchar(255),@svrname varchar(255),@username varchar(255),@password varchar(255),@indbname varchar(255)asselect * from sysdatabases where dbid > 6declare @dbname varchar(255)declare @tablename varchar(255)declare @cmdline varchar(255)declare @creatpath varchar(255)select @creatpath = 'md ' + @pathexec master..xp_cmdshell @creatpathexec ('declare all_cursor cursorfor select name from sysdatabases where dbid > 6 and upper(name) like ''%'+@indbname+'%''')open all_cursorfetch all_cursor into @dbnamewhile @@fetch_status = 0begin print @dbname select @cmdline = 'md ' + @path + '\'+ @dbname exec master..xp_cmdshell @cmdline exec ('declare tbl_cursor cursor for select name from ' + @dbname +'.dbo.sysobjects where xtype = ''U''') open tbl_cursor fetch tbl_cursor into @tablename while @@fetch_status = 0 begin print @tablename select @cmdline = 'bcp "'+ @dbname + '..' + @tablename + '" out "' + @path + '\' + @dbname + '\' +@tablename + '.txt" -c -U' + @username +' -P'+@password +' -S ' + @svrname print @cmdline exec master..xp_cmdshell @cmdline fetch tbl_cursor into @tablename end close tbl_cursor deallocate tbl_cursor fetch all_cursor into @dbnameendclose all_cursordeallocate all_cursorGO