SQL BCP command to export full database data to text/csv file

There are times when you need to automate the exporting of table data to text file. In that command line BCP command comes handy.

SQL CODE TO EXPORT FULL DATABASE DATA:

SET NOCOUNT ON

DECLARE @cmd NVARCHAR(2048)

DECLARE @tableName NVARCHAR(2048)

DECLARE @schemaID NVARCHAR(2048)

DECLARE @schemaName NVARCHAR(2048)

DECLARE @outPath NVARCHAR(2048)  

SET @outPath = 'd:\test\'

DECLARE tableCursor CURSOR

FOR SELECT [name],[uid] FROM sysobjects WHERE type='U' FOR READ ONLY

OPEN tableCursor

FETCH NEXT FROM tableCursor INTO @tableName, @schemaID;

WHILE @@FETCH_STATUS = 0

BEGIN

      SET @schemaName = SCHEMA_NAME(@schemaID)

      SET @cmd = 'bcp [' + db_name() + '].['+ @schemaName +'].[' + @tableName + '] out ' + @outPath + @tableName + '.txt -t"," -T -c -S <SERVERNAME\INSTANCE>'

      PRINT @cmd

      EXEC xp_cmdshell @cmd

      FETCH NEXT FROM tableCursor INTO @tableName,@schemaID;

END;

CLOSE tableCursor;

DEALLOCATE tableCursor;

Happy Exporting !!

Ravi Kumar V.