Export all table data to Text file / Excel file

DECLARE @tables TABLE(table_name VARCHAR(100))

INSERT INTO @tables

SELECT name FROM sysobjects WHERE xtype ='u'

DECLARE @table_name VARCHAR(1000)

SELECT @table_name=MIN(table_name)FROM @tables

WHILE @table_name>''

BEGIN

        DECLARE @str VARCHAR(1000)  

        SET @str='EXEC Master..xp_Cmdshell ''bcp "SELECT * FROM '+db_name()+'..'+@table_name+'" queryout "C:\data\'+@table_name+'.xls" -Sservername -Uusername -Ppassword -T -c'',no_output'  

        EXEC(@str)  

SELECT @table_name=MIN(table_name)FROM @tables WHERE table_name>@table_name

        

-- DECLARE @str1 VARCHAR(1000)  

--      SET @str1='EXEC Master..xp_Cmdshell ''bcp "SELECT * FROM '+db_name()+'..'+@table_name+'" queryout "C:\data\'+@table_name+'.txt" -Sservername -Uusername -Ppassword -T -c'',no_output'  

--      EXEC(@str1)  

-- SELECT @table_name=MIN(table_name)FROM @tables WHERE table_name>@table_name

END