SQL Server name in query

I would like to declare SQL Server name in a variable so that I can run query on any server by just changing server name.

DECLARE @ServerName NVARCHAR(100)
SET @ServerName = 'dbServer'

[@ServerName].[dbo].[Product]
728x90

2 Answers SQL Server name in query

Macro substitutions, as in your SQL code, are not supported in SQL Server.

A possible solution is to use dynamic SQL. However, do beware of SQL injection when using this technique ; do not set the variable from a POSTed value.

DECLARE @ServerName nvarchar(100)
SET @ServerName = 'dbServer';

DECLARE @SQL varchar(max) 
SET @SQL = 'SELECT * From ' + QUOTENAME(@ServerName) + '.[dbo].[Product]'
Exec(@SQL)

4 months ago

You can use Central Management Servers to do this.

Here is official documentation Create a Central Management Server and Server Group

Execute Statements Against Multiple Servers Simultaneously

And here is more detailed example with the pictures: Execute SQL Server query on multiple servers at the same time

Onced you've set up Central Management Servers you can execute your statements on all of these servers. It's very usefull for administrator's tasks when you want to control job executions, etc on all of your servers.

4 months ago