Hey guys!

In this post, I would like to share with you some ways to identify the version and edition of all server instances using xp_regread and Powershell. I saw a user sending this question in a Whatsapp group and I didn't find many solutions on the internet, which motivated me to write this article.

How to identify the version and edition of the logged in instance

If you want to identify the information about the instance you are connected to, it is very simple:

SELECT @@VERSION

Result:

You can also identify this information using the SERVERPROPERTY function:

SELECT
    SERVERPROPERTY('MachineName') AS [MachineName],
    SERVERPROPERTY('ServerName') AS [ServerName],
    SERVERPROPERTY('InstanceName') AS [Instance],
    SERVERPROPERTY('IsClustered') AS [IsClustered],
    SERVERPROPERTY('ComputerNamePhysicalNetBIOS') AS [ComputerNamePhysicalNetBIOS],
    CASE LEFT(CONVERT(VARCHAR, SERVERPROPERTY('ProductVersion')), 4)
        WHEN '8.00' THEN 'SQL Server 2000'
        WHEN '9.00' THEN 'SQL Server 2005'
        WHEN '10.0' THEN 'SQL Server 2008'
        WHEN '10.5' THEN 'SQL Server 2008 R2'
        WHEN '11.0' THEN 'SQL Server 2012'
        WHEN '12.0' THEN 'SQL Server 2014'
        WHEN '13.0' THEN 'SQL Server 2016'
        WHEN '14.0' THEN 'SQL Server 2017'
        WHEN '15.0' THEN 'SQL Server 2019'
        ELSE 'SQL Server 2019+'
    END AS [SQLVersionBuild],
    SERVERPROPERTY('Edition') AS [Edition],
    SERVERPROPERTY('ProductLevel') AS [ProductLevel],
    SERVERPROPERTY('ProductUpdateLevel') AS [ProductUpdateLevel],
    SERVERPROPERTY('ProductVersion') AS [ProductVersion],
    SERVERPROPERTY('ProductMajorVersion') AS [ProductMajorVersion],
    SERVERPROPERTY('ProductMinorVersion') AS [ProductMinorVersion],
    SERVERPROPERTY('ProductBuild') AS [ProductBuild],
    SERVERPROPERTY('ProductBuildType') AS [ProductBuildType],
    SERVERPROPERTY('ProductUpdateReference') AS [ProductUpdateReference],
    SERVERPROPERTY('ProcessID') AS [ProcessID],
    SERVERPROPERTY('Collation') AS [Collation],
    SERVERPROPERTY('IsFullTextInstalled') AS [IsFullTextInstalled],
    SERVERPROPERTY('IsIntegratedSecurityOnly') AS [IsIntegratedSecurityOnly],
    SERVERPROPERTY('FilestreamConfiguredLevel') AS [FilestreamConfiguredLevel],
    SERVERPROPERTY('IsHadrEnabled') AS [IsHadrEnabled],
    SERVERPROPERTY('HadrManagerStatus') AS [HadrManagerStatus],
    SERVERPROPERTY('IsXTPSupported') AS [IsXTPSupported],
    SERVERPROPERTY('BuildClrVersion') AS [Build CLR Version];

Result:

How to identify the version and edition of all instances

The problem in question is when you need to identify information from servers that have multiple instances, which can end up making this activity a bit laborious, depending on the number of server instances.

How to identify the version and edition of all instances using T-SQL

To meet this need, I wrote the script below, which uses the system procedure xp_regread to search the name of the instances in the Windows registry. After that, it retrieves the information from each of these instances and still uses the procedure xp_cmdshell to read information from the ERRORLOG file of each instance to identify some information (practically simulates @@VERSION).

View source code
-------------------------------------------------------------------------
-- Declaração de variáveis
-------------------------------------------------------------------------

DECLARE
    @Contador INT,
    @Total INT,
    @ValorExiste INT,
    @Platform VARCHAR(100),
    @ChaveAtual NVARCHAR(500),
    @ValorAtual NVARCHAR(500),
    @SQL NVARCHAR(MAX),
    @Comando VARCHAR(8000);


DECLARE @PortasTCP TABLE
(
    PortType NVARCHAR(180),
    [Port] INT
);


DECLARE @Instancias TABLE
(
    [InstanceID] INT IDENTITY(1, 1) NOT NULL PRIMARY KEY,
    [InstName] NVARCHAR(180),
    [Folder] NVARCHAR(50),
    [StaticPort] INT NULL,
    [DynamicPort] INT NULL,
    [Platform] INT NULL,
    [SQLDataRoot] NVARCHAR(255),
    [SQLPath] NVARCHAR(255),
    [Collation] NVARCHAR(100),
    [SQLVersion] NVARCHAR(100),
    [Edition] NVARCHAR(100),
    [Version] NVARCHAR(50),
    [PatchLevel] NVARCHAR(50),
    [FullVersion] NVARCHAR(255)
);


DECLARE @Plat TABLE
(
    Id INT,
    [Name] VARCHAR(180),
    InternalValue VARCHAR(100),
    Charactervalue VARCHAR(100)
);


DECLARE @Keyexist TABLE
(
    Keyexist INT
);


IF (OBJECT_ID('tempdb..#ErrorLog') IS NOT NULL) DROP TABLE #ErrorLog
CREATE TABLE #ErrorLog (
    linha INT IDENTITY(1,1) NOT NULL,
    conteudo NVARCHAR(MAX) NULL
);



-------------------------------------------------------------------------
-- Recupera informações da instância, que são comuns entre todas do servidor
-------------------------------------------------------------------------

INSERT INTO @Plat
EXEC xp_msver

SET @Platform = (SELECT TOP(1) 1 FROM @Plat WHERE [Name] = 'Platform' AND Charactervalue LIKE '%86%' ORDER BY 1)


-------------------------------------------------------------------------
-- Recupera o nome de todas as instâncias do servidor
-------------------------------------------------------------------------

IF (@Platform IS NULL)
BEGIN


    INSERT INTO @Instancias ( InstName, Folder )
    EXEC master.dbo.xp_regenumvalues N'HKEY_LOCAL_MACHINE', N'SOFTWARE\Microsoft\Microsoft SQL Server\Instance Names\SQL'


    UPDATE @Instancias
    SET [Platform] = 64;


END
ELSE BEGIN


    INSERT INTO @Instancias ( InstName, Folder )
    EXEC xp_regenumvalues N'HKEY_LOCAL_MACHINE', N'SOFTWARE\Microsoft\Microsoft SQL Server\Instance Names\SQL';


    UPDATE @Instancias
    SET [Platform] = 32


END


INSERT INTO @Keyexist
EXEC xp_regread 'HKEY_LOCAL_MACHINE', N'SOFTWARE\Wow6432Node\Microsoft\Microsoft SQL Server\Instance Names\SQL';


SET @ValorExiste = (SELECT TOP(1) Keyexist FROM @Keyexist ORDER BY 1)

IF (@ValorExiste = 1)
BEGIN

    INSERT INTO @Instancias ( InstName, Folder )
    EXEC xp_regenumvalues N'HKEY_LOCAL_MACHINE', N'SOFTWARE\Wow6432Node\Microsoft\Microsoft SQL Server\Instance Names\SQL';

END


UPDATE
    @Instancias
SET
    [Platform] = 32
WHERE
    [Platform] IS NULL;


-------------------------------------------------------------------------
-- Recupera as portas TCP utilizadas pelas instâncias
-------------------------------------------------------------------------

SELECT
    @Total = MAX(InstanceID),
    @Contador = 1
FROM
    @Instancias;


WHILE (@Contador <= @Total)
BEGIN


    DELETE FROM @PortasTCP;


    SELECT
        @SQL = 'EXEC xp_instance_regread N''HKEY_LOCAL_MACHINE'', N''SOFTWARE\Microsoft\\Microsoft SQL Server\' + Folder + '\MSSQLServer\SuperSocketNetLib\Tcp\IPAll'', N''TCPDynamicPorts'''
    FROM
        @Instancias
    WHERE
        InstanceID = @Contador;


    INSERT INTO @PortasTCP
    EXEC sp_executesql @SQL;


    SELECT
        @SQL = 'EXEC xp_instance_regread N''HKEY_LOCAL_MACHINE'', N''SOFTWARE\Microsoft\\Microsoft SQL Server\' + Folder + '\MSSQLServer\SuperSocketNetLib\Tcp\IPAll'', N''TCPPort'''
    FROM
        @Instancias
    WHERE
        InstanceID = @Contador;


    INSERT INTO @PortasTCP
    EXEC sp_executesql @SQL;


    SELECT
        @SQL = 'EXEC xp_instance_regread N''HKEY_LOCAL_MACHINE'', N''SOFTWARE\Wow6432Node\Microsoft\\Microsoft SQL Server\' + Folder + '\MSSQLServer\SuperSocketNetLib\Tcp\IPAll'', N''TCPDynamicPorts'''
    FROM
        @Instancias
    WHERE
        InstanceID = @Contador;


    INSERT INTO @PortasTCP
    EXEC sp_executesql @SQL;


    SELECT
        @SQL = 'EXEC xp_instance_regread N''HKEY_LOCAL_MACHINE'', N''SOFTWARE\Wow6432Node\Microsoft\\Microsoft SQL Server\' + Folder + '\MSSQLServer\SuperSocketNetLib\Tcp\IPAll'', N''TCPPort'''
    FROM
        @Instancias
    WHERE
        InstanceID = @Contador;


    INSERT INTO @PortasTCP
    EXEC sp_executesql @SQL;


    UPDATE
        A
    SET
        StaticPort = C.[Port],
        DynamicPort = B.[Port]
    FROM
        @Instancias A
        INNER JOIN @PortasTCP B ON B.PortType = 'TCPDynamicPorts'
        INNER JOIN @PortasTCP C ON C.PortType = 'TCPPort'
    WHERE
        A.InstanceID = @Contador;


    SET @Contador = @Contador + 1;


END;



-------------------------------------------------------------------------
-- Recupera as outras informações da instância
-------------------------------------------------------------------------

SET @Contador = 1


WHILE (@Contador <= @Total)
BEGIN
    

    SELECT 
        @ChaveAtual = 'SOFTWARE\Microsoft\Microsoft SQL Server\' + Folder + '\Setup'
    FROM 
        @Instancias
    WHERE 
        InstanceID = @Contador

    
    -- SQLDataRoot
    EXEC master.dbo.xp_regread 
        @rootkey = 'HKEY_LOCAL_MACHINE',
        @key = @ChaveAtual,
        @value_name = 'SQLDataRoot',
        @value = @ValorAtual OUTPUT


    UPDATE @Instancias
    SET [SQLDataRoot] = @ValorAtual
    WHERE InstanceID = @Contador


    -- SQLPath
    EXEC master.dbo.xp_regread 
        @rootkey = 'HKEY_LOCAL_MACHINE',
        @key = @ChaveAtual,
        @value_name = 'SQLPath',
        @value = @ValorAtual OUTPUT


    UPDATE @Instancias
    SET [SQLPath] = @ValorAtual
    WHERE InstanceID = @Contador


    -- PatchLevel
    EXEC master.dbo.xp_regread 
        @rootkey = 'HKEY_LOCAL_MACHINE',
        @key = @ChaveAtual,
        @value_name = 'PatchLevel',
        @value = @ValorAtual OUTPUT


    UPDATE @Instancias
    SET 
        [PatchLevel] = @ValorAtual,
        [SQLVersion] = (
        CASE 
            WHEN LEFT(@ValorAtual, 1) = '8' THEN 'SQL Server 2000'
            WHEN LEFT(@ValorAtual, 1) = '9' THEN 'SQL Server 2005'
            WHEN LEFT(@ValorAtual, 4) = '10.5' THEN 'SQL Server 2008 R2'
            WHEN LEFT(@ValorAtual, 2) = '10' THEN 'SQL Server 2008'
            WHEN LEFT(@ValorAtual, 2) = '11' THEN 'SQL Server 2012'
            WHEN LEFT(@ValorAtual, 2) = '12' THEN 'SQL Server 2014'
            WHEN LEFT(@ValorAtual, 2) = '13' THEN 'SQL Server 2016'
            WHEN LEFT(@ValorAtual, 2) = '14' THEN 'SQL Server 2017'
            WHEN LEFT(@ValorAtual, 2) = '15' THEN 'SQL Server 2019'
        END)
    WHERE 
        InstanceID = @Contador


    -- Version
    EXEC master.dbo.xp_regread 
        @rootkey = 'HKEY_LOCAL_MACHINE',
        @key = @ChaveAtual,
        @value_name = 'Version',
        @value = @ValorAtual OUTPUT


    UPDATE @Instancias
    SET [Version] = @ValorAtual
    WHERE InstanceID = @Contador


    -- Edition
    EXEC master.dbo.xp_regread 
        @rootkey = 'HKEY_LOCAL_MACHINE',
        @key = @ChaveAtual,
        @value_name = 'Edition',
        @value = @ValorAtual OUTPUT


    UPDATE @Instancias
    SET [Edition] = @ValorAtual
    WHERE InstanceID = @Contador


    -- Collation
    EXEC master.dbo.xp_regread 
        @rootkey = 'HKEY_LOCAL_MACHINE',
        @key = @ChaveAtual,
        @value_name = 'Collation',
        @value = @ValorAtual OUTPUT


    UPDATE @Instancias
    SET [Collation] = @ValorAtual
    WHERE InstanceID = @Contador


    SET @Contador = @Contador + 1;


END



-------------------------------------------------------------------------
-- Recupera as informações da instância a partir do errorlog
-------------------------------------------------------------------------


DECLARE @Fl_Xp_CmdShell_Ativado BIT = (SELECT (CASE WHEN CAST([value] AS VARCHAR(MAX)) = '1' THEN 1 ELSE 0 END) FROM sys.configurations WHERE [name] = 'xp_cmdshell')
 
IF (@Fl_Xp_CmdShell_Ativado = 0)
BEGIN
 
    EXECUTE SP_CONFIGURE 'show advanced options', 1;
    RECONFIGURE WITH OVERRIDE;
    
    EXEC sp_configure 'xp_cmdshell', 1;
    RECONFIGURE WITH OVERRIDE;
    
END


SET @Contador = 1


WHILE (@Contador <= @Total)
BEGIN
    

    SELECT 
        @Comando = 'type "' + SQLPath + '\Log\ERRORLOG"'
    FROM 
        @Instancias
    WHERE 
        InstanceID = @Contador

    
    TRUNCATE TABLE #ErrorLog


    INSERT INTO #ErrorLog
    EXEC master.dbo.xp_cmdshell @Comando

    
    SET @ValorAtual = (SELECT LTRIM(RTRIM(SUBSTRING(conteudo, 35, 100))) FROM #ErrorLog WHERE linha = 1)
    SET @ValorAtual += ' ' + (SELECT LTRIM(RTRIM(SUBSTRING(conteudo, 2, 100))) FROM #ErrorLog WHERE linha = 4)


    UPDATE @Instancias
    SET [FullVersion] = @ValorAtual
    WHERE InstanceID = @Contador


    SET @Contador = @Contador + 1;


END



IF (@Fl_Xp_CmdShell_Ativado = 0)
BEGIN
 
    EXEC sp_configure 'xp_cmdshell', 0;
    RECONFIGURE WITH OVERRIDE;
 
    EXECUTE SP_CONFIGURE 'show advanced options', 0;
    RECONFIGURE WITH OVERRIDE;
 
END



-------------------------------------------------------------------------
-- Exibe as informações finais
-------------------------------------------------------------------------

SELECT
    SERVERPROPERTY('ComputerNamePhysicalNetBIOS') AS ServerName,
    A.InstName,
    A.[Folder],
    A.StaticPort,
    A.DynamicPort,
    A.[Platform],
    A.SQLPath,
    A.SQLVersion,
    A.Edition,
    A.[Version],
    A.PatchLevel,
    A.FullVersion
FROM
    @Instancias A

Result:

How to identify the version and edition of all instances using Powershell

Another way to capture the basic information of all instances of a server list is by using the Powershell script below:

View source code (using PowerShell only)
# Recupera o nome de todos os servidores do arquivo "servidores.txt"
$servers = get-content c:\temp\servidores.txt;

"servidor,instância,edição,versão" > "C:\temp\versao.csv"
 
# Iteração entre os servidores
foreach ($server in $servers) 
{
 
    $out = $null;
 
    # Verifica se o computador está online
    if (test-connection -computername $server -count 1 -ea 0) 
    {
 
        try 
        {
            
            $type = [Microsoft.Win32.RegistryHive]::LocalMachine;
            $regconnection = [Microsoft.Win32.RegistryKey]::OpenRemoteBaseKey($type, $server) ;
            $instancekey = "SOFTWARE\Microsoft\Microsoft SQL Server\Instance Names\SQL";
 
            try 
            {
                $openinstancekey = $regconnection.opensubkey($instancekey);
            }
            catch { 
                $out = $server + ", Nenhuma chave de registro do SQL Server encontrada";
            }
 
            # Recupera o nome das instâncias
            $instances = $openinstancekey.getvaluenames();
            
            # Iteração entre as instâncias do servidor
            foreach ($instance in $instances) 
            {
 
                $instancename = $openinstancekey.getvalue($instance);
                $instancesetupkey = "SOFTWARE\Microsoft\Microsoft SQL Server\" + $instancename + "\Setup"; 
 
                $openinstancesetupkey = $regconnection.opensubkey($instancesetupkey);
                $edition = $openinstancesetupkey.getvalue("Edition")
                $version = $openinstancesetupkey.getvalue("Version");
 
                switch -wildcard ($version) 
                {
                    "15*" {$versionname = "SQL Server 2019";}
                    "14*" {$versionname = "SQL Server 2017";}
                    "13*" {$versionname = "SQL Server 2016";}
                    "12*" {$versionname = "SQL Server 2014";}
                    "11*" {$versionname = "SQL Server 2012";}
                    "10.5*" {$versionname = "SQL Server 2008 R2";}
                    "10.4*" {$versionname = "SQL Server 2008";}
                    "10.3*" {$versionname = "SQL Server 2008";}
                    "10.2*" {$versionname = "SQL Server 2008";}
                    "10.1*" {$versionname = "SQL Server 2008";}
                    "10.0*" {$versionname = "SQL Server 2008";}
                    "9*" {$versionname = "SQL Server 2005";}
                    "8*" {$versionname = "SQL Server 2000";}
                    default {$versionname = $version;}
                }
 
                # Armazena os resultados na variável que será grava no arquivo CSV
                $out += $server + "," + $instancename + "," + $edition + "," + $versionname + "`n";
                
            }
 
        }
        catch 
        { 
            $out = $server + ", Não foi possível abrir o registro";
        }
 
    }
    else 
    {
        $out = $server + ", Servidor offline"
    }
 
    $out >> "C:\temp\versao.csv";
    
}
View source code (using T-SQL, xp_cmdshell and PowerShell)
------------------------------------------------------
-- Gera o Script Powershell
------------------------------------------------------

DECLARE @Script VARCHAR(MAX) = '
# Recupera o nome de todos os servidores do arquivo "servidores.txt"
$servers = get-content c:\temp\servidores.txt;

"servidor,instância,edição,versão" > "C:\temp\versao.csv"
 
# Iteração entre os servidores
foreach ($server in $servers) 
{
 
    $out = $null;
 
    # Verifica se o computador está online
    if (test-connection -computername $server -count 1 -ea 0) 
    {
 
        try 
        {
            
            $type = [Microsoft.Win32.RegistryHive]::LocalMachine;
            $regconnection = [Microsoft.Win32.RegistryKey]::OpenRemoteBaseKey($type, $server) ;
            $instancekey = "SOFTWARE\Microsoft\Microsoft SQL Server\Instance Names\SQL";
 
            try 
            {
                $openinstancekey = $regconnection.opensubkey($instancekey);
            }
            catch { 
                $out = $server + ", Nenhuma chave de registro do SQL Server encontrada";
            }
 
            # Recupera o nome das instâncias
            $instances = $openinstancekey.getvaluenames();
            
            # Iteração entre as instâncias do servidor
            foreach ($instance in $instances) 
            {
 
                $instancename = $openinstancekey.getvalue($instance);
                $instancesetupkey = "SOFTWARE\Microsoft\Microsoft SQL Server\" + $instancename + "\Setup"; 
 
                $openinstancesetupkey = $regconnection.opensubkey($instancesetupkey);
                $edition = $openinstancesetupkey.getvalue("Edition")
                $version = $openinstancesetupkey.getvalue("Version");
 
                switch -wildcard ($version) 
                {
                    "15*" {$versionname = "SQL Server 2019";}
                    "14*" {$versionname = "SQL Server 2017";}
                    "13*" {$versionname = "SQL Server 2016";}
                    "12*" {$versionname = "SQL Server 2014";}
                    "11*" {$versionname = "SQL Server 2012";}
                    "10.5*" {$versionname = "SQL Server 2008 R2";}
                    "10.4*" {$versionname = "SQL Server 2008";}
                    "10.3*" {$versionname = "SQL Server 2008";}
                    "10.2*" {$versionname = "SQL Server 2008";}
                    "10.1*" {$versionname = "SQL Server 2008";}
                    "10.0*" {$versionname = "SQL Server 2008";}
                    "9*" {$versionname = "SQL Server 2005";}
                    "8*" {$versionname = "SQL Server 2000";}
                    default {$versionname = $version;}
                }
 
                # Armazena os resultados na variável que será grava no arquivo CSV
                $out += $server + "," + $instancename + "," + $edition + "," + $versionname + "`n";
                
            }
 
        }
        catch 
        { 
            $out = $server + ", Não foi possível abrir o registro";
        }
 
    }
    else 
    {
        $out = $server + ", Servidor offline"
    }
 
    $out >> "C:\temp\versao.csv";
    
}'



------------------------------------------------------
-- Cria um arquivo físico com o código do Script Posh
------------------------------------------------------


DECLARE @Fl_Ole_Automation_Ativado BIT = (SELECT (CASE WHEN CAST([value] AS VARCHAR(MAX)) = '1' THEN 1 ELSE 0 END) FROM sys.configurations WHERE [name] = 'Ole Automation Procedures')
 
IF (@Fl_Ole_Automation_Ativado = 0)
BEGIN
 
    EXECUTE SP_CONFIGURE 'show advanced options', 1;
    RECONFIGURE WITH OVERRIDE;
    
    EXEC sp_configure 'Ole Automation Procedures', 1;
    RECONFIGURE WITH OVERRIDE;
    
END


DECLARE
    @objFileSystem INT,
    @objTextStream INT,
    @objErrorObject INT,
    @strErrorMessage VARCHAR(1000),
    @hr INT,
    @Ds_Arquivo VARCHAR(500) = 'C:\Temp\Script.ps1'

 
SELECT @strErrorMessage = 'opening the File System Object'
    
EXECUTE @hr = sp_OACreate
    'Scripting.FileSystemObject',
    @objFileSystem OUT
 
    
IF @HR = 0
    SELECT
        @objErrorObject = @objFileSystem,
        @strErrorMessage = 'Creating file "' + @Ds_Arquivo + '"'
    
    
IF @HR = 0
    EXECUTE @hr = sp_OAMethod
        @objFileSystem,
        'CreateTextFile',
        @objTextStream OUT,
        @Ds_Arquivo,
        2,
        True
 
IF @HR = 0
    SELECT
        @objErrorObject = @objTextStream,
        @strErrorMessage = 'writing to the file "' + @Ds_Arquivo + '"'
    
    
IF @HR = 0
    EXECUTE @hr = sp_OAMethod
        @objTextStream,
        'Write',
        NULL,
        @Script
 
    
IF @HR = 0
    SELECT
        @objErrorObject = @objTextStream,
        @strErrorMessage = 'closing the file "' + @Ds_Arquivo + '"'
    
    
IF @HR = 0
    EXECUTE @hr = sp_OAMethod
        @objTextStream,
        'Close'
 
    
IF @hr <> 0
BEGIN
    
    DECLARE
        @Source VARCHAR(255),
        @Description VARCHAR(255),
        @Helpfile VARCHAR(255),
        @HelpID INT
    
    EXECUTE sp_OAGetErrorInfo
        @objErrorObject,
        @source OUTPUT,
        @Description OUTPUT,
        @Helpfile OUTPUT,
        @HelpID OUTPUT
        
        
    SELECT
        @strErrorMessage = 'Error whilst ' + COALESCE(@strErrorMessage, 'doing something') + ', ' + COALESCE(@Description, '')
        
        
    RAISERROR (@strErrorMessage,16,1)
        
END
    
    
EXECUTE sp_OADestroy @objTextStream
EXECUTE sp_OADestroy @objTextStream


--------------------------------------------------------------------------------
-- Desativando o OLE Automation (Se não estava habilitado antes)
--------------------------------------------------------------------------------
 
IF (@Fl_Ole_Automation_Ativado = 0)
BEGIN
 
    EXEC sp_configure 'Ole Automation Procedures', 0;
    RECONFIGURE WITH OVERRIDE;
 
    EXECUTE SP_CONFIGURE 'show advanced options', 0;
    RECONFIGURE WITH OVERRIDE;
 
END


------------------------------------------------------
-- Executa o Script Powershell
------------------------------------------------------

DECLARE @Fl_Xp_CmdShell_Ativado BIT = (SELECT (CASE WHEN CAST([value] AS VARCHAR(MAX)) = '1' THEN 1 ELSE 0 END) FROM sys.configurations WHERE [name] = 'xp_cmdshell')
 
IF (@Fl_Xp_CmdShell_Ativado = 0)
BEGIN
 
    EXECUTE SP_CONFIGURE 'show advanced options', 1;
    RECONFIGURE WITH OVERRIDE;
    
    EXEC sp_configure 'xp_cmdshell', 1;
    RECONFIGURE WITH OVERRIDE;
    
END


EXEC master.dbo.xp_cmdshell 'powershell.exe -ExecutionPolicy ByPass -File "C:\Temp\Script.ps1"'


IF (@Fl_Xp_CmdShell_Ativado = 0)
BEGIN
 
    EXEC sp_configure 'xp_cmdshell', 0;
    RECONFIGURE WITH OVERRIDE;
 
    EXECUTE SP_CONFIGURE 'show advanced options', 0;
    RECONFIGURE WITH OVERRIDE;
 
END

In this example, you will populate the input file (C:\Temp\servidores.txt) with the hostname of the servers you want to analyze SQL Server editions and versions.

After running the Powershell script, a CSV file (C:\Temp\versao.csv) will be generated with the collected information:

That's it, folks!
I hope you enjoyed this post and see you next time.