SQL Server Complete Server Inventory

One Query. Full Server Visibility.

🔎 Why This Script Is Important

In enterprise SQL Server environments, quick and accurate server visibility is critical.

During:

• Production incidents

• DR audits

• Migration planning

• Capacity reviews

• Performance investigations

We often need to manually collect information from multiple DMVs, system views, and configuration settings.

That process is time-consuming and error-prone.

So I built a single, consolidated server assessment script that delivers a complete infrastructure snapshot in one execution.

 What This Script Delivers

This automation generates a comprehensive SQL Server environment report, including:

🖥 Server & OS Information

• Server name and instance name

• SQL Server version and edition

• KB patch level

• Windows OS build detection

• Collation settings

• Clustered or standalone status

💾 Storage & Disk Capacity

Using secure DMV-based methods (no deprecated procedures), it:

• Enumerates fixed drives

• Calculates drive capacity (in GB)

• Generates a clean, readable disk summary

• Provides structured storage visibility

Perfect for capacity planning and audit reporting.

🧠 Memory & CPU Configuration

• Total physical RAM

• SQL Server max memory setting

• Total CPU count

• Server-level configuration validation

Ensures quick identification of misconfigured memory or underutilized hardware.

🗄 Database Inventory

• Total number of user databases

• Total database size (GB)

• Consolidated database name list

Useful for consolidation reviews and infrastructure sizing.

🔐 TDE (Transparent Data Encryption) Status

• Detects whether TDE certificates exist

• Quickly confirms encryption posture

Critical for compliance and security audits.

🔁 Always On Availability Group Details

If AG is enabled, the script captures:

• Availability Group name

• Replica server names

• Listener DNS name

• Primary replica detection

• AO status (AG / Standalone)

If AG is not configured, it clearly reports standalone status.

🛠 Technical Highlights

✔ Uses modern DMVs (no deprecated methods)
✔ Secure disk enumeration via sys.dm_os_enumerate_fixed_drives
✔ Aggregates results into clean single-row output
✔ Automatically detects clustering & HADR
✔ Production-safe read-only queries
✔ Designed for audit, DR, and infrastructure documentation

All critical server metadata — in one structured output.

🎯 Where This Script Helps Most

• Pre-migration assessments

• DR documentation

• Audit readiness

• Health check reports

• Environment baseline capture

• New server validation

• Capacity planning

Instead of running 15 different queries, you run one.

You can find script by below link:

SQL Server Complete Server Inventory