SQL Script to find Installed Instances on a server

Leave a comment (1) Go to comments

How many SQL Server Instances are installed on a server?

Recently, I was working on a project, where I need to quote to manage a SQL Server on 50 servers (physical boxes). Considering a product based company, I assumed that one instances on every box but I was shocked there were 2-3 instances on every server and one server has 6 instances.

Looking at this, I need to developed a quick script to find "Installed Instances on every Machine"

SQL Script to find all SQL instances installed on a server

-- ******************************************** */
-- Description                                  */
-- ============                                 */
-- Script to find SQL instances installed on a server
-- =============================================*/
-- Compatibility  :     2000+                   */
-- ******************************************** */

-- 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

-- Clear the temp table
drop table #SQLInstances


SQL Script to find Installed Instances on a server Undocumented sql server list of installed instances SQL Server Instance Information SQL Server 2011 SQL Server 2008 SQL Server 2005 SQL Queries Installed Instances

EOF - SQL Script to find Installed Instances on a server, SQL Server 2012 , 2008 R2 , 2008 , 2005 , 2000
Leave a comment


  1. How can I use Excel vba to retrieve on instances from particular server?

Leave a Reply

Your email address will not be published. Required fields are marked *


You may use these HTML tags and attributes: <a href="" title=""> <abbr title=""> <acronym title=""> <b> <blockquote cite=""> <cite> <code> <del datetime=""> <em> <i> <q cite=""> <strike> <strong>

Notify me of followup comments via e-mail. You can also subscribe without commenting.