SQL2008多数据库查询表或删表

SQL2008和2005多数据库操作，还是有一定的区别，采用这个方法是最好的，以下提供两种方法

set nocount off
declare @DBName as varchar(255)
Create table #temp(Faccount varchar(255),FDate varchar(255),FDescription varchar(255))
declare cs_DBName cursor for select name from sysdatabases where name like '%sy%' order by name
open cs_DBName
fetch next from cs_DBName into @DBName
while @@fetch_Status =0
begin
exec('DELETE FROM '+@DBName+'.dbo.t_UserProfile')
--select * from @DBName.dbo.t_log where substring(convert(varchar(108),fDATE,120),1,16)=@datetime
--
-- SELECT * FROM #temp drop table #temp
fetch next from cs_DBName into @DBName
end
close cs_DBName
DEALLOCATE cs_DBName

-----------------------------------------------------------------------------------

set nocount off
declare @datetime as varchar(255)
set @datetime='''2011-07-27 10:58'''
declare @DBName as varchar(255)
Create table #temp(Faccount varchar(255),FDate varchar(255),FDescription varchar(255))
declare cs_DBName cursor for select name from sysdatabases where name like '%sy%' order by name
open cs_DBName
fetch next from cs_DBName into @DBName
while @@fetch_Status =0
begin
exec('insert into #temp select '''+@DBName+''',fdate,FDescription from '+@DBName+ '.dbo.t_log where substring(convert(varchar(108),fDATE,120),1,16)='+@datetime+'')
--select * from @DBName.dbo.t_log where substring(convert(varchar(108),fDATE,120),1,16)=@datetime
--
-- SELECT * FROM #temp drop table #temp
fetch next from cs_DBName into @DBName
end
close cs_DBName
DEALLOCATE cs_DBName
select * from #temp
drop table  #temp

转载于:https://www.cnblogs.com/Anders888/archive/2011/08/03/2126453.html