258.导出表结构为excel

水深无声 2023-12-27 06:37 44阅读 0赞

--导出表结构为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