Post reply

How to find out Installed SQL Server Instances on a server

  • September 13, 2011 at 11:53 pm

    #248071

    Hi All,

    How to find out Installed SQL Server Instances and their names on a server using T-SQL or any other way ?

    Thank You.

    Regards,
    Raghavender Chavva

  • September 14, 2011 at 2:52 am

    #1381111

    I think below will give the expected result:

    -- Create Temporary table to store the data

    Create Table #SQLInstances

    ( Value nvarchar(100),

    InstanceName nvarchar(100),

    Data nvarchar(100))

    -- Read Data from Registery

    Insert into #SQLInstances

    EXECUTE xp_regread

    @rootkey = 'HKEY_LOCAL_MACHINE',

    @key = 'SOFTWARE\Microsoft\Microsoft SQL Server',

    @value_name = 'InstalledInstances'

    Select InstanceName from #SQLInstances

    Thank You.

    Regards,
    Raghavender Chavva

  • September 14, 2011 at 2:52 am

    #1381112

    Raghavender (9/14/2011)

    I think below will give the expected result:

    -- Create Temporary table to store the data

    Create Table #SQLInstances

    ( Value nvarchar(100),

    InstanceName nvarchar(100),

    Data nvarchar(100))

    -- Read Data from Registery

    Insert into #SQLInstances

    EXECUTE xp_regread

    @rootkey = 'HKEY_LOCAL_MACHINE',

    @key = 'SOFTWARE\Microsoft\Microsoft SQL Server',

    @value_name = 'InstalledInstances'

    Select InstanceName from #SQLInstances

    Here can we find out which instance is running and which is not ?

    Thank You.

    Regards,
    Raghavender Chavva

  • September 14, 2011 at 7:06 pm

    #1381699

    Check if this helps..

    http://www.sqlservercentral.com/scripts/Administration/71614/

    Thank You,

    Best Regards,

    SQLBuddy

  • September 14, 2011 at 10:24 pm

    #1381717

    Raghavender (9/13/2011)

    Hi All,

    How to find out Installed SQL Server Instances and their names on a server using T-SQL or any other way ?

    The easiest way for me is, go to services and check the sql server services.

    If its named instance then your sql service will be named as mssql$instancename, if its default instance then simply mssqlserver.

    ----------
    Ashish

  • September 14, 2011 at 10:39 pm

    #1381719

    crazy4sql (9/14/2011)

    Raghavender (9/13/2011)

    Hi All,

    How to find out Installed SQL Server Instances and their names on a server using T-SQL or any other way ?

    The easiest way for me is, go to services and check the sql server services.

    If its named instance then your sql service will be named as mssql$instancename, if its default instance then simply mssqlserver.

    I think this becomes bit difficult if we have 100 instances or more 🙂 ..

    Thank You,

    Best Regards,

    SQLBuddy

  • September 14, 2011 at 10:44 pm

    #1381720

    I think this becomes bit difficult if we have 100 instances or more 🙂 ..

    Thank You,

    Best Regards,

    SQLBuddy

    100 instance in one server. :w00t: :w00t: tell me your server name and I am going to report it to MS :w00t::hehe::-D

    ----------
    Ashish

  • September 14, 2011 at 10:52 pm

    #1381722

    crazy4sql (9/14/2011)

    I think this becomes bit difficult if we have 100 instances or more 🙂 ..

    Thank You,

    Best Regards,

    SQLBuddy

    100 instance in one server. :w00t: :w00t: tell me your server name and I am going to report it to MS :w00t::hehe::-D

    No :w00t: .. Please don't report it to MS 😀 ..

    Thank You,

    Best Regards,

    SQLBuddy

  • September 18, 2011 at 3:13 am

    #1383052

    Hi,

    Get SQL server information in OS registry levels.

    Irrespective of the version of SQL Server, information is saved in the registry. Only the location changes for various versions. The path in the registry is as follows.

    SQL Server Default Instance(2000)

    HKEY_LOCAL_MACHINE\SOFTWARE\Microsoft\MSSQLServer\Setup

    SQL Server Default Instance(2005)

    HKEY_LOCAL_MACHINE\SOFTWARE\Microsoft\Microsoft SQL Server\MSSQL.1\Setup

    SQL Server Named Instance (2008)

    HKEY_LOCAL_MACHINE\SOFTWARE\Microsoft\Microsoft SQL Server\MSSQL10.ONE\Setup

    Best Regards,

    Rama Udaya.K

  • September 19, 2011 at 1:27 am

    #1383170

    If you can lookup how to capture info from a command prompt and put it into a table, you could use the command:

    Net start

    To list all running processes. Joined with the the info from the registry, you cab figure out the started and stopped versions of SQL.

    If you want to keep it all in the registry, they key will be something like

    Hklm\services\control\current version\mssql(instance)

    (^^^ the above key is not correct as I'm not near a windows PC to check. It is approximately right.)

    This will give you the actual status of each service, e.g. start, stopped, paused (probably numeric values for each). Overkill?

    If it were me, I'd use the net start command as I don't need to list all the registry keys and then search its values.

    Wayne

    Did you get access denied? Great the security works.

  • September 19, 2011 at 1:39 am

    #1383172

    OR you can :-

    1) open cmd

    2) sqlcmd -L >C:\servername.txt

    3) in your C drive servername.txt will be created with all the existing sql server(instance).

    You can modify the location C: to some other location.

    ----------
    Ashish

Viewing 11 posts - 1 through 10 (of 10 total)

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