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\'

DECLAREtableCursor CURSOR

FOR SELECT[name],[uid] FROMsysobjects WHEREtype='U' FOR READ ONLY

OPENtableCursor

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 NEXTFROM tableCursor INTO@tableName,@schemaID;

END;

CLOSEtableCursor;

DEALLOCATE tableCursor;

Happy Exporting !!

Ravi Kumar V.