Exportar Datos De Varias Tablas A La Vez En Txt

[r01010010]

Bueno me respondo a mi misma, encontré algo interesante a raiz de tu respuesta Jota, lo pasteo para que alguien pueda echar mano de ello si necesita lo mismo. (Está en inglés).

To move all the table data from database to xls or doc or txt files table
Many times we come accross to move data from database to external files such as word,xls or data files. Here is the script that move all tables data to specified files. Target file names are the table names.
specify the folder path, server name and @FILE_TYPE wether
.xls or .doc or .txt

---************************************************** **********************************************
-- AUTHOR : VEERESH V NASHI (DBA)
-- CREATED ON : 05-04-2006
-- PUROSE : To move all the table data from database to xls or doc or txt files table
---name as the file name

SET NOCOUNT ON
GO
declare @tables table( tid int identity,tname sysname)
declare @index int
declare @Max int
declare @QUERY varchar(4000)
declare @QUERY1 varchar(4000)
DECLARE @Tname sysname
DECLARE @DBNAME SYSNAME
DECLARE @Folder_Path sysname
DECLARE @SERVER_NAME SYSNAME
DECLARE @FILE_TYPE VARCHAR(5)
insert into @tables (tname)
select name from sysobjects where xtype='u'
set @Max = @@identity

------USER SETTINGS-----------------
SET @Folder_Path = 'D:\XLS'
SET @SERVER_NAME ='javauser3\veena'
SET @FILE_TYPE = 'XLS' -- EXCEL OR DATAFILES LIKE TXT OR DOC
------USER SETTINGS-----------------

SET @DBNAME = DB_NAME()
SET @QUERY =''
SET @QUERY1 =''
SET @Index = 1
WHILE @index <= @max
BEGIN
SELECT @Tname = tname from @tables where tid = @Index
SET @QUERY1 ='SELECT '
SELECT @QUERY1 = @QUERY1 + '''' + QUOTENAME( NAME ,'''') + ''' AS ' + NAME + ' ,' FROM SYSCOLUMNS where id = object_id(@tname)
SET @QUERY1 = LEFT( @QUERY1,LEN( @QUERY1 ) - 1 )


SET @QUERY='SELECT '
SELECT @QUERY = @QUERY + 'CAST ( '+NAME + ' AS VARCHAR('+ LTRIM( STR( LENGTH )) +')),' FROM SYSCOLUMNS where id = object_id(@tname)
SET @QUERY = LEFT( @QUERY,LEN( @QUERY ) -1 ) +' FROM '+@DBNAME+'.dbo.'+ @TNAME


SET @QUERY1 = @QUERY1 + ' UNION ALL ' + @QUERY

PRINT 'MOVING '+@tname +' TO ' + @Folder_Path +' AS FILE NAME ' +@TNAME +'.'+@FILE_TYPE

SELECT @Query= 'master.dbo.xp_cmdshell
''bcp.exe "'+@QUERY1 + ' " queryout "'+@Folder_Path+'\' + @tname + '.'+@FILE_TYPE +'" -c -T -S '+@SERVER_NAME +''''

EXECUTE(@Query)
--PRINT (@Query1)
set @Index = @Index + 1
end

GO
SET NOCOUNT ON