导出所有用户表到excel

最新推荐文章于 2022-08-24 11:24:59 发布

weixin_30267785

最新推荐文章于 2022-08-24 11:24:59 发布

阅读量68

点赞数

原文链接：http://www.cnblogs.com/catcn/archive/2009/04/30/1446770.html

版权

导出所有用户表到excel，结合上一个发的存储过程。

/* --
导出所有用户表到excel
-- */
CREATE proc 导出所有用户表
as

DECLARE @tb_name varchar ( 300 )

DECLARE tbname_cursor CURSOR FOR
select o.name from dbo.sysobjects o
where OBJECTPROPERTY (o.id, N ' IsUserTable ' ) = 1
and o.name <> ' dtproperties '
order by o.name

OPEN tbname_cursor

-- Perform the first fetch and store the values in variables.
-- Note: The variables are in the same order as the columns
-- in the SELECT statement.

FETCH NEXT FROM tbname_cursor
INTO @tb_name

-- Check @@FETCH_STATUS to see if there are any more rows to fetch.
WHILE @@FETCH_STATUS = 0
BEGIN

    -- 导出所有用户表
    exec ( ' p_exporttb @sqlstr= '' select * from ' + @tb_name + ' '' ,@path= '' c:\abc '' ,@fname= ''' + @tb_name + ' .xls '' ,@sheetname= ''' + @tb_name + '''' )

    FETCH NEXT FROM tbname_cursor
    INTO @tb_name

END

CLOSE tbname_cursor
DEALLOCATE tbname_cursor

转载于:https://www.cnblogs.com/catcn/archive/2009/04/30/1446770.html