How to determine if database exists on linked server?

Question

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?

Answer 1

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.

Answer 2

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:

SQL Server Linked Server Example Query

Answer 3

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.

Answer 4

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