-------------------------------------------------------------------------
-- 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
This is great. Can you also add a column about when latest or last SP\CU was installed on the server?