Use the stored procedure implemented by bcp to direct the entire database

ZOU Jian's
/******************* Guide the entire database **************** *****************************/

Stored procedure implemented with BCP

/*
Stored Procedure for Data Import/Export
You can import/export the entire database or a single table based on different parameters.
Call example:
-- Export call example
---- Export a single table
Exec file2table 'zj', '','', 'xzkh _ sa .. region information', 'c:/zj.txt ', 1
---- Export the entire database
Exec file2table 'zj', '','', 'xzkh _ Sa', 'c:/docman ', 1

-- Import call example
---- Import a single table
Exec file2table 'zj', '','', 'xzkh _ sa .. region information', 'c:/zj.txt ', 0
---- Import the entire database
Exec file2table 'zj', '','', 'xzkh _ Sa', 'c:/docman ', 0

*/
If exists (select 1 from sysobjects where name = 'file2table' and objectproperty (ID, 'isprocedure ') = 1)
Drop procedure file2table
Go
Create procedure file2table
@ Servername varchar (200) -- server name
, @ Username varchar (200) -- user name. If you use the NT authentication method, it is null''
, @ Password varchar (200) -- Password
, @ Tbname varchar (500) -- database. DBO. Table name. If not specified:. DBO. Table Name, all user tables of the database will be exported.
, @ Filename varchar (1000) -- import/export path/file name. If @ tbnameverification indicates that the entire data warehouse is exported, the file name will automatically use table name .txt
, @ Isout bit -- 1 is for export, 0 is for Import
As
Declare @ SQL varchar (8000)

If @ tbname like '%. %. %' -- if the table name is specified, a single table is exported directly.
Begin
Set @ SQL = 'bcp' + @ tbname
+ Case when @ isout = 1 then 'out' else' in 'end
+ '"' + @ Filename + '"/W'
+ '/S' + @ servername
+ Case when isnull (@ username, '') = ''then'' 'else'/U' + @ username end
+ '/P' + isnull (@ password ,'')
Exec master .. xp_mongoshell @ SQL
End
Else
Begin -- export the entire database, define the cursor, and retrieve all user tables
Declare @ m_tbname varchar (250)
If right (@ filename, 1) <> '/'set @ filename = @ filename + '/'

Set @ m_tbname = 'Clare # TB cursor for select name from '+ @ tbname +' .. sysobjects where xtype = 'u '''
Exec (@ m_tbname)
Open # TB
Fetch next from # TB into @ m_tbname
While @ fetch_status = 0
Begin
Set @ SQL = 'bcp' + @ tbname + '...' + @ m_tbname
+ Case when @ isout = 1 then 'out' else' in 'end
+ '"'{@Filename}@m_tbname}'.txt"/W'
+ '/S' + @ servername
+ Case when isnull (@ username, '') = ''then'' 'else'/U' + @ username end
+ '/P' + isnull (@ password ,'')
Exec master .. xp_mongoshell @ SQL
Fetch next from # TB into @ m_tbname
End
Close # TB
Deallocate # TB
End
Go