Search code examples
sqlsql-serverdeclareserver-name

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

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

Solution

  • 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)