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