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]
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)
GMB 4 months ago
You can use Central Management Servers to do this.
Here is official documentation Create a Central Management Server and Server Group
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.