Verificare setari server SQL

Configurare noua (How To)

Situatie

Pentru cazurile in care avem nevoie sa vedem ce fel de setari sunt pe un server de sql am facut o procedura care, rulata pe server, ne arata urmatoarele lucruri:

– daca instalarea e Case Sensitive sau nu
– calea unde sunt fisierele mdf si ldf aferente bazei Master
– calea unde este fisierul care inregistreaza erorile
– calea unde e instalat serverul de sql
– versiunea serverului de sql
– numele serverului
– editia
– ultimul service pack instalat
– daca e clustered sau nu
– versiunea motorului de sql
– collatiunea instalata

 

Solutie

SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
create procedure [dbo].[BD_Setari_Server]

as

declare @RegPParams sysname
declare @Arg sysname
declare @Param sysname
declare @MasterPath nvarchar(512)
declare @LogPath nvarchar(512)
declare @ErrorLogPath nvarchar(512)
declare @n int

select @n=0
select @RegPParams=N’SoftwareMicrosoftMSSQLServerMSSQLServer’+’Parameters’
select @Param=’dummy’
while(not @Param is null)
begin
select @Param=null
select @Arg=’SqlArg’+convert(nvarchar,@n)

exec master.dbo.xp_instance_regread N’HKEY_LOCAL_MACHINE’, @RegPParams, @Arg, @Param OUTPUT
if(@Param like ‘-d%’)
begin
select @Param=substring(@Param, 3, 255)
select @MasterPath=substring(@Param, 1, len(@Param) – charindex(”, reverse(@Param)))
end
else if(@Param like ‘-l%’)
begin
select @Param=substring(@Param, 3, 255)
select @LogPath=substring(@Param, 1, len(@Param) – charindex(”, reverse(@Param)))
end
else if(@Param like ‘-e%’)
begin
select @Param=substring(@Param, 3, 255)
select @ErrorLogPath=substring(@Param, 1, len(@Param) – charindex(”, reverse(@Param)))
end

select @n=@n+1
end

declare @SmoRoot nvarchar(512)
exec master.dbo.xp_instance_regread N’HKEY_LOCAL_MACHINE’, N’SOFTWAREMicrosoftMSSQLServerSetup’, N’SQLPath’, @SmoRoot OUTPUT

SELECT
CAST(case when ‘a’ <> ‘A’ then 1 else 0 end AS bit) AS [IsCaseSensitive],
CAST(FULLTEXTSERVICEPROPERTY(‘IsFullTextInstalled’) AS bit) AS [IsFullTextInstalled],
@LogPath AS [MasterDBLogPath],
@MasterPath AS [MasterDBPath],
@ErrorLogPath AS [ErrorLogPath],
@SmoRoot AS [RootDirectory],
SERVERPROPERTY(N’ProductVersion’) AS [VersionString],
CAST(SERVERPROPERTY(N’MachineName’) AS sysname) AS [NetName],
CAST(SERVERPROPERTY(N’Edition’) AS sysname) AS [Edition],
CAST(SERVERPROPERTY(N’ProductLevel’) AS sysname) AS [ProductLevel],
CAST(SERVERPROPERTY(‘IsClustered’) AS bit) AS [IsClustered],
CAST(SERVERPROPERTY(‘EngineEdition’) AS int) AS [EngineEdition],
convert(sysname, serverproperty(N’collation’)) AS [Collation]

Tip solutie

Permanent

Voteaza

(6 din 22 persoane apreciaza acest articol)

Despre Autor

Leave A Comment?