Get BCP out for all tables in a DB

,

           The script mentioned below will export data in all tables into textfiles to a physical path on your system using BCP utility from a Database. The sql commands used in this stored procedure are BCP utility,Identity function and temporary tables.
    Here you required to give 4 input parameters, they are path to export data to textfiles,user name of the database, password of that database and servername.
For example EXEC USP_BCPOUT 'C:\TEMP\','SA','',''

CREATE PROCEDURE Usp_BcpOut 
	@Path VARCHAR(100),
	@UserName VARCHAR(15),
	@PassWord VARCHAR(15),
	@ServerName VARCHAR(15)
AS 

/*** This procedure does the BCP out for all tables in a database.
     Here U need to give the Path of the hostfile, Server name of 
     database, User name and Pass word to connect datatbase ***/ 
SET QUOTED_IDENTIFIER OFF
SET NOCOUNT ON
DECLARE @n INT
DECLARE @db VARCHAR(40)
SET @db=DB_NAME()
DECLARE @TableName VARCHAR(15) 
DECLARE @bcp VARCHAR(200)
SELECT identity(int,1,1) as tblNo,name AS tblname INTO #TblNames FROM Sysobjects WHERE xtype='u'
SELECT @n=COUNT(*) FROM #TblNames
WHILE (@n>0)
BEGIN
	SELECT @TableName=tblname FROM #TblNames WHERE tblno=@n
	PRINT 'Now BCP out for the table: ' +@TableName
	SET @bcp="master..xp_cmdshell 'BCP " + @db +".." + @TableName + " OUT " + @Path + "" + @TableName + ".txt -c -U" + @UserName + " -P" + @PassWord + " -S" + @ServerName + "'" 
	EXEC(@bcp)
	SET @n=@n-1
END
DROP TABLE #TblNames

Rate

Share

Share

Rate