SQL Server Central Management Server Query Patterns

If you deal with a lot of SQL Servers you know that Central Management Server (CMS) is your friend.  If you use CMS correctly you can run queries against all of your instances with very little additional work.  But there is *some* additional work.  For instance, what if you need to run queries against databases with different names or naming conventions on each instance?  Or only run the query on a given database if it contains a given application table?  Or only run a query for user databases?  Here are the query patterns I use to overcome some of these issues.  I hope you find them useful.  

Show me all register servers in my CMS

You should connect directly to your CMS and switch your db context to msdb to run this query.  

declare @name varchar(256), @server_name varchar(256)
declare dave cursor for 
	select name, server_name 
	from dbo.sysmanagement_shared_registered_servers_internal
	--from dbo.sysmanagement_shared_server_groups_internal
	where server_group_id = 6
open dave
fetch next from dave into @name, @server_name
while (@@FETCH_STATUS = 0)
begin
	print 'echo Running: ' + @name 
	print 'PAUSE'
	print 'powershell .\something -ServerName "' + @server_name + '"'
fetch next from dave into @name, @server_name
end
close dave
deallocate dave
 

The remaining queries can be run by connecting to your CMS server in "Registered Servers" in Management Studio, and then selecting "Connect to All"

Run a command on a subset of databases based on a property and name

declare @something varchar(4000)
declare dave cursor for
	select name
	from sys.databases WHERE name like '%tnd%' and is_broker_enabled = 1
open dave
fetch next from dave into @something
while @@FETCH_STATUS = 0 
begin
	select @something = 'EXEC ' + @something + '.dbo.Blah'
	exec (@something)
	fetch next from dave into @something
end
close dave
deallocate dave

Run a query against all databases named something

declare @something varchar(4000)
declare dave cursor for
	select 'select COUNT(*) from ' + name + '.dbo.Something WHERE Blah IS NULL AND StpDTime IS NULL '
	from sys.databases WHERE name like '%blah%'
open dave
fetch next from dave into @something
while @@FETCH_STATUS = 0 
begin
 
	exec (@something)
	fetch next from dave into @something
end
close dave
deallocate dave