/*
Dit script haal alle tabellen van een database over naar file system.
voorbereiding:
1. rechten op xp_cmdshell moeten worden geactiveerd.
2. maak op de server een map aan die dezelfde naam heeft als de database.
Bron: http://www.siccolo.com/Articles/SQLScripts/how-to-create-sql-to-mass-export-all-tables-to-excel.html
*/
/*
--1. rechten
--sp_configure 'show advanced options',1
--GO
--RECONFIGURE
--GO
--sp_configure xp_cmdshell,1
--GO
--RECONFIGURE
*/
--het script dat tabellen uitleest en als xls bestand opslaat.
set
nocount on
declare @databasename as varchar(255)
set @databasename = 'nedasco_productie_wvp'
declare @table_name varchar(255)
declare @command varchar(7000)
declare @file_name_data varchar(500),
@file_name_header varchar(500),
@path varchar(255)
-- folder for a database already created!!!
set @path = 'c:\' + db_name() + '\'
print @path
declare all_tables cursor for
select name from sysobjects where type = 'U' order by name
for read only
open all_tables
fetch next from all_tables into @table_name
while @@fetch_status=0
begin
set @file_name_data = @path + @table_name + '.xls'
set @file_name_header = @path + @table_name + '_column_info.xls'
--///////////HEADER samenstellen\\\\\\\\\\\\\\\
--1. hoeveel kolom velden zijn er?
declare @aantalheadervelden int
set @aantalheadervelden = (select COUNT(*) from INFORMATION_SCHEMA.COLUMNS where TABLE_NAME = '' + @table_name + '')
print @aantalheadervelden
declare @loop int
declare @regel varchar(4000)
set @loop = 0
set @regel = ''
while @loop < @aantalheadervelden
BEGIN
set @loop = @loop + 1
if @regel = ''
BEGIN
set @regel = (select column_name from INFORMATION_SCHEMA.COLUMNS where ORDINAL_POSITION = @loop and TABLE_NAME = '' + @table_name + '')
END
else
BEGIN
set @regel = @regel + ' ' + (select column_name from INFORMATION_SCHEMA.COLUMNS where ORDINAL_POSITION = @loop and TABLE_NAME = '' + @table_name + '')
END
END
declare @cmdtxt as varchar(255)
select @cmdtxt = 'echo ' + @regel + ' >> ' + @file_name_header
print @regel
exec master..xp_cmdshell @cmdtxt
set @regel = ''
-- //////////////get data into excel:\\\\\\\\\\\\\\\\\\
set @command = 'bcp ' + db_name() + '.dbo.' + @table_name + ' out ' + @file_name_data + ' -c -t\t -T '
print @command
exec master..xp_cmdshell @command, NO_OUTPUT
--samenvoegen van de header + data bestanden in een txt bestand
select @cmdtxt = 'copy ' + @file_name_header + ' + ' + @file_name_data + ' c:\' + @databasename +'\' + @table_name + '.txt'
print @cmdtxt
exec master..xp_cmdshell @cmdtxt
--verwijderen header en data bestanden
select @cmdtxt = 'del ' + @file_name_header
exec master..xp_cmdshell @cmdtxt
select @cmdtxt = 'del ' + @file_name_data
exec master..xp_cmdshell @cmdtxt
--rename van txt bestand naar xls bestand
select @cmdtxt = 'move c:\' + @databasename + '\' + @table_name + '.txt c:\' + @databasename + '\' + @table_name + '.xls'
print 'copy: ' + @cmdtxt
exec master..xp_cmdshell @cmdtxt
fetch next from all_tables into @table_name
END
close all_tables
deallocate all_tables
set nocount off