-2

Is there an ability to query below system specifications using t-sql ?

1. cpu (show number of processors / cores / frequency)
2. memory (show total amount of RAM on a machine; show allocated RAM; show really used RAM)
3. data disks (show total number of disks, their volume, free space available)
4. show Windows edition and version

Thanks!

  • A little search on google will give you everything. – Shanky Jul 21 '18 at 8:00
6

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:

sp_Blitz output

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!

0

I use xp_cmdshell to do this. You can invoke the command line and even PowerShell from SQL with a simple Exec xp_cmdshell 'command here' All you have to do is turn it on via advanced options.

Not the answer you're looking for? Browse other questions tagged or ask your own question.