CODEWITHSUNDEEP
sqlsql-server
itmannz
itmannz

Reputation: 599

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]

Upvotes: 0

Views: 212

Answers (2)

sepupic
sepupic

Reputation: 8687

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.

Upvotes: 0

GMB
GMB

Reputation: 222382

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)

Upvotes: 1

Related Questions