To move all the table data from database to xls or

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