I know you can do something like:
select count(*) as Qty from sys.databases where name like '%mydatabase%'
but how could you do something like:
select count(*) as Qty from linkedServer.sys.databases where name like '%mydatabases%'
I guess I could put a stored procedure on the linked server and execute the first select, but is there a way to query a linked server for what databases it holds?
Assuming your linked server login has read permissions on the master.sys.databases table, you can use the following:
select * from linkedserver.master.sys.databases
In the past, I've used this very query on SQL Server 2008 R2.
I think its just a matter of your syntax that is stopping you, try using single quotes instead of %% around your database name:
SELECT COUNT(*) as Qty FROM LinkedServer.master.sys.databases where name like 'mydatabase'
The correct formatting for selecting a Linked Server has already been answered here:
Listed below is a link to a cursor that works: http://jasonbrimhall.info/2012/03/05/are-my-linked-servers-being-used/
The query will need some rework to include all functions and triggers though.
I'm not sure if a remote master DB is always available through a linked server.
I'll be using the following TRY CATCH probe
BEGIN TRY
EXEC ('SELECT TOP 1 1 FROM MyLinkedServer.MyTestDb.INFORMATION_SCHEMA.TABLES')
END TRY
BEGIN CATCH
PRINT 'No MyTestDB on MyLinkedServer'
END CATCH
