SQLServerを解析してExcelのすべてのシートを取得する方法

Eディスクルートディレクトリの新しいExcelファイルaa.xls後に次のコードをテストします.

 
  
use tempdb 
  
go 
  
if (object_id ('udf_getExcelTableNames' ) is not null ) 
  
    drop function dbo .udf_getExcelTableNames 
  
go 
  
create function udf_getExcelTableNames (@filename varchar (1000 )) 
  
returns @t table (id int , name varchar (255 )) 
  
as 
  
begin 
  
    declare    
  
    @error int , @obj int , @c int , @sheetname varchar (255 ) , @sheetstring varchar (255 ) 
  
 
  
    exec @error = sp_oacreate 'Excel.Application' , @obj   out   
  
    exec @error = sp_oamethod @obj , 'Workbooks.Open' , @c out , @filename 
  
    exec @error = sp_oagetproperty @obj , 'ActiveWorkbook.Sheets.Count' , @c    out 
  
    while (@c > 0 ) 
  
    begin 
  
        set @sheetstring = 'ActiveWorkbook.Sheets(' + ltrim (@c )+ ').Name' 
  
        exec @error = sp_oagetproperty @obj , @sheetstring , @sheetname    out 
  
        insert into @t select @c , @sheetname 
  
        set @c = @c - 1 
  
    end 
  
    exec @error = sp_oadestroy @obj   
  
    return 
  
end 
  
go 
  
select * from dbo .udf_getExcelTableNames ('e:/aa.xls' ) 
  
/*--  
  
3    Sheet3 
  
2    Sheet2 
  
1    Sheet1 
  
*/