Urgent-------Export all tables in a schema to mult

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2008 Forums

SSIS and Import/Export (2008)

Urgent-------Export all tables in a schema to mult

Author

Topic

santu4bth
Starting Member

5 Posts

Posted - 2011-04-14 : 01:57:45

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

sunitabeck
Master Smack Fu Yak Hacker

5155 Posts

Posted - 2011-04-14 : 07:28:16

There are probably sophisticated ways to do it using SSIS, but I would use the low-tech way, as follows.

1. In SSMS, in a query window run the following command.

SELECT 
   'bcp ' 
   + TABLE_CATALOG + '.' + TABLE_SCHEMA + '.' + TABLE_NAME
   + ' out '
   + TABLE_SCHEMA + '_' + TABLE_NAME + '.txt'
   + ' -T -c'
FROM
   INFORMATION_SCHEMA.TABLES

This generates the bcp command for exporting each file in the database. If the script generated does not do the right things, change the query as required.

2. Copy the output of the query to a command file or a command window and let it rip. I would try it for a group of 2 or 3 files first before doing it en-masse.

I know SSIS aficionados will laugh at me (and at you if you use this approach), but let 'em!!

yosiasz
Master Smack Fu Yak Hacker

1635 Posts

Posted - 2011-04-14 : 12:10:47

is business asking you to export the content of the tables or the schema of the tables?

If you don't have the passion to help people, you have no passion

sunitabeck
Master Smack Fu Yak Hacker

5155 Posts

Posted - 2011-04-14 : 12:23:02

I am thinking that it is the content of the tables because of OP's "text file in pipe delimited format" statement.

santu4bth
Starting Member

5 Posts

Posted - 2011-04-14 : 17:26:59

its the data from the tables to get exported to textfiles

santu4bth
Starting Member

5 Posts

Posted - 2011-04-14 : 17:54:21

Thanks sunitha let me try that and will get back to you