How To Get Basic SQL Server Instance Info (T-SQL Script)

With the below T-SQL script, you can get basic SQL Server instance info such as:

  • Full instance name
  • SQL Server version
  • Edition
  • Collation
  • Number of databases
  • Product level (i.e. SP-level)
  • …and more

 

The T-SQL Script

The script uses the built-in SQL Server function SERVERPROPERTY.

SELECT
SERVERPROPERTY('ServerName') AS FullInstanceName,
REPLACE(SUBSTRING(@@version,0,CHARINDEX('-',@@version)),'Microsoft ','') as FullSQLVersion,
SERVERPROPERTY('ProductVersion') AS ProductVersion,
SERVERPROPERTY('ProductLevel') AS ProductLevel,
SERVERPROPERTY('ComputerNamePhysicalNetBIOS') AS ComputerNamePhysicalNetBIOS,
SERVERPROPERTY('MachineName') AS MachineName,
SERVERPROPERTY('InstanceName') as InstanceName,
SERVERPROPERTY('BuildClrVersion') AS BuildClrVersion,
SERVERPROPERTY('Collation') AS Collation,
SERVERPROPERTY ('edition') as InstanceEdition,
CASE WHEN SERVERPROPERTY('EngineEdition')=1 THEN 'Personal/Desktop' 
   WHEN SERVERPROPERTY('EngineEdition')=2 THEN 'Standard' 
   WHEN SERVERPROPERTY('EngineEdition')=3 THEN 'Enterprise' 
   WHEN SERVERPROPERTY('EngineEdition')=4 THEN 'Express' 
   WHEN SERVERPROPERTY('EngineEdition')=5 THEN 'SQL Database' 
   WHEN SERVERPROPERTY('EngineEdition')=6 THEN 'SQL Data Warehouse' 
END AS EngineEdition,
CASE WHEN SERVERPROPERTY('IsClustered')=1 THEN 'Clustered'
 	 WHEN SERVERPROPERTY('IsClustered')=0 THEN 'Not Clustered'
   ELSE 'N/A' END AS ClusteredStatus,
(SELECT COUNT(*) FROM sys.databases) AS TotalDatabases

For more information about the built-in SQL Server function SERVERPROPERTY please visit this MSDN article.

 

Check our Master Class on SQL Server Administration

If you really want to learn sophisticated SQL Server administration techniques, then you should check our on-demand online course titled “Essential SQL Server Administration Tips” (special limited-time discount included in link).

Via the course, you will learn essential hands-on SQL Server Administration tips on SQL Server maintenance, security, performance, integration, error handling and more. Many live demonstrations and downloadable resources included!

Essential SQL Server Administration Tips - Online Course with Live Demonstrations and Hands-on Guides
SQL Server Administration Tips (lifetime access – learn more).

Enroll Now with Discount!

 

Featured Online Courses:

 

Related SQL Server Administration Articles:

 

Rate this article: 1 Star2 Stars3 Stars4 Stars5 Stars (1 votes, average: 5.00 out of 5)

Loading...

Reference: SQLNetHub.com (https://www.sqlnethub.com)

© SQLNetHub