Automating BCP out for all databases

Question

Post reply

Automating BCP out for all databases

iabed

SSC Veteran

Points: 207
More actions
May 12, 2008 at 12:06 pm

#121546

Hi all.
I have the following scripts which works for the pubs db...
select 'bcp pubs..' + name + ' out ' + name + '.txt' + '-c -T' from pubs..sysobjects where type = 'U' order by name
It generates the following which I can output to a batch file and run from the command prompt:
bcp pubs..authors out authors.txt -c -T
bcp pubs..discounts out discounts.txt -c -T
etc.
for all the tables in pubs.
Now what I'd like is to generalize this so that all the tables for all the created databases will have a corresponding bcp output. Any suggestions? I'd like to generate an output generically like so:
bcp [database name]..[table1] out [database+table1].txt -c -T
bcp [database name]..[table2] out [database+table2].txt -c -T
etc.
I'm having a devil of a time passing the parameters properly.

Viewing 5 posts - 1 through 5 (of 5 total)

You must be logged in to reply to this topic. Login to reply

Jeff Moden SSC Guru Points: 1004641 More actions · Answer 1

Google for ...

sp_MSForEachDB

sp_MSForEachTable

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

iabed SSC Veteran Points: 207 More actions · Answer 2

Thanks Jeff, enormously helpful. Love all the "undocumented" procedures out there, like uncovering buried treasure.

Gaby

Jeff Moden SSC Guru Points: 1004641 More actions · Answer 3

You'll also find a lot of folks that suggest not using them because Microsoft could change them at any time... Heck, they do that with documented features :hehe:

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

Tammy.Prater SSC Journeyman Points: 80 More actions · Answer 4

Did you get a script to work for this? I am wanting to use a script to bcp out the data from all the tables in the database without running them one at a time. I would really appreciate it if you did get one to run if you could post the script.

Thanks!!!