Sql Server 2000/2008 用Sql导出数据表结构（包括注释）到Excel

--Sql Server 2000

declare @id int,
    @name varchar(100),
    @s varchar(8000)
declare cur_1 cursor for select id,name from sysobjects where xtype='U' --and id=537768973
open cur_1
fetch cur_1 into @id,@name
while(@@fetch_status=0)
begin
    if exists(select * from tempdb..sysobjects where id=object_id('tempdb..##temp'))
    drop table ##temp
    create table ##temp(名称 varchar(100),数据类型 varchar(100),长度 varchar(100),默认值 varchar(100),注释 varchar(100))
    Insert into ##temp(名称,数据类型,长度,默认值,注释)
    select '表名：',Cast(@name as varchar(100)),'','',''
    union all
    select '名称','数据类型','长度','默认值','注释'
    union all
    select Cast(t3.name as varchar(100)) 名称,Cast(t5.name as varchar(20)) 数据类型,Cast(t3.length as varchar(20)) 长度,isnull(Cast(t6.text as varchar(100)),'') 默认值,isnull(Cast(t4.value as varchar(1000)),'') 注释
    from syscolumns t3 
    left join sysproperties t4 on t3.id=t4.id and t3.colid=t4.smallid 
    left join systypes t5 on t3.xtype=t5.xusertype
    left join syscomments t6 on t3.cdefault=t6.id
    where t3.id=@id  
    union all
    select '','','','',''
    union all
    select '','','','',''
    union all
    select '-','-','-','-','-'
 
    --declare @s varchar(8000)
    set @s='Insert Into OpenRowSet(''Microsoft.Jet.OLEDB.4.0'',''Excel 8.0;DataBase=C:\temp.xls'',''select * from [Sheet1$]'') select * from ##temp'
    Exec(@s) 

fetch cur_1 into @id,@name
end
deallocate cur_1

 

--Sql Server 2008

declare @id int,
    @name varchar(100),
    @s varchar(8000)
declare cur_1 cursor for select id,name from sysobjects where xtype='U' --and id=37575172
open cur_1
fetch cur_1 into @id,@name
while(@@fetch_status=0)
begin
    if exists(select * from tempdb..sysobjects where id=object_id('tempdb..##temp'))
    drop table ##temp
    create table ##temp(名称 varchar(100),数据类型 varchar(100),长度 varchar(100),默认值 varchar(100),注释 varchar(100))
    Insert into ##temp(名称,数据类型,长度,默认值,注释)
    select '表名：',Cast(@name as varchar(100)),'','',''
    union all
    select '名称','数据类型','长度','默认值','注释'
    union all
    select Cast(t3.name as varchar(100)) 名称,Cast(t5.name as varchar(20)) 数据类型,Cast(t3.length as varchar(20)) 长度,isnull(Cast(t6.text as varchar(100)),'') 默认值,isnull(Cast(t4.value as varchar(1000)),'') 注释
    from syscolumns t3 
    left join sys.extended_properties t4 on t3.id=t4.major_id and t3.colorder=t4.minor_id 
    left join systypes t5 on t3.xtype=t5.xusertype
    left join syscomments t6 on t3.cdefault=t6.id
    where t3.id=@id  
    union all
    select '','','','',''
    union all
    select '','','','',''
    union all
    select '-','-','-','-','-'
 
    --declare @s varchar(8000)
    set @s='Insert Into OpenRowSet(''Microsoft.Jet.OLEDB.4.0'',''Excel 8.0;DataBase=C:\temp.xls'',''select * from [Sheet1$]'') select * from ##temp'
    Exec(@s) 

fetch cur_1 into @id,@name
end
deallocate cur_1