Declare the server name as a variable in MS Sql Server 2016

Question

I have a big script file and I need to use it on another server. I need to edit the name of the server one time at the beggining using Declare so I can be able to use the same script on multiple servers only by changing the value of the variable.

something like that:

Declare @Quell nvarchar(100)
SET @Quell = '[server1].[dbo]'

SELECT * From @Quell.[Documents] 

but it did not work.

how to do it? thank you

Answer 1

Unfortunately macro substitution is not permitted in SQL Server, but you can use dynamic SQL.

Declare @Quell nvarchar(100)
SET @Quell = '[server1].[dbo]'

Declare @SQL varchar(max) 
SET @SQL = 'SELECT * From ' + @Quell +'.[Documents]'
Exec(@SQL)

Answer 2

If you are running the script from SQL Server Management Studio, you can use a SQLCMD variable and run the script in SQLCMD mode (Query-->SQLCMD Mode). A SQLCMD script can also be executed using the SQLCMD command-line utility with the variable value(s) passed as command-line arguments.

Sample script:

:SETVAR Quell "[server1].[dbo]"

SELECT * From $(Quell).[Documents];