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.

 

How to Import and Export Data in SQL Server - Online Course

Learn how to import and export data in SQL Server using all the available built-in tools!

Attend the online course!

 

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!
Avatar
About Artemakis Artemiou 33 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.