日期:2014-05-18  浏览次数:20593 次

这么晚了,不知还有没有人在~~好心人看一下,卡住了
select   name   from   sysdatabases   where   left(name,3)   = 'AK '
这条语句当然是从MASTER数据库中取出所以符合条件的实体名了

Select   FKey,FValue,FDescription   From   AK0001..t_systemprofile   Where   FKey   = '1 '   and   FDescription   = 'BB '
这条语句当然是去每一个符合条件数据库实体中找到一个t_systemprofile的表的几个字段(注意也可能数据中没有这个表,需要作一个判断),然后把所有结果拼结起来,其样式如:

name       FKey         FValue         FDescription  
----------------------------------------
AK001         1                   2                 BB
AK002         1                   3                 BB
AK003         1                   4                 BB
AK004


需要使用游标处理

------解决方案--------------------
create table #TMP (name sysname, FKey varchar(10), FValue varchar(10), FDescription varchar(100))

Declare @DataName varchar(20)
Declare mycur Cursor
For select name from master.dbo.sysdatabases where left(name,2) = 'AK '
Open mycur
Fetch Next From mycur into @DataName

While(@@fetch_status=0)
Begin
if object_id(@DataName + '..t_systemprofile ') is not null
exec ( 'insert #TMP Select name,FKey,FValue,FDescription From ' + @DataName + '..t_systemprofile Where FKey = ' '1 ' ' and FDescription = ' 'BB ' ' ')
Fetch Next From mycur into @DataName
End

Close mycur
Deallocate mycur

------解决方案--------------------
--改一下,数据库名称要加引号

Declare @DataName varchar(20)
declare @sql varchar(8000)
set @sql= ' '
Declare mycur Cursor
For select name from sysdatabases where left(name,3) = 'AK '
Open mycur
Fetch Next From mycur into @DataName

While(@@fetch_status=0)
Begin
if object_id(@DataName+ '..t_systemprofile ') is not null
begin
set @sql=@sql+
' Select ' ' '+@DataName+ ' ' ' AS name,FKey,FValue,FDescription '+
'From '+@DataName + '..t_systemprofile Where FKey = ' '1 ' ' and FDescription = ' 'BB ' ' union all '
end
Fetch Next From mycur into @DataName
End

Close mycur
Deallocate mycur
if @sql <> ' '
begin
set @sql=left(@sql,len(@sql)-10)
exec(@sql)
end