Is there a way to extract all the records from 20 tables in a database 2

[qb828]

I have a database containing about 20 tables.
I need to extract all the records from each tables.
But Can I extract them all from all the tables at the same time?
OR do I need to extract from each table one by one??
I can extract the data into the excel sheet or
I can just have the table format.

How can I extract the data into a file?
Thanks

QB

 

[JayKusch]

You will need to extract each table into its own file. This is largely due to the fact that each table will have differing fields and datatypes ... I assume!

If you are trying to automate the process a bit so that you do not have to do this each and every time you need to dump these 20 tables, I would suggest setting up a DTS package for these operations. Once completed, all you will have to do is execute the package when you need to dump the tables.

Thanks

J. Kusch

 

[sunila7]

Try this:

This is an undocumented SP.

EXEC sp_msforeachtable 'SELECT * from ?'

Sunil

 

[qb828]

Thanks J.Kusch. You helped me many times already here at Tek-tips.
I appreciate your help.

I tried Sunil's sql statement.
It looks like it pulled up all the tables.

Sunil, do you know how to export these to a file, excel or flat file or something from the sql server?
Thank you also for your help.

QB

 

[nigelrivett]

try bcp

something like

create table #a(name varchar(128))
insert #a select 'mytbl'

if there is a connection between the table names

insert #a select name from sysobjects where xtype = 'U' and name like 'tbl_%'

In that case you don't need the temp table but can use the above in the query below

select 'bcp mydb..' + name + ' out c:\bcpout\' + name + '.txt -c -Smyserver'
from #a

Copy the result into a .bat file and run it and it should extract all the data from the tables into files.
You could run the result directly using xp_cmdshell or osql but it is usually better to use a .bat file.
See bcp in bol for the various formats available.


======================================
Cursors are useful if you don't know sql.
DTS can be used in a similar way.
Beer is not cold and it isn't fizzy.