Mobile Management For SQL Server(s!) - Siccolo - SQL Management Tool
Main
How to Mass Export All Tables Into Excel Files
 
First, we need to be able export field names out of the table:
	drop view vw_field_names
	go

	create view vw_field_names
	as  
	select so.name	as table_name
	, sc.name  as field_name
	, ( select count(*) from syscolumns sc2 
		where sc2.id = sc.id 
			AND sc2.number =  sc.number 
			AND sc2.colid  <= sc.colid ) as position
	from syscolumns sc,sysobjects so  
	where sc.id = so.id  
	go
	
Then, in a cursor export data from a table and save into excel file:
	set nocount on

	declare @name varchar(255)
	declare @command varchar(7000)
	declare @index int
	declare @field_names_command varchar(8000)

	declare @file_name varchar(500),
		@fields_file_name varchar(500),
		@path varchar(255)

	-- folder for a database already created!!!
	set @path = 'c:\' + db_name() + '\'
	print @path

	set @index  = 1

	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 @name
	while @@fetch_status=0
	begin
		set @file_name = @path + @name + '.xls'
		set @fields_file_name = @path + @name + '_column_info.txt'
		print convert(varchar, @index) + ' ' + @name + '		-->'  + @file_name

		-- *********************************************************************************
		-- get field names:
		set @field_names_command = 'select field_name from ' + db_name() + '.dbo.vw_field_names ' +
				' where table_name = ''' + @name + ''''+
				' order by position '
		print @field_names_command

		set @command = 'bcp ' + '"'  + @field_names_command + '"'  +
			' queryout ' + @fields_file_name + ' -c -t, -T -Soraclepri '
		print @command
		exec master..xp_cmdshell @command, NO_OUTPUT

		-- *********************************************************************************
		-- get data into excel:
		set @command = 'bcp ' + db_name() + '.dbo.' + @name + 
			' out ' + @file_name + ' -c -t\t -T -Soraclepri '

		print @command

		exec master..xp_cmdshell @command, NO_OUTPUT

		fetch next from all_tables into @name
		set @index  = @index +1

	end

	close all_tables 
	deallocate all_tables 

	set nocount off
	
Side Note:
BCP utility - bulk copy
The bcp utility copies data between an instance of Microsoft® SQL Server™ and a data file in a user-specified format.
Side Note:
Cursor
Cursors are an extension to result sets that provide mechanism to work with one row or a small block of rows at a time.
Side Note:
xp_cmdshell - eXtended SQL stored procedure.
Executes a given command string as an operating-system command shell and returns any output as rows of text. Grants nonadministrative users permissions to execute xp_cmdshell. xp_cmdshell operates synchronously. Control is not returned until the command shell command completes.

Where xp_cmdshell {'command_string'} [, no_output]

For example, to retireve all the folders for a given disk path:
	create table #tmp (directory_name varchar(255))

	insert into #tmp
	exec master.dbo.xp_cmdshell "dir d:\ /B /A:D /S"

	select * from #tmp --for xml auto

	drop table #tmp
	
or to get server information:
	exec master.dbo.xp_cmdshell "net statistics server"
	

Article keywords: Excel, Import, create view, declare, cursor, fetch, xp_cmdshell, bcp

Back To Articles Page

Free Mobile Management For SQL Server(s!) - Siccolo - SQL Management ToolQuestions? Suggestions? Concerns? - email me to siccolo_mobile_management@yahoo.com    Greg Dubinovsky © 2006
or share your thoughts at Siccolo Blog

Web being sponsor - Mid-Atlantic Processing. Well being sponsor - Clarity MediSpa. Hairless sponsor - Clarity MediSpa Laser Hair Removal.