在FROM语句中使用变量 [英] use variable in FROM statement

问题描述

我试图创建一个查询，该查询将对 sys.server 中每台服务器中特定数据库中的特定表执行一次。

I am trying to create a query that will be executed once for a specific table in a specific database in each server in sys.server.

对于每个 server.database.dbo.table 我想知道其内容。

For each server.database.dbo.table I want to know the contents.

所以我需要的是这样的东西

So what I need is something like:

declare @numrows int = (select count(*) from sys.servers)
declare @i int = 1

while @i <= @numrows
BEGIN
declare @servername varchar(max) = (select servernaam from #servers where rij = @i)

select * from @servername.DATABASE.DBO.TABLE

set @i = @i+1

END

但是，<$中的 @servername c $ c> @ servername.DATABASE.DBO.TABLE 似乎不起作用。

However, the @servername in @servername.DATABASE.DBO.TABLE does not seem to work.

建议？感谢您与我的思考。

Suggestions? Thanks for thinking with me.

推荐答案

您必须使用动态sql：

You have to use dynamic sql:

declare @numrows int = (select count(*) from sys.servers)
declare @i int = 1
declare @Sql(1000)
declare @servername varchar(max)

while @i <= @numrows
BEGIN
select @servername = servernaam 
from #servers where rij = @i

set @Sql = 'select * from '+@servername+'.DATABASE.DBO.TABLE'
exec(@Sql)

set @i = @i+1

END

此处是有关exec的更多信息。

Here is more informations about exec.

这篇关于在FROM语句中使用变量的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！