Query system specs (cpu, memory, data disks, free space) using T-SQL Is there an ability to query below system specifications using T-SQL?


cpu (show number of processors / cores / frequency)
memory (show total amount of RAM on a machine; show allocated RAM; show really used RAM)
data disks (show total number of disks, their volume, free space available)
show Windows edition and version
Yes, you bet. Let's start with the open source First Responder Kit (disclaimer: I'm one of the script authors) - install sp_Blitz, and then:

sp_Blitz @CheckServerInfo = 1

And at the bottom, you get a bunch of information about your system:



I've taken the liberty of rearranging the columns a little in that screenshot because I want to show you the finding, details, and the CheckID.

When you see a CheckID that has information you want to use, open up the sp_Blitz script and do a search for that CheckID. For example, if you want to know how we're determining that you have 4 logical processors and 31GB RAM, do a control-F for 84 (the CheckID), and you'll see the dynamic management views (DMVs) where we're gathering that data from.

Check 84 is a great example because it requires different logic based on what version of SQL Server you're trying to check, too.

Rather than giving you the specific answers for each of the 4 questions you asked, I'm just teaching you this process because I bet if you need those 4 things, you probably need a lot more, too - and you'll find a lot of 'em in sp_Blitz. Hope that helps!