SQL 2008 存储过程 临时表如何清空呢!
CREATE procedure [dbo].[getTableRecord]
as
--清空数据
truncate table FirewallLog
--定义临时表存储表名
declare @tables table(name varchar(50))
--数据库名称
declare @databaseName varchar(200)
--数据库所对应的表名
declare @tbName varchar(200)
declare cursor_database cursor for
select
'ldy.'+data.name
from ldy.master.sys.databases data
where Convert(varchar(100),create_date,23)=CONVERT(varchar(90), GETDATE(), 23)
and SUBSTRING(data.name,17,3) ='FWS'
order by [name]
open cursor_database
fetch next from cursor_database into @databaseName
while @@fetch_status=0
begin
insert into @tables
exec('select ''dbo.''+name from '+@databaseName+'.sys.tables')
declare cursor_tb cursor for
select name from @tables order by [name]
open cursor_tb
fetch next from cursor_tb into @tbName
while @@fetch_status=0
begin
insert into FirewallLog
exec('select * from '+@databaseName+'.'+@tbName+'')
fetch next from cursor_tb into @tbName
end
close cursor_tb
deallocate cursor_tb
fetch next from cursor_database into @databaseName
end
close cursor_database
deallocate cursor_database
[/code]