I came across an interesting undocumented stored proc today and thought I’d share what I found out.

Situation: I currently have a set of scripts to run when I jump on a server to bring me up to speed with what’s happening. They check all manner of things so that I don’t have to check everything manually. They’re extremely useful and save me a lot of time.

Problem: One thing customers do is run multiple instances of SQL Server on the same machine (not virtualised). This is bad because it’s extremely difficult to work out which server is causing certain statistics. It’s like a crowded lift, you have no idea who farted.

Solution: xp_regread can tell you via T-SQL how many instances of SQL Server you have running on the box.

EXECUTE xp_regread
@rootkey = 'HKEY_LOCAL_MACHINE',
@key = 'SOFTWARE\Microsoft\Microsoft SQL Server',
@value_name = 'InstalledInstances'

The output will look something like this;

Value	Value	Data
InstalledInstances – Item #1	MSSQLSERVER	NULL
InstalledInstances – Item #2	INSTANCE2	NULL

Issues: As a software vendor we always connect as an admin account so this always works. You do need a certain level of permissions for this to work, if you’re only in a basic group then you’re probably out of luck I’m afraid. Time to buy the DBA team a bottle of whisky and ask really nicely for your permissions to be elevated.