SQLServer Installed Instances

SQL Server Installed Instances

                                          There is many ways to find SQL Server Installed Instances given physical server.Microsoft providers few methoed,

• Registry reader
• SQL Server folder information reader
• Windows Service information reader

Registry reader

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

EXECUTE master..xp_regenumvalues
  @rootkey = 'HKEY_LOCAL_MACHINE',

  @key = 'SOFTWARE\Microsoft\Microsoft SQL Server\Instance Names\SQL';
GO


Windows Service information reader

DECLARE @T TABLE (data nvarchar(4000));

INSERT INTO @T

EXEC master..xp_cmdshell 'sc query';

SELECT CASE WHEN data='SERVICE_NAME: MSSQLSERVER' THEN HOST_NAME() ELSE REPLACE(data,'SERVICE_NAME: MSSQL$',HOST_NAME()+'\') END  SQLServerInstalledInstances 

FROM @T WHERE data LIKE '%SERVICE_NAME: MSSQL%';

If you need to find server physical file location then following SQL Statements will helps, 

--and if you need to view file location give sql server Instances   

SELECT InstanceDefaultDataPath = serverproperty('InstanceDefaultDataPath')-- only 2012 OR later

SELECT TOP 1 REPLACE(REPLACE([filename],'master.mdf',''),'mastlog.ldf','') file_location FROM master.sys.sysfiles;

Most of SQL Server instances not enabled xp_cmdshell system procedures. if not you need to enabled it when needed.

EXEC sp_configure 'show advanced options', 1;

GO

RECONFIGURE;

GO

-- To enable the feature.

EXEC sp_configure 'xp_cmdshell', 1;

GO

RECONFIGURE;

GO

/*

Add your T-SQL Code 

*/

-- To enable the feature.

EXEC sp_configure 'xp_cmdshell', 0;

GO

RECONFIGURE;

GO

Cheers...

and Most welcome your comments and ideas...