求问个自动选择数据库并且能循环的语句
有几百个数据库 需要进行同一个统计语言 1个1个选择数据库太麻烦了 求个可以自动选择数据库并且运行查询的语句
declare @d nvarchar(50),@id1 int,@id2 int,@databasename nvarchar(255)
DECLARE @str nvarchar(4000) ,@databasecon nvarchar(4000)
set @id1=603
set @databasecon='RXZJ_mydb_gamelog%'
select @id2=684
select @d=name from sys.databases where @id1=database_id
while(@id1<=@id2)
BEGIN
SET @databasename=''
select @databasename= name from sys.databases WHERE database_id=@id1
IF(@databasename like @databasecon)
BEGIN
set @str=' use '+@databasename
print @str
EXEC SP_EXECUTESQL @str
declare @i datetime
set @i='2014-01-01'
select CONVERT(nvarchar(50),@i,23)as date ,等级分布,COUNT(*)as renshu from (
select case when srclvl>0and srclvl <=10 then '10级下'
when srclvl>10and srclvl <=20then'10-20级'
when srclvl>20and srclvl <=30then'21-30级'
when srclvl>30and srclvl <=40then'31-40级'
when srclvl>40and srclvl <=50then'41-50级'
when srclvl>50and srclvl <=60then'51-60级'
when srclvl>60and srclvl <=70then'61-70级'
when srclvl>70and srclvl <=80then'71-80级'
when srclvl>80and srclvl <=90then'81-90级'
when srclvl>90and srclvl <=100then'91-100级'
when srclvl>140and srclvl <=150then'一转41-50级'
when srclvl>150and srclvl <=160then'一转51-60级'
when srclvl>160and srclvl <=170then'一转61-70级'
when srclvl>170and srclvl <=180then'一转71-80级'
when srclvl>180and srclvl <=190then'一转81-90级'
when srclvl>190and srclvl <=200then'一转91-100级'
when srclvl>240and srclvl <=250then'二转41-50级'
when srclvl>250and srclvl <=260then'二转51-60级'
when srclvl>260and srclvl <=270then'二转61-70级'
when srclvl>270and srclvl <=280then'二转71-80级'
when srclvl>280and srclvl <=290then'二转81-90级'
end 等级分布 from [1_TS_item_log_tbl])a group by 等级分布 order by 等级分布
end
set @id1=@id1+1
end
declare @d nvarchar(50),@id1 int,@id2 int,@databasename nvarchar(255)
DECLARE @str nvarchar(4000) ,@databasecon nvarchar(4000)
declare @i datetime
set @i='2014-01-01'
set @id1=603
set @databasecon='RXZJ_mydb_gamelog%'
select @id2=684
select @d=name from sys.databases where @id1=database_id
while(@id1<=@id2)
BEGIN
SET @databasename=''
select @databasename= name from sys.databases WHERE database_id=@id1
IF(@databasename like @databasecon)
BEGIN
set @str=' use '+@databasename+';'
set @str = @str +
'
select CONVERT(nvarchar(50),'''+ convert(varchar(10),@i,120) +''',23)as date ,等级分布,COUNT(*)as renshu from (
select case when srclvl>0and srclvl <=10 then ''10级下''
when srclvl>10and srclvl <=20then''10-20级''
when srclvl>20and srclvl <=30then''21-30级''
when srclvl>30and srclvl <=40then''31-40级''
when srclvl>40and srclvl <=50then''41-50级''
when srclvl>50and srclvl <=60then''51-60级''
when srclvl>60and srclvl <=70then''61-70级''
when srclvl>70and srclvl <=80then''71-80级''
when srclvl>80and srclvl <=90then''81-90级''
when srclvl>90and srclvl <=100then''91-100级''
when srclvl>140and srclvl <=150then''一转41-50级''
when srclvl>150and srclvl <=160then''一转51-60级''
when srclvl>160and srclvl <=170then''一转61-70级''
when srclvl>170and srclvl <=180then''一转71-80级''
when srclvl>180and srclvl <=190then''一转81-90级''
when srclvl>190and srclvl <=200then''一转91-100级''
when srclvl>240and srclvl <=250then''二转41-50级''
when srclvl>250and srclvl <=260then''二转51-60级''
when srclvl>260and srclvl <=270then''二转61-70级''
when srclvl>270and srclvl <=280then''二转71-80级''
when srclvl>280and srclvl <=290then''二转81-90级''
end 等级分布 from [1_TS_item_log_tbl])a
group by 等级分布 order by 等级分布
'
print @str
--EXEC SP_EXECUTESQL @str
end
set @id1=@id1+1
end
