258.导出表结构为excel

--导出表结构为Excel
declare @dbname sysname,@fname nvarchar(1000)
select @dbname=N'pubs'	               --要生成结构的库名
	,@fname=N'c:\'+@dbname+N'.xls' --导出的excel文件名

--导出excel处理
declare @s nvarchar(4000)
set @s='bcp "'+
	+N'select top 100 percent '
	+N' case c.colid when 1 then o.name else N'''' end as 表名,'
	+N' c.colid as 序号,'
	+N' c.name as 字段名,'
	+N' t.name 数据类型,'
	+N' c.prec as 长度,'
	+N' p.value as 字段说明,'
	+N' m.text as 默认值'
	+N' from '+quotename(@dbname)+N'.dbo.sysobjects o'
	+N' inner join '+quotename(@dbname)+N'.dbo.syscolumns c on o.id=c.id'
	+N' inner join '+quotename(@dbname)+N'.dbo.systypes t on c.xusertype=t.xusertype'
	+N' left join '+quotename(@dbname)+N'.dbo.sysproperties p on c.id=p.id and c.colid = p.smallid'
	+N' left join '+quotename(@dbname)+N'.dbo.syscomments m on c.cdefault=m.id'
	+N' where o.xtype in(N''U'') and o.status>=0'
	+N' order by c.id,c.colid'
	+N'" queryout "'+@fname
	+N'" /P"" /w'
exec master..xp_cmdshell @s,no_output
go


--导出表结构为Excel

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