BCP OUT tables from any database

,

This script will help DBA's who wants to BCP out required tables form any database. This script can be run through as a job. Before running you have to create a table in any given database and store the table names in it, which you want to Bulk copy. Query in this procedure will check the table for table names and will copy data in .txt files and store the out put in given path.

(You can change the paths for storing the out put and data text files. Also change the database name. By default I have given pubs..)

/*

exec dbo.spo_bcpout

EXECUTE THIS SCRIPT TO CREATE TABLE BEFORE EXECUTING THE PROCEDURE FOR FIRST TIME.
THIS TABLE WILL STORE ALL THE TABLE NAMES WHICH HAS TO BE BCP OUT FROM ANY DATABASE.


CREATE TABLE [bcp_tables] (
	[tablename] [varchar] (200) NOT NULL 
) ON [PRIMARY]


*/

CREATE PROCEDURE dbo.spo_bcpout as

SET NOCOUNT ON

SET QUOTED_IDENTIFIER OFF

DECLARE  @tblvar_tablename TABLE (srno INT identity , tablename VARCHAR(200))

DECLARE @totcnt INT
DECLARE @cnt INT
DECLARE @sqry NVARCHAR(4000)
DECLARE @tblname VARCHAR(200)
DECLARE @FileName VARCHAR(50)

	INSERT INTO  @tblvar_tablename
	SELECT * FROM dbo.bcp_tables 
----where tablename like '%author%'

	SELECT @totcnt = COUNT(*) FROM @tblvar_tablename
SET @cnt = 0

WHILE ( @cnt < @totcnt )
BEGIN
	SET @cnt = @cnt + 1
	
	SELECT @tblname = LTRIM(RTRIM(ISNULL(tablename,''))), 
               @sqry = 'bcp "SELECT * FROM PUBS..'+LTRIM(RTRIM(ISNULL(tablename,'')))+'" queryout  "'
	FROM  @tblvar_tablename WHERE srno = @cnt



SET @tblname = 'c:\'+@tblname+'.txt'
	
	SELECT  @sqry = @sqry + @tblname + ' "  -Uusername -Ppassword -c -o c:\output.txt '

EXEC master..xp_cmdshell @sqry

END

Rate

5 (1)

Share

Share

Rate

5 (1)