Nov
2
2011

SQL Server – Export All User Tables Part 1

If all you want is to export raw data from SQL server to a flat file, doing so should be easy.

But what if you require text qualifiers and a formatted header?

Neither BCP or SQLCMD support text qualifiers. BCP doesn’t support column headers at all and SQLCMD’s column headers aren’t very friendly.

So what do you do when you need to dump all the data in your database to flat files? If you only have several tables, you could export them manually using the Import/Export Wizard or even SSIS, but both require you to export each table individually. SSIS natively requires strongly typed structure when piping data through.

I have come up with 2 different solutions. The first will be demonstrated here and will consist of dynamic T-SQL and the BCP utility. Part 2 will consist of SSIS and will be geared more towards the enterprise environment.

The code here is pretty self-explanatory.

We start out setting variables for our script. These consist of the directory we will be exporting the tables to, and the source database and variables to hold our dynamic SQL. For more aesthetic purposes I split the SQL into two variables: one for the column headers and another for the main recordset.

 T-SQL |  copy code |? 
1
declare @outputDirectory varchar(255), @databaseName varchar(255)
2
set @outputDirectory = 'C:\test_export\'
3
set @databaseName = 'myDatabase'
4
 
5
declare @sqlHeader varchar(8000), @sqlBody varchar(8000)

 

Next, we create a table variable to hold the list of table names we want to export. In this example I generate the table names by querying the information_schema.tables view.

 T-SQL |  copy code |? 
1
declare @tbl table (id int identity(1,1), tableName varchar(255))
2
insert into @tbl (tableName) select table_name from information_schema.tables order by table_name

 

Then we loop through the table variable and generate our SQL for every table.

 T-SQL |  copy code |? 
1
declare @id int, @maxid int
2
select @id = 1, @maxid = max(id) from @tbl
3
 
4
while (@id <= @maxid) begin
5
declare @currTableName varchar(255)
6
select @currTableName = tableName, @sqlHeader = null, @sqlBody = null from @tbl where id = @id

 

We generate the SQL for the column header by querying the columns in the information_schema.columns view for the appropriate table, sorted by the column’s ordinal. We use the coalesce function to concatenate the column names in an effecient statement and we wrap the column name with double quotes for our text qualifier and single quotes to specify a string in SQL. We also make sure that the double quotes are repeated so that they won’t be escaped when we execute the BCP command.

Note: I am excluding columns that are of image type because these were useless to export. You could of course convert the image to varbinary then to varchar to export as a string. 

 T-SQL |  copy code |? 
1
select @sqlHeader = coalesce(@sqlHeader+',','')+ '''""'+ column_name+'""''' from information_schema.columns where table_name = @currTableName and data_type != 'image' order by ordinal_position
2
set @sqlHeader = 'select ' + @sqlHeader + 'union all '

 

We generate the SQL for the recordset in a very similar manner, except instead of setting the column name as a string we set it as the column name,  then cast it as a string for uniform export and set null values to an empty string.

 T-SQL |  copy code |? 
1
 --Set SQL Syntax for records
2
select @sqlBody = coalesce(@sqlBody+',','')+ '''""''+ isnull(cast('+ quotename(column_name)+' as varchar(max)),'''')+''""''' from information_schema.columns where table_name = @currTableName and data_type != 'image' order by ordinal_position
3
set @sqlBody = 'select ' + @sqlBody + ' from ' + quotename(@databaseName)+'..'+quotename(@currTableName) 

 

At the end we concatenate these two SQL queries into one union query and use it to execute a BCP command.

 T-SQL |  copy code |? 
1
declare @bcpCommand varchar(8000)
2
set @bcpCommand = 'bcp "' + @sqlHeader + @sqlBody + '" queryout '+@outputDirectory+@currTableName+'.txt -c -t"|" -T -S' +@@servername
3
 
4
exec master..xp_cmdshell @bcpCommand

 

The entire T-SQL code is as follows:

 T-SQL |  copy code |? 
01
declare @outputDirectory varchar(255), @databaseName varchar(255)
02
set @outputDirectory = 'C:\test_export\'
03
set @databaseName = 'myDatabase'
04
 
05
declare @sqlHeader varchar(8000), @sqlBody varchar(8000)
06
 
07
declare @tbl table (id int identity(1,1), tableName varchar(255))
08
insert into @tbl (tableName) select top 5 table_name from information_schema.tables order by table_name
09
 
10
declare @id int, @maxid int
11
select @id = 1, @maxid = max(id) from @tbl
12
 
13
while (@id <= @maxid) begin
14
 declare @currTableName varchar(255)
15
 select @currTableName = tableName, @sqlHeader = null, @sqlBody = null from @tbl where id = @id
16
 
17
 --Set SQL Syntax for headers
18
 select @sqlHeader = coalesce(@sqlHeader+',','')+ '''""'+ column_name+'""''' from information_schema.columns where table_name = @currTableName and data_type != 'image' order by ordinal_position
19
 set @sqlHeader = 'select ' + @sqlHeader + 'union all '
20
 
21
 --Set SQL Syntax for records
22
 select @sqlBody = coalesce(@sqlBody+',','')+ '''""''+ isnull(cast('+ quotename(column_name)+' as varchar(max)),'''')+''""''' from information_schema.columns where table_name = @currTableName and data_type != 'image' order by ordinal_position
23
 set @sqlBody = 'select ' + @sqlBody + ' from ' + quotename(@databaseName)+'..'+quotename(@currTableName) 
24
 
25
 declare @bcpCommand varchar(8000)
26
 set @bcpCommand = 'bcp "' + @sqlHeader + @sqlBody + '" queryout '+@outputDirectory+@currTableName+'.txt -c -t"|" -T -S' +@@servername
27
 
28
 exec master..xp_cmdshell @bcpCommand
29
 
30
set @id = @id + 1
31
end
32
 
33
select 'Export complete...'

Related Posts

About the Author: Jared Miller

Leave a comment