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!
Featured Online Courses:
- Essential SQL Server Administration Tips
- SQL Server Fundamentals (SQL Database for Beginners)
- Introduction to Data Science and SQL Server Machine Learning
- The Philosophy and Fundamentals of Computer Programming
- Introduction to Azure SQL Database
- SQL Server 2019: What’s New
- Entity Framework: Getting Started (Ultimate Beginners Guide)
- How to Import and Export Data in SQL Server
- Get Started with SQL Server in 30 Minutes
- A Guide on How to Start and Monetize a Successful Blog
Related SQL Server Administration Articles:
- Essential SQL Sever Administration Tips
- How to Patch a Standalone SQL Server Instance
- The SQL Server Browser Service and UDP Port 1434
- The Maximum Number of Concurrent Connections Setting in SQL Server
- Top 10 SQL Server DBA Daily Tasks List
- There is no SQL Server Failover Cluster Available to Join
- Encrypting a SQL Server Database Backup
- …more
Rate this article: 
(1 votes, average: 5.00 out of 5)
Loading...
Reference: SQLNetHub.com (https://www.sqlnethub.com)
© SQLNetHub
Artemakis Artemiou is a Senior SQL Server Architect, Author, and a 9 Times 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).
