Well, after a 3 week vacation from blogging, I’m back. The
past month has been very busy for me, but its time I get back on track.
So I had somebody send me some code that they were having problems
with. They were trying to use T-SQL to read a value from the
local registry. When I saw that the code was about 70 lines long,
I didn’t even really examine it, as there is a much easier way to get
values from the registry.
In the master database, there is an extended stored procedure called
“xp_instance_regread” that allows you to read values from the
registry. The reason most people don’t know about it is because
you won’t find any information in BOL. If its undocumented, this
means that they are unsupported by Microsoft and you have to google to
figure out how to use them, or ask somebody who knows.
Here is a quick example on using xp_instance_regread to get the
installation path of Sql Server, and another one that tells you where
the default data directory is for Sql Server.
In addition to an extended stored procedure to read values from the
registry, there are other xp’s that allow you to manipulate the
registry as well.
- xp_instance_regaddmultistring
- xp_instance_regdeletekey
- xp_instance_regdeletevalue
- xp_instance_regenumkeys
- xp_instance_regenumvalues
- xp_instance_regremovemultistring
- xp_instance_regwrite
As always, use these extended stored procedures with EXTREME
CAUTION! Also, anybody can use xp_instance_regread, but you have
to be in the sysadmin role to change, write and delete registry values
and keys.