How To Get Basic SQL Server Instance Info

image

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 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.

 

Learn more about interesting technology topics in our eBook: “Tech How To’s Vol. 1

Tech How To's Vol. 1 - Technology eBook by Artemakis Artemiou

 

Useful note: If you work with many SQL Server instances and performing multi-administration methods, SQLNetHub’s DBA Security Advisor can definitely help you. Give it a try here!

 

Reference: TechHowTos.com (http://www.techhowtos.com)

© TechHowTos.com

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

Loading...

Share this!
About Artemakis Artemiou 31 Articles
Artemakis Artemiou is a Senior SQL Server and Software Architect, Author, and a former Microsoft Data Platform MVP (2009-2018). He has over 15 years of experience in the IT industry in various roles. Artemakis is the founder of SQLNetHub and TechHowTos.com. Artemakis is the creator of the well-known software tools Snippets Generator and DBA Security Advisor. Also, he is the author of many eBooks on SQL Server. Artemakis currently serves as the President of the Cyprus .NET User Group (CDNUG) and the International .NET Association Country Leader for Cyprus (INETA). Artemakis's official website can be found at aartemiou.com.