Does anyone know of a way to get the machine's processor name from T-SQL?

Question

Does anyone know of a way to get the machine's processor name from T-SQL?

JunkIt

SSCommitted

Points: 1554
More actions
July 29, 2014 at 7:33 am

#397130

I'd like to retrieve the processor name (the same that name that shows up in the system properties control panel applet)
I was able to get this code to work but I'd rather not use xp_regread if there is a better way:
DECLARE @Key_Value nvarchar(4000), @rc int
EXEC @rc = master.dbo.xp_regread 'HKEY_LOCAL_MACHINE'
, 'HARDWARE\DESCRIPTION\System\CentralProcessor\0'
, 'ProcessorNameString'
, @Key_Value OUT
, N'no output'
Returns:
Intel(R) Xeon(R) CPU E7330 @ 2.40GHz

Viewing 6 posts - 1 through 5 (of 5 total)

You must be logged in to reply to this topic. Login to reply

Sean Lange SSC Guru Points: 286590 More actions · Answer 1

JunkIt (7/29/2014)
I'd like to retrieve the processor name (the same that name that shows up in the system properties control panel applet)
I was able to get this code to work but I'd rather not use xp_regread if there is a better way:
DECLARE @Key_Value nvarchar(4000), @rc int
EXEC @rc = master.dbo.xp_regread 'HKEY_LOCAL_MACHINE'
, 'HARDWARE\DESCRIPTION\System\CentralProcessor\0'
, 'ProcessorNameString'
, @Key_Value OUT
, N'no output'
Returns:
Intel(R) Xeon(R) CPU E7330 @ 2.40GHz

Why do you want to do this in sql? Why not just store the name in a table. It isn't like that value is going to change all the time. 😉

_______________________________________________________________

Need help? Help us help you.

Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.

Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.

Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/

JunkIt SSCommitted Points: 1554 More actions · Answer 2

I see I posted this under the wrong forum -- the solution only need apply to SQL Server 2008 R2 or higher.

As for the "why", this is a data collection/troubleshooting stored procedure that will run on our clients' servers. Since I don't have access to their hardware I want the lookup to be dynamic.

Sean Lange SSC Guru Points: 286590 More actions · Answer 3

JunkIt (7/29/2014)
I see I posted this under the wrong forum -- the solution only need apply to SQL Server 2008 R2 or higher.
As for the "why", this is a data collection/troubleshooting stored procedure that will run on our clients' servers. Since I don't have access to their hardware I want the lookup to be dynamic.

Gotcha. I don't know of any other way to do that. Reading that type of information is well outside of the "standard" querying implemented by sql server. If this is going to be run repeatedly it still might make sense to do this once and put the value in a table.

_______________________________________________________________

Need help? Help us help you.

Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.

Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.

Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/

Shawn Melton SSC-Insane Points: 24685 More actions · Answer 4

You can utilize two things: PowerShell and xp_cmdshell[/url] and this will open your world into a new wealth of information to pull or capture for a server. 😀

Now, xp_cmdshell is not bad and don't let anyone tell you it is. I used to think it was since supporting military as a contractor they frowned upon using it. However, only sysadmin folks can execute it and you can additionally add into your procedure to verify if it is on, if not enable it, and then when done turn it back off (I do this on client machines where I find it not in use).

Now for folks that come across this in the future, this solution only works if PowerShell exist on the SQL Server itself...

I use this bit of code to pull in OS information, disk information, and CPU information. I actually similar code to this running in production for a client to help me monitor disk space usage.DECLARE @XML_string varchar(max)

DECLARE @DriveSpace XML

DECLARE @XML TABLE (XMLitself varchar(2000), orderIt int IDENTITY(1,1) PRIMARY KEY);

INSERT INTO @XML(XMLitself)

EXEC xp_cmdshell 'powershell.exe -noprofile -command "Get-WmiObject -Class Win32_Processor -ErrorAction ''Continue'' -ErrorVariable eWmiData | Select-Object Name, NumberOfCores, NumberOfLogicalProcessors, AddressWidth, MaxClockSpeed | ConvertTo-XML -As string"'

SELECT @XML_string = COALESCE(@XML_string,'') + XMLitself

FROM @XML

WHERE XMLitself IS NOT NULL

SELECT @DriveSpace = @XML_string

SELECT

MAX(CASE WHEN attribute='Name' THEN VALUE ELSE '' END) AS ProcModel,

CAST(MAX(CASE WHEN attribute='NumberOfCores' THEN VALUE ELSE '' END) AS int) AS NumCores,

CAST(MAX(CASE WHEN attribute='NumberOfLogicalProcessors' THEN VALUE ELSE '' END) AS int) AS NumLogicalProcessors,

MAX(CASE WHEN attribute='AddressWidth' THEN VALUE ELSE '' END) AS Architecture,

CAST(MAX(CASE WHEN attribute='MaxClockSpeed' THEN VALUE ELSE '' END) AS int) AS MaxClockSpeed

FROM (

SELECT [property].value('(./text())[1]','varchar(80)') AS [value],

[property].value('@Name','varchar(20)') AS [attribute],

DENSE_RANK() OVER (ORDER BY [object]) AS unique_object

FROM @DriveSpace.nodes('Objects/Object') AS b([object])

CROSS APPLY b.object.nodes('./Property') AS c(property)

) psData

GROUP BY unique_object;

This will give you output like this:

Shawn Melton
Twitter: @wsmelton
Blog: wsmelton.github.com
Github: wsmelton

Jeff Moden SSC Guru Points: 1004378 More actions · Answer 5

Shawn Melton (7/29/2014)
Now, xp_cmdshell is not bad and don't let anyone tell you it is. I used to think it was since supporting military as a contractor they frowned upon using it. However, only sysadmin folks can execute it and you can additionally add into your procedure to verify if it is on, if not enable it, and then when done turn it back off (I do this on client machines where I find it not in use).

Well said. I wish that everyone understood that. BTW, turning it off does nothing for security except cause a hacker's attack software a 3ms burp to turn it on once they've broken in with "SA" privs. If they can't break in with "SA" privs, they can't use xp_CmdShell even if it's enabled.

And, I agree... PowerShell and xp_CmdShell sure do make life easy especially when it comes to the likes of WMI.

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)