Using xp_cmdshell to export all tables into separate txt files

Question

I am trying to export some data from one of my SQL Server database. It has a little over 300 tables and I didn't see any way to export this data using the SSMS wizard as it only does one table at a time. I need to use the delimiter "/". So I have been playing around with xp_cmdshell and it seems to do almost everything I need it to do except one thing.

I need it to just output the table name like this: table1.txt where right now it outputs as [dbo].[table1].txt

Execute sp_MSforeachtable 'Execute master.dbo.xp_cmdshell ''sqlcmd -S SERVER\INSTANCE -E -d DB -q "SET NOCOUNT ON SELECT * FROM ?"''' -W -h-1 -o C:\DBA\?.txt -s "/"'''

Answer 1

Here is one trick to do it

EXECUTE sp_MSforeachtable 'select ''?'' as org_string,replace(replace(''?'',''[dbo].['',''''),'']'','''') as result_string'

You need to add the above logic in your code.

Note : If your table name has [dbo].[ or ] characters in it then this could mess up

Answer 2

You can do this using cursor and bcp command.

use YOURDB;
declare @tablename varchar(50), @schemaId int
declare c cursor for 
select name, [schema_id] from sys.tables where type = 'U' and is_ms_shipped = 0
open c
fetch next from c into @tablename, @schemaId
while @@FETCH_STATUS = 0
begin
declare @query varchar(1000) = 'bcp '+quotename(schema_name(@schemaId))+'.'+
quotename(@tablename)+' out C:\DBA\'+@tablename+'.txt -S SERVER\INSTANCE -d YOURDB -T -c'
exec xp_cmdshell @query, no_output;
fetch next from c into @dtableame, @schemaId
end
close c
deallocate c

Answer 3

Try this:

exec sp_MSforeachtable 'declare @query varchar(8000)=''sqlcmd -q "set nocount on;select * from ?" -o "''+''C:\DBA\''+replace(substring(''?'',charindex (''.'',''?'')+2,len(''?'')-1),'']'','''')+''.txt"'';
                            exec xp_cmdshell @query'