Loop through each instance on a server

Question

Post reply

Loop through each instance on a server

SQLHeap

Hall of Fame

Points: 3572
More actions
April 15, 2015 at 9:54 am

#398332

I'm writing a query to loop through each instance on a server so I can get each instances relevant information. I can't seem to get each instances sql service and sql agent service, either one or the other. Any ideas?
DECLARE @InstanceNames varchar(50), @sql varchar(2000)
DECLARE @GetInstances TABLE (Value varchar(100), InstanceNames varchar(50), Data varchar(100), ID int IDENTITY(1,1))
DECLARE @Serverinfo TABLE (ServerName varchar(50), InstanceName varchar(50), ProductVersion varchar(20), ProductLevel char(3)
, ProductEdition varchar(50), IsClustered varchar(3), IsAG varchar(3), OperatingSystem varchar(50), Ram varchar(20)
, SQLServiceAcct varchar(50), SQLAgentAcct varchar(50))
INSERT INTO @GetInstances
EXECUTE xp_regread
@rootkey = 'HKEY_LOCAL_MACHINE',
@key = 'SOFTWARE\Microsoft\Microsoft SQL Server',
@value_name = 'InstalledInstances'
SELECT InstanceNames from @GetInstances
DECLARE curGetInfo CURSOR for
SELECT InstanceNames
FROM @GetInstances
OPEN curGetInfo
FETCH NEXT FROM curGetInfo INTO @InstanceNames
WHILE @@FETCH_STATUS = 0
BEGIN
SET @sql = '
SELECT CASE WHEN SERVERPROPERTY(''IsClustered'') = 1
THEN (SELECT cluster_nodename FROM sys.dm_server_services WHERE servicename = ''SQL Server (' + @InstanceNames + ')'')
ELSE CAST(SERVERPROPERTY(''MachineName'') as varchar(50))
END ServerName
, ''' + @InstanceNames + ''' InstanceName
, CAST(SERVERPROPERTY(''ProductVersion'') as varchar(20)) ProductVersion
, CAST(SERVERPROPERTY(''ProductLevel'') as char(3)) ProductLevel
, CAST(SERVERPROPERTY(''Edition'') as varchar(50)) ProductEdition
, CASE WHEN SERVERPROPERTY(''IsClustered'') = 1
THEN ''Yes''
ELSE ''No''
END IsClustered
, CASE WHEN SERVERPROPERTY(''IsHADREnabled'') = 1
THEN ''Yes''
ELSE ''No''
END IsAG
, RIGHT(@@VERSION, LEN(@@VERSION) -3 -CHARINDEX('' ON '', @@VERSION)) OperatingSystem
, ''16.0''RAM
, (SELECT service_account FROM sys.dm_server_services WHERE servicename = ''SQL Server (' + @InstanceNames + ')'') SQLServiceAcct
, (SELECT service_account FROM sys.dm_server_services WHERE servicename = ''SQL Server Agent (' + @InstanceNames + ')'') SQLAgentAcct
FROM sys.dm_os_sys_info'
select @sql
INSERT INTO @Serverinfo
EXEC (@SQL)
FETCH NEXT FROM curGetInfo INTO @InstanceNames
END
CLOSE curGetInfo
DEALLOCATE curGetInfo
select * from @Serverinfo
There is an exception to every rule, except this one...

Viewing 8 posts - 1 through 7 (of 7 total)

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

Eirikur Eiriksson SSC Guru Points: 182915 More actions · Answer 1

Quick thought, how about cmd net start and parse the output?

😎

SQLHeap Hall of Fame Points: 3572 More actions · Answer 2

I don't want to start anything, just trying to get info. I'd like to stay with a SQL query.

There is an exception to every rule, except this one...

Eirikur Eiriksson SSC Guru Points: 182915 More actions · Answer 3

SQLHeap (4/15/2015)
I don't want to start anything, just trying to get info. I'd like to stay with a SQL query.

without any parameters it only lists the services, should be easy to parse the output.

😎

SQLHeap Hall of Fame Points: 3572 More actions · Answer 4

That lists out the services yes, but I'm looking for the account that the SQL services are running under.

There is an exception to every rule, except this one...

sql-lover SSCoach Points: 18530 More actions · Answer 5

I use powershell for this type of cross server activities. Here's an example: USING POWERSHELL TO LOOP THROUGH A LIST OF SQL SERVER DATABASES[/url]

Eirikur Eiriksson SSC Guru Points: 182915 More actions · Answer 6

Just ran your code on a multi instance box (10 running and 4 not running), all showing up in the results, could this be a permission issue?

😎

BTW: the services not running show up with NULLs in SQLServiceAcct and SQLAgentAcct.

SQLHeap Hall of Fame Points: 3572 More actions · Answer 7

GRRRR, doesn't work for me at home either:crazy:

There is an exception to every rule, except this one...