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

  • 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

  • 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/

  • 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.

  • 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/

  • 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

  • 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)

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

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