Thursday, March 24, 2016

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