BCP export data and schema from all tables in all databases

 EXEC master.dbo.sp_configure 'show advanced options', 1;

RECONFIGURE WITH OVERRIDE;

GO

EXEC master.dbo.sp_configure 'xp_cmdshell', 1;

RECONFIGURE WITH OVERRIDE;

GO

SET NOCOUNT ON;

DECLARE @bcpSchema nvarchar(4000), @db sysname;

DECLARE cur CURSOR LOCAL FAST_FORWARD FOR

    SELECT [name] FROM sys.databases WHERE name NOT IN ('master', 'model', 'msdb', 'tempdb', 'distribution', 'ReportServer', 'ReportServerTempDB');

OPEN cur

WHILE 1 = 1

BEGIN

    FETCH cur INTO @db;

    IF @@FETCH_STATUS <> 0 BREAK;

SET @bcpSchema = N'bcp "SELECT SCHEMA_NAME(tab.[schema_id]) AS [schema], tab.[name] AS table_name, col.column_id, col.[name] AS column_name, t.[name] as data_type, col.max_length, col.[precision], col.scale FROM sys.tables AS tab JOIN sys.columns AS col ON tab.[object_id] = col.[object_id] LEFT JOIN sys.types AS t ON col.user_type_id = t.user_type_id ORDER BY [schema], table_name, column_id;" queryout "D:\DWExport\Schema ' + REPLACE(@@SERVERNAME, '\', '_') + ' ' + @db + ' ' + CONVERT(varchar(8), CAST(GETDATE() AS date), 112) + '.bcp" -T -w -S ' + @@SERVERNAME + ' -d ' + @db;

EXEC master..xp_cmdshell @bcpSchema;

END

CLOSE cur

DEALLOCATE cur

/* EXPORT SCHEMA FINISH */

/* EXPORT TABLE DATA START */

DECLARE @allTables table (completeTableName nvarchar(4000));

DECLARE @bcpTableData nvarchar(4000), @completeTableName nvarchar(4000);

INSERT @allTables (completeTableName)

EXEC sp_msforeachdb 'USE [?];

IF DB_NAME() NOT IN (''master'', ''model'', ''msdb'', ''tempdb'', ''distribution'', ''ReportServer'', ''ReportServerTempDB'')

BEGIN

SELECT ''?''+''.''+s.name+''.''+t.name

FROM sys.tables AS T JOIN sys.schemas AS S ON T.schema_id=S.schema_id;

END';

DECLARE cur CURSOR LOCAL FAST_FORWARD FOR

SELECT completeTableName FROM @allTables ORDER BY completeTableName;

OPEN cur

WHILE 1 = 1

BEGIN

FETCH cur INTO @completeTableName;

IF @@FETCH_STATUS <> 0 BREAK;

SET @bcpTableData = N'bcp ' + @completeTableName + ' out "D:\DWExport\' + REPLACE(@@SERVERNAME, '\', '_') + ' ' + @completeTableName + ' ' + CONVERT(varchar(8), CAST(GETDATE() AS date), 112) + '.bcp" -T -w -S ' + @@SERVERNAME;

EXEC master..xp_cmdshell @bcpTableData;

END

CLOSE cur

DEALLOCATE cur