SQL Server Configuration Settings Query

 CREATE TABLE #CPUValues(

[index]        SMALLINT,
[description]  VARCHAR(128),
[server_cores] SMALLINT,
[value]        VARCHAR(5) 
)
 
CREATE TABLE #MemoryValues(
[index]         SMALLINT,
[description]   VARCHAR(128),
[server_memory] DECIMAL(10,2),
[value]         VARCHAR(64) 
)
 
INSERT INTO #CPUValues
EXEC xp_msver 'ProcessorCount'
 
INSERT INTO #MemoryValues 
EXEC xp_msver 'PhysicalMemory'
 
SELECT 
   SERVERPROPERTY('SERVERNAME') AS 'instance',
   v.sql_version,
   (SELECT SUBSTRING(CONVERT(VARCHAR(255),SERVERPROPERTY('EDITION')),0,CHARINDEX('Edition',CONVERT(VARCHAR(255),SERVERPROPERTY('EDITION')))) + 'Edition') AS sql_edition,
   SERVERPROPERTY('ProductLevel') AS 'service_pack_level',
   SERVERPROPERTY('ProductVersion') AS 'build_number',
   (SELECT DISTINCT local_tcp_port FROM sys.dm_exec_connections WHERE session_id = @@SPID) AS [port],
   (SELECT [value] FROM sys.configurations WHERE name like '%min server memory%') AS min_server_memory,
   (SELECT [value] FROM sys.configurations WHERE name like '%max server memory%') AS max_server_memory,
   (SELECT ROUND(CONVERT(DECIMAL(10,2),server_memory/1024.0),1) FROM #MemoryValues) AS server_memory,
   server_cores, 
   (SELECT COUNT(*) AS 'sql_cores' FROM sys.dm_os_schedulers WHERE status = 'VISIBLE ONLINE') AS sql_cores,
   (SELECT [value] FROM sys.configurations WHERE name like '%degree of parallelism%') AS max_dop,
   (SELECT [value] FROM sys.configurations WHERE name like '%cost threshold for parallelism%') AS cost_threshold_for_parallelism 
FROM #CPUValues
LEFT JOIN (
      SELECT
      CASE 
         WHEN CONVERT(VARCHAR(128), SERVERPROPERTY ('PRODUCTVERSION')) like '8%'    THEN 'SQL Server 2000'
         WHEN CONVERT(VARCHAR(128), SERVERPROPERTY ('PRODUCTVERSION')) like '9%'    THEN 'SQL Server 2005'
         WHEN CONVERT(VARCHAR(128), SERVERPROPERTY ('PRODUCTVERSION')) like '10.0%' THEN 'SQL Server 2008'
         WHEN CONVERT(VARCHAR(128), SERVERPROPERTY ('PRODUCTVERSION')) like '10.5%' THEN 'SQL Server 2008 R2'
         WHEN CONVERT(VARCHAR(128), SERVERPROPERTY ('PRODUCTVERSION')) like '11%'   THEN 'SQL Server 2012'
         WHEN CONVERT(VARCHAR(128), SERVERPROPERTY ('PRODUCTVERSION')) like '12%'   THEN 'SQL Server 2014'
         WHEN CONVERT(VARCHAR(128), SERVERPROPERTY ('PRODUCTVERSION')) like '13%'   THEN 'SQL Server 2016'     
         WHEN CONVERT(VARCHAR(128), SERVERPROPERTY ('PRODUCTVERSION')) like '14%'   THEN 'SQL Server 2017'
         WHEN CONVERT(VARCHAR(128), SERVERPROPERTY ('PRODUCTVERSION')) like '15%'   THEN 'SQL Server 2019' 
         ELSE 'UNKNOWN'
      END AS sql_version
     ) AS v ON 1 = 1
 
DROP TABLE #CPUValues
DROP TABLE #MemoryValues

Comments

Post a Comment

Popular posts from this blog

CREATE VIEW SQL: Modifying views in SQL Server

Image
Introduction The primary goal will be to get familiar with the  ALTER VIEW  command used to modify views and change the output. A view is based on the result set from a query, and this command allows us to change the structure and definition of a query. Ironically, before modifying a view, we will create another view with a bit more complex T-SQL using aggregates in it rather than having a simple SELECT statement that is pulling everything from a table. We will not go over the syntax again since T-SQL is exactly the same as in CREATE VIEW SQL statement except the fact that instead of the CREATE reserved keyword ALTER is used. Creating view As I mentioned earlier, let’s use the code from below to create a bit more complex view: 1 2 3 4 5 6 7 8 9 10 11 CREATE VIEW vTop3SalesByQuantity AS      SELECT TOP 3 --will only return first 3 records from query      Sales . ProductID ,      Name AS ProductName ,      SUM ( Sales . Quantity ) AS TotalQuantity      FROM Sales            J
Read more

Transfer logins and passwords between instances of SQL Server

  Transfer logins and passwords between instances of SQL Server This article describes how to transfer the logins and the passwords between different instances of SQL Server running on Windows. Original product version:    SQL Server Original KB number:    918992 Introduction This article describes how to transfer the logins and passwords between different instances of Microsoft SQL Server.  Note The instances may be on the same server or on different servers, and their versions may differ. More information In this article, server A and server B are different servers. After you move a database from the instance of SQL Server on server A to the instance of SQL Server on server B, users may be unable to log in to the database on server B. Additionally, users may receive the following error message: Login failed for user ' MyUser '. (Microsoft SQL Server, Error: 18456) This problem occurs because you did not transfer the logins and the passwords from the instance of SQL Server on
Read more

SQL SERVER –Different Ways to Find Recovery Model for Database

Image
  This is a very easy method and it gives all the database information in one script. SELECT  name  AS  [Database Name] , recovery_model_desc  AS  [Recovery Model]  FROM  sys.databases GO Method 4 This method provides only one database at a time. SELECT  'ADVENTUREWORKS'  AS  [Database Name] , DATABASEPROPERTYEX ( 'ADVENTUREWORKS' ,  'RECOVERY' ) AS  [Recovery Model] GO My recommendation
Read more