导出所有用户表到excel
导出所有用户表到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
导出所有用户表到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
