Monday, December 20, 2010

VBScript: Retrieve all SQL Server instance names

I've seen vbscript code that is supposed to retrieve all the SQL Server instances from a fqdn, but when I implemented them, to my great disappointment, none worked. So, I wrote this function, which has worked well for me. It works for SQL Server 2000, 2005, and 2008.  I included a little bit of extra code so you can see how to use it.

option explicit
const HKEY_LOCAL_MACHINE = &H80000002

dim aryInstances, intInstance

aryInstances=sql_server_instances("computername.domain.fqdn.com")

if not isempty(aryInstances) then
    for intInstance=0 to ubound(aryInstances)
        wscript.echo aryInstances(intInstance)
    next
else
    wscript.echo "no SQL Server instances were found"
end if
wscript.quit

' - strServername: "computername.domain.fqdn.com"
function sql_server_instances(strServername)
    dim objReg, strKeypath, aryEntrynames, aryValuetypes, intCount, strInstance, strObject, strValue
    dim arySQLEntrynames, arySQLValuetypes, intEntryname, strSQLKeypath, arySQLKeys, intSQLKeys
    dim strSQLKey, arySQLInstanceKeys, strSQLInstanceKey, intSQLInstanceKey, strSQLInstanceKeyPath
   
    aryInstances=array
   
    strObject="winmgmts:{impersonationLevel=impersonate}!\\" & strServername & "\root\default:StdRegProv"
    if can_getobject(strObject) then
        set objReg=getobject(strObject)
        strKeyPath = "SOFTWARE\Microsoft\Microsoft SQL Server"
        objReg.enumkey HKEY_LOCAL_MACHINE, strKeyPath, arySQLKeys

        if not isnull(arySQLKeys) then
            for intSQLKeys=0 to ubound(arySQLKeys)
                strSQLKey=arySQLKeys(intSQLKeys)
                strSQLKeypath=strKeyPath & "\" & strSQLKey
                objReg.enumkey HKEY_LOCAL_MACHINE, strSQLKeypath, arySQLInstanceKeys
                if not isnull(arySQLInstanceKeys) then
                    for intSQLInstanceKey=0 to ubound(arySQLInstanceKeys)
                        strSQLInstanceKey=arySQLInstanceKeys(intSQLInstanceKey)
                        if lcase(strSQLInstanceKey)="mssqlserver" and ((left(lcase(strSQLKey), len("mssql"))="mssql" and right(lcase(strSQLKey), len(".mssqlserver"))=".mssqlserver") or instr(strSQLKey, ".")=0) then
                            redim preserve aryInstances(ubound(aryInstances)+1)
                            if right(lcase(strSQLKey), len(".mssqlserver"))=".mssqlserver" then
                                strSQLKey="(default)"
                            end if
                            aryInstances(ubound(aryInstances))=strSQLKey
                        end if
                    next
                end if
            next
        end if
    else
        aryInstances=empty
    end if
    sql_server_instances=aryInstances
end function

2 comments:

Vic said...

Thanks for the script.
Small update...

Code if can_getobject(strObject) then fails in may case.
can_getobject function "not found".

I had to replace it with:

set objReg=getobject(strObject)
if IsObject(objReg) then

William said...

Thank you, Viktors! If I remember correctly, can_getobject was a function I wrote, but regrettably didn't include here. I no longer use VBScript, so I doubt I will find it. I appreciate your input!