Urgent-------Export all tables in a schema to multiple textfiles in a pipe delimited format

Question

Urgent-------Export all tables in a schema to multiple textfiles in a pipe delimited format

santu4bth

SSC Enthusiast

Points: 171
More actions
April 13, 2011 at 11:48 pm

#248865

Friends,
Please help me!
I have 3 schemas in a database and each schema has about 300 tables.Business is asking to export all (900 tables) to text files ie. each table to a textfile in pipe delimited format.Manually I can't do this using xp_cmd shell and bcp
since that allows only one table at a time.
can any one provide me a script to this task.Waiting for your help
santosh v
santu4bth@gmail.com

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

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

Orlando Colamatteo SSC Guru Points: 182276 More actions · Answer 1

xp_cmdshell calling bcp....yuck!

Write a PowerShell script to (1) get the list of tables from sys.tables (2) loop over the list of tables and call bcp for each one.

There are no special teachers of virtue, because virtue is taught by the whole community.
--Plato

drew.allen SSC Guru Points: 76944 More actions · Answer 2

You could also try the undocumented stored procedure sp_msforeachtable.

Drew

J. Drew Allen
Business Intelligence Analyst
Philadelphia, PA

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

santu4bth (4/13/2011)
Friends,
Please help me!
I have 3 schemas in a database and each schema has about 300 tables.Business is asking to export all (900 tables) to text files ie. each table to a textfile in pipe delimited format.Manually I can't do this using xp_cmd shell and bcp
since that allows only one table at a time.
can any one provide me a script to this task.Waiting for your help
santosh v
santu4bth@gmail.com

Do you have the privs to use xp_CmdShell?

--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)

santu4bth SSC Enthusiast Points: 171 More actions · Answer 4

Actually I'm not sure .They are trying for access for me on that server(PROD).But if there are any restrictions then my idea is to get the data from production to testing server and enable xp_xmdshell while I run this script.

I got a script from somebody and that is working fine .

DECLARE @tablename VARCHAR(256) -- filename for backup

DECLARE @cmd VARCHAR(max)

DECLARE db_cursor CURSOR FOR

select db_name() + '.' + b.name + '.' + a.name as TableName from sys.objects a inner join sys.schemas b on a.schema_id = b.schema_id

where a.type_desc = 'USER_TABLE'

OPEN db_cursor

FETCH NEXT FROM db_cursor INTO @tablename

WHILE @@FETCH_STATUS = 0

BEGIN

SET @cmd = 'xp_cmdshell ''BCP ' + @tablename + ' out C:\output\' + @tablename + '.txt -t"|" -T -c'''

--PRINT @cmd

EXEC (@cmd)

FETCH NEXT FROM db_cursor INTO @tablename

END

CLOSE db_cursor

DEALLOCATE db_cursor

Jeff Moden SSC Guru Points: 1003870 More actions · Answer 5

That'll work. Thanks for sharing.

--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)