[SQL Server] Trouver le port d'un serveur SQL Server

-- Show port number for SQL 2000/2005/2008/2008R2 Version

SET NOCOUNT ON

go

DECLARE @SqlPort NVARCHAR(10)

DECLARE @instance_name NVARCHAR(30)

DECLARE @reg_key NVARCHAR(500)

DECLARE @value_name NVARCHAR(20)

-- Scan for SQL 2008R2

IF LEFT(CAST(SERVERPROPERTY('ProductVersion')AS sysname),5) = '10.50'

BEGIN

      SELECT @instance_name = CAST(SERVERPROPERTY('instancename')AS sysname)

      IF @instance_name is NULL

            BEGIN

                  SET @reg_key = 'SOFTWARE\Microsoft\Microsoft SQL Server\MSSQL10_50.MSSQLSERVER\MSSQLServer\SuperSocketNetLib\AdminConnection\Tcp'

                  --END

                  --ELSE BEGIN

                  --SET @reg_key = 'SOFTWARE\Microsoft\Microsoft SQL Server\' + @instance_name + '\MSSQLServer\SuperSocketNetLib\Tcp'

            END

            EXEC master..xp_regread@rootkey='HKEY_LOCAL_MACHINE',

            @key=@reg_key, @value_name='TcpDynamicPorts',

            @value=@SqlPort output

            SELECT CAST(SERVERPROPERTY('ServerName')AS sysname) AS ServerName, @SqlPort AS Port

END

-- Scan for SQL 2008

IF LEFT(CAST(SERVERPROPERTY('ProductVersion')AS sysname),5) = '10.0.'

BEGIN

      SELECT @instance_name = CAST(SERVERPROPERTY('instancename')AS sysname)

      IF @instance_name is NULL

            BEGIN

                  SET @reg_key = 'SOFTWARE\Microsoft\Microsoft SQL Server\MSSQL10.MSSQLSERVER\MSSQLServer\SuperSocketNetLib\AdminConnection\Tcp'

                  --END

                  --ELSE BEGIN

                  --SET @reg_key = 'SOFTWARE\Microsoft\Microsoft SQL Server\' + @instance_name + '\MSSQLServer\SuperSocketNetLib\Tcp'

            END

                  EXEC master..xp_regread@rootkey='HKEY_LOCAL_MACHINE',

                  @key=@reg_key, @value_name='TcpDynamicPorts',

                  @value=@SqlPort OUTPUT

                  SELECT CAST(SERVERPROPERTY('ServerName')AS sysname) AS ServerName, @SqlPort AS Port

END

-- Scan for SQL 2005

IF LEFT(CAST(SERVERPROPERTY('ProductVersion')AS sysname),1) = '9'

BEGIN

      SELECT @instance_name = CAST(SERVERPROPERTY('instancename')AS sysname)

      IF @instance_name is NULL

            BEGIN

                  SET @reg_key = 'SOFTWARE\Microsoft\MSSQLServer\MSSQlServer\SuperSocketNetLib\Tcp'

            END

            ELSE

            BEGIN

                  SET @reg_key = 'SOFTWARE\Microsoft\Microsoft SQL Server\' + @instance_name + '\MSSQLServer\SuperSocketNetLib\Tcp'

            END

            EXEC master..xp_regread@rootkey='HKEY_LOCAL_MACHINE',

            @key=@reg_key, @value_name='TcpPort',

            @value=@SqlPort output

            SELECT CAST(SERVERPROPERTY('ServerName')AS sysname) AS ServerName, @SqlPort AS Port

END

-- Scan for SQL 2000

IF LEFT(CAST(SERVERPROPERTY('ProductVersion')AS sysname),1) = '8'

      BEGIN

            CREATE TABLE #Port_2000(value NVARCHAR(20),Data NVARCHAR(10))

            INSERT INTO #Port_2000 exec master..xp_instance_regread'HKEY_LOCAL_MACHINE', 'SOFTWARE\Microsoft\MSSQLServer\MSSQLServer\Supersocketnetlib\tcp', 'tcpPort'

            SELECT @SqlPort = Data from #Port_2000

            SELECT CAST(SERVERPROPERTY('ServerName')AS sysname) AS ServerName, @SqlPort AS Port

            DROP TABLE #Port_2000

      END