要求:
將指定的 MsSql 中指定的數據庫, 如Master中的各個表結構導出到Excel中!
導出的大概格式是:
第一個 Sheet 保存各個對應的Table 名字
然後, 下面每個Sheet 依次保存各個Talbe 的字段名結構, 如下:
Name Type Length isNull
1
2
...
---------------------------------------------------------------
--2.调用上面的存储过程实现楼主的导出要求.
declare @id varchar(20),@tb sysname,@sql nvarchar(4000)
declare tb cursor for
select id=cast(id as varchar),name from sysobjects where xtype='U' and status>0
open tb
fetch next from tb into @id,@tb
while @@fetch_status=0
begin
set @sql='select top 100 percent 序号=c.colid
,c.name as 字段名
,t.name 数据类型
,c.prec as 长度
,p.value as 字段说明
,m.text as 默认值
from syscolumns c
inner join systypes t on c.xusertype=t.xusertype
left join sysproperties p on c.id=p.id and c.colid = p.smallid
left join syscomments m on c.cdefault=m.id
where c.id='+@id+'
order by c.colid'
exec p_exporttb @sqlstr=@sql,@path='c:',@fname='aa.xls',@sheetname=@tb
fetch next from tb into @id,@tb
end
close tb
deallocate tb