Build a connection string

ConnectionString.sql

DROP FUNCTION IF EXISTS ConnectionString;
GO

CREATE FUNCTION ConnectionString(@includeProperties bit = 1)
RETURNS nvarchar(1000)
AS
BEGIN
    -- Retrieve server name
    DECLARE @hostName nvarchar(128);
    SET @hostName = CAST(host_name() AS nvarchar(128));

    DECLARE @machineName nvarchar(128);
    SET @machineName = CAST(SERVERPROPERTY('MachineName') AS nvarchar(128));
    SET @machineName = CASE WHEN @hostName = @machineName THEN '(local)' ELSE @machineName END;

    DECLARE @instanceName nvarchar(128);
    SET @instanceName = CAST(SERVERPROPERTY('InstanceName') AS nvarchar(128));
    SET @instanceName = LEFT(@instanceName, CHARINDEX('#', @instanceName + '#') - 1);
    SET @instanceName = CASE WHEN @instanceName = 'LOCALDB' THEN 'MSSQLLocalDB' ELSE @instanceName END;

    -- Build server name
    DECLARE @serverName nvarchar(128);
    SET @serverName = CONCAT(@machineName, '\', @instanceName);

    -- Retrieve database name
    DECLARE @databaseName nvarchar(128);
    SET @databaseName = DB_NAME();

    -- Retrieve current login
    DECLARE @currentLogin nvarchar(128);
    SET @currentLogin = CAST(SUSER_SNAME() AS nvarchar(128));

    -- Check if MARS is enabled
    DECLARE @isMarsEnabled bit;
    SET @isMarsEnabled = CASE WHEN CONNECTIONPROPERTY('IsMARS') = 1 THEN 1 ELSE 0 END;

    -- Check if connection is encrypted
    DECLARE @isEncrypted bit;
    SET @isEncrypted = CASE WHEN CONNECTIONPROPERTY('Encrypt') = 1 THEN 1 ELSE 0 END;

    -- Check if integrated security is used
    DECLARE @isIntegratedSecurity bit;
    SET @isIntegratedSecurity = CASE WHEN CAST(SERVERPROPERTY('IsIntegratedSecurityOnly') AS bit) = 1 THEN 1 ELSE 0 END;

    -- Check application name
    DECLARE @applicationName nvarchar(128);
    SET @applicationName = APP_NAME();

    -- Retrieve language
    DECLARE @currentLanguage nvarchar(128);
    SET @currentLanguage = CAST(@@language AS nvarchar(128));

    -- Retrieve collation
    DECLARE @collation nvarchar(128);
    SET @collation = CAST(DATABASEPROPERTYEX(DB_NAME(), 'Collation') AS nvarchar(128));

    -- Retrieve compatibility level
    DECLARE @compatibilityLevel tinyint;
    SET @compatibilityLevel = CONVERT(tinyint, DATABASEPROPERTYEX(DB_NAME(), 'CompatibilityLevel'));

    -- Build connection string
    DECLARE @connectionString nvarchar(1000);
    SET @connectionString = N'Data Source=' + @serverName +
                            N';Initial Catalog=' + @databaseName +
                            N';User Id=' + @databaseName +
                            N';Password=' + @databaseName +
                            CASE WHEN @includeProperties = 1 THEN
                                CONCAT(N';Integrated Security=', CASE WHEN @isIntegratedSecurity = 1 THEN N'True' ELSE N'False' END,
                                       N';MultipleActiveResultSets=', CASE WHEN @isMarsEnabled = 1 THEN N'True' ELSE N'False' END,
                                       N';Encrypt=', CASE WHEN @isEncrypted = 1 THEN N'True' ELSE N'False' END,
                                       N';Application Name=', @applicationName,
                                       N';Language=', @currentLanguage,
                                       N';Collation=', @collation,
                                       N';CompatibilityLevel=', CAST(@compatibilityLevel AS nvarchar(10))) 
                            ELSE N'' END;

    RETURN @connectionString;
END;
GO

SELECT dbo.ConnectionString(0) AS AdoConnStr;