[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