Tuesday, July 16, 2013

Display SQL Server startup parameters using T-SQL


DECLARE @ROOTKEY VARCHAR(30), @INSTANCENAME VARCHAR(100), @KEY VARCHAR(200), @INSTANCEVERSION VARCHAR(50), @INSTANCEVALUE VARCHAR(200)

      DECLARE @StartupParams VARCHAR(100)
      DECLARE @ver varchar(140)
      SELECT @ver = CAST(serverproperty('ProductVersion') AS varchar)
      SET @ver = SUBSTRING(@ver, 1, CHARINDEX('.', @ver) - 1)
      PRINT @ver

      DECLARE @tbl_values TABLE(id INT IDENTITY(1,1), server_name VARCHAR(200) DEFAULT @@SERVERNAME, keyname VARCHAR(40), keyvalue VARCHAR(200))
      SELECT @InstanceVersion = @@serviceName
      EXEC master.dbo.xp_instance_regread 'HKEY_LOCAL_MACHINE',
      'SOFTWARE\MICROSOFT\\Microsoft SQL Server\\Instance Names\\SQL', @InstanceVersion, @InstanceValue OUTPUT

      SELECT @InstanceName = @InstanceValue
      SET @RootKey = 'HKEY_LOCAL_MACHINE'
      SET @Key ='SOFTWARE\Microsoft\\Microsoft SQL Server\' + @InstanceName + '\MSSQLServer\Parameters'
      INSERT INTO @tbl_values (keyname, keyvalue) EXEC master..xp_regenumvalues @RootKey, @Key
      SELECT @StartupParams = Keyvalue + ',' + ISNULL(@StartupParams,'')  FROM @tbl_values WHERE id>3
      SELECT CASE WHEN @StartupParams IS NULL THEN '' ELSE left(@StartupParams,LEN(@StartupParams)-1) END

No comments: