If you're running SMS 2003 SP2 (or earlier versions of SMS 2003) then inventorying SQL Server installations can be a little difficult. The information displayed in Add/Remove programs for SQL Server installations does not provide you with enough detail to determine the edition installed or even whether or not the computer only has the client tools installed-and definitely nothing about the licensing information associated with it.
Note: If you're running an SMS 2003 SP3 site (with Asset Intelligence installed) or a Configuration Manager 2007 site, then ignore this post, do not pass Go, and go directly to Asset Intelligence License Report 01A! Asset Intelligence reporting is designed just for situations like this. If you're running an SMS 2003 SP2 or below site, then after you see what you need to go through to get the same information that Asset Intelligence gets by default, it may just be enough to convince you to install SMS 2003 SP3 (with Asset Intelligence) or Configuration Manager!
I've been confounded by this for a while now, and have even managed to use SMS 2003 hardware inventory to determine which computers have the full database engine installation of SQL Server installed versus just the client tools, but determining the edition (Standard, Enterprise, Developer, etc...) has always eluded me. I just couldn't find this information in the registry or WMI of SQL Server computers. This doesn't mean it's not there necessarily, just that I couldn't find it.
Of course, the nice thing about working for Microsoft, is that help is only an e-mail away and I've done a lot of e-mailing the past few days 
Big thanks to Jim Bradbury (SMS SDK writer) who wrote the .sql and .vbs files!
Finding this information without Asset Intelligence is not easy, but it is possible. Here's a quick rundown of how to do it:
- Create a collection of computers that have SQL Server installed based on the Add/Remove programs information collected via standard SMS 2003 hardware inventory. Make sure your collection only contains computers running operating systems capable of installing the full database engine and not workstations that can only install the client components.
- Use software distribution or manually run a .sql script on all SQL Servers in the collection created in step 1.
- Modify the SMS 2003 SMS_def.mof file to inventory the information stored in the registry when the .sql from step 2 is used.
- Create a query or report to view the newly collected information.
Before I continue here, you should know that this procedure requires some serious disclaimers (notice how long this section is...considering SMS 2003 SP3 with Asset Intelligence yet?):
- To run the .sql script, the account used requires rights to access the registry and the master database.
- The .sql script used to determine the SQL Server installation information uses an undocumented extended stored procedure to write to the registry. This means that it is not officially supported and subject to not working at some point in the future-use at your own risk.
- I've only done very limited testing of this procedure in my lab environment. If you're going to use this method, be sure to test it in your lab environment, and then go test it again, before introducing these changes in your production environment.
- Using this method to write information to the registry is kind of like using a NOIDMIF file or any other scripted method that writes to the registry. You'll need to re-run this .sql script on the computers with SQL Server installed periodically to get the most current information. Also, if SQL Server is uninstalled, the registry entries created by running the .sql script will not be removed and you may end up with conflicting information based on the collections/reports created using this custom information versus the information returned via Add/Remove programs. To mitigate this, base the queries/reports for this information only on systems that show up in your SQL Server collection (based on Add/Remove programs information). You might also want to run another program first to delete the registry key information and only repopulate later using the .sql script if SQL Server is still installed. Of course, SQLCMD.EXE won't work if SQL Server is no longer installed, and this will help keep the registry free from unneeded entries. A quick .vbs to do this is provided here (rename the extension from .txt to .vbs before using it).
OK, on to the good stuff. This post is already becoming long so I'm not going to show the actual files here, I'll just describe them and give you links to view them. When viewing these as .txt files in a Web browser, the formatting leaves something to be desired. For best results, right-click the link and save them somewhere to view. All of the linked files have been renamed with a .txt extension so I could upload them and make them easier to read without them actually doing anything. To get the files with their 'real' extensions, use the link to save the entire .zip at the bottom of this post.
After you've created the collection of computers that report SQL Server installed via Add/Remove programs information obtained through hardware inventory, you need to somehow run the .sql script on them to populate the registry with the information we're after.
To run the .sql script on SQL Servers, you need to use SQLCMD.EXE. Since I've named the .sql script SQLInfo.sql, the command to use is: SQLCMD -i SQLInfo.sql. If you're running this locally via a command prompt, ensure that the account you're using can write to the registry and has rights to the master database. When run, the .sql script does a few nifty things:
- Creates some variables
- Runs SQL Server SELECT queries to get the information we're after into those variables
- Uses the undocumented extended stored procedure xp_regwrite* to write the information to the registry
- NULLs out the variables at the end to clean up
* This extended stored procedure is not officially documented and therefore not officially supported. Use at your own risk.
Once you've got the information in the registry, next up is an SMS_def.mof hardware inventory modification using the Registry Property Provider to read those keys and get them in the database. To see this information, you can create queries based on it or reports to view the information. When viewed in Resource Explorer, the information looks like so:
To determine the version of SQL Server running using the version value, check out this link. The license type value can be PER_SEAT, PER_PROCESSOR, or DISABLED. The number of licenses is the number of client licenses registered, processors licensed for that instance, or NULL if licensing is disabled.
Of course, this is a long way to go, and not a very fun way to get there, when trying to inventory SQL Server installations...and to be honest, if I was an SMS 2003 SP2 admin I'd be much more inclined to just install SMS 2003 SP3 with Asset Intelligence or even make the move to Configuration Manager 2007 to get this information the easy way.
Happy inventorying!
~Jeff
If you want to get all of the files I've referenced here, you can download them all in one .zip file here.