| |
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"
|
Questions? Suggestions? Concerns? - email me to [email protected]
Greg Dubinovsky � 2006