CODEWITHSUNDEEP
sql-server-2005sql-server-2008
Zachary Scott
Zachary Scott

Reputation: 21162

How to determine if database exists on linked server?

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?

Upvotes: 2

Views: 8345

Answers (4)

it3xl
it3xl

Reputation: 2672

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

Upvotes: 0

Kevin Suchy
Kevin Suchy

Reputation: 57

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

Upvotes: 1

Tequila
Tequila

Reputation: 864

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.

Upvotes: 0

Jonathan McIntire
Jonathan McIntire

Reputation: 2675

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.

Upvotes: 4

Related Questions