(Azure Synapse) How to get the environment name in SQL Script?

user_6565296 0
Viewed 508

In Synapse Analytics, we have four environments :

"****devsa"

"****qasa"

"****uatsa"

"****prodsa"

Here is my script to create an external data source :

IF NOT EXISTS (SELECT * FROM sys.external_data_sources WHERE name = 'Datalake')
BEGIN
CREATE EXTERNAL DATA SOURCE [DataLake]
WITH (LOCATION = 'https://****devsa.blob.core.windows.net/');
END;

How to variablize the location name ?

My goal is being able to run this script on any environment.

azure-synapse
1 Answers

The @@servername function is not supported in Azure Synapse, but you can use SERVERPROPERTY for similar results. A simple example, tested on dedicated and serverless SQL pools:

SELECT SERVERPROPERTY( 'ServerName' )

You could assign the result to a variable, check its contents with Like and wildcards (%). A simple example:

DECLARE @serverName VARCHAR(100) = CAST( SERVERPROPERTY( 'ServerName' ) AS VARCHAR(100) )
DECLARE @environment VARCHAR(10)

SELECT @serverName sn

IF @serverName Like '%dev'
    SET @environment = 'dev'
ELSE IF @serverName Like '%qasa'
    SET @environment = 'QA'
ELSE
    RAISERROR( 'Unable to determine environment for server %s.', 16, 1, @serverName )

SELECT @serverName sn, @environment e;

NB Having no wildcard (%) at the end of the match string is equivalent to 'ends with'. Double-check to see if your server names can be longer than 100 and adjust accordingly.

user_15275040
Related