How to check if database exist on Remote Server through SQL query

Question

I have a table as following in SQL Server 2012.

|---------------------|------------------|------------------|
|      ClientName     |     servername   |Databasename      |
|---------------------|------------------|------------------|
|          c1         |         s1       |         b1       |
|          c2         |         s2       |         b2       |
|          c3         |         s4       |         b4       |
|          c4         |         s5       |         b6       |
|---------------------|------------------|------------------|

Is there a way to return only rows for which database exists on the server names mentioned in the table?

I searched in forums for the answer but couldn't get one. I am aware that there exists a query as mentioned here which checks whether database on that server exists or not. But in my case, I want it to be part of where clause.

Also, please consider that, this table is in server 1 but the servers in table can be different from each other.

WHERE EXISTS(your remote query here). Keep in mind that since you are storing all the object names in a table you are going to have to use dynamic sql for this. — Sean Lange, Jun 22 '16 at 16:41
@SeanLange 'your remote query here' is the query, I am looking for. :) — atp9, Jun 22 '16 at 16:42
I think I am missing something. What are you trying to do here? — Sean Lange, Jun 22 '16 at 16:47
I am trying to return list or clients whose databases are reachable and not offline -through sql query. — atp9, Jun 22 '16 at 16:55
`atp_09` It is like you ask clerk in YOUR bank to include list of accounts from several other banks. At first, you have to connect to other servers, gather necessary info from them and only then you'll get a report. — Slava Murygin, Jun 22 '16 at 20:53
@SlavaMurygin, that's the point, I am trying to connect to these servers via query and for the possible ways I have asked the question. Considering your bank example, I believe not all banks work with the same protocols where as all SQL server does. I am asking clerk to add accounts from several other branches under same bank name (Linked Servers).I hope you understand :) — atp9, Jun 22 '16 at 20:56
This table is located on server 1. Do you also have linked servers defined there? From server 1 to server 2, 3, 4, and 5? — Gary, Jun 22 '16 at 21:23

score 2 · Accepted Answer · answered Jun 22 '16 at 21:16

So, I assume you already have all your server linked and you used an account, which can read the schema. Than script will be something like this:

SELECT TOP 0 * INTO #tbl_Server_DBs
FROM tbl_Server_DBs

DECLARE ServerDBs CURSOR LOCAL STATIC FORWARD_ONLY 
FOR SELECT ClientName, servername, Databasename FROM tbl_Server_DBs

DECLARE @ClientName NVARCHAR(128), @servername NVARCHAR(128), @Databasename NVARCHAR(128);
DECLARE @s NVARCHAR(4000)

OPEN ServerDBs
FETCH NEXT FROM ServerDBs 
INTO @ClientName, @servername, @Databasename

WHILE (@@fetch_status <> -1)
BEGIN
    SET @s = N'SELECT ''' + @ClientName + N''', ''' + @servername + N''', name 
        FROM [' + @servername + N'].sys.databases
        WHERE name = ''' + @Databasename + N''';';

    PRINT @s
    INSERT INTO #tbl_Server_DBs (ClientName, servername, Databasename)
    EXEC(@s);

    FETCH NEXT FROM ServerDBs 
    INTO @ClientName, @servername, @Databasename
END

CLOSE ServerDBs
DEALLOCATE ServerDBs

SELECT * FROM #tbl_Server_DBs;

Thank you. It seems I will have to write either SP or Function, I was looking for this solution to be part of where clause but, I think it is not possible. One way I think to add it as part of where clause is - defining the function and calling it in where clause but again, each data read will cause function call which may increase overhead. But, thanks again. — atp9, Jun 23 '16 at 12:56
Potentially, you can have it within one query or a view, but it implies that you have hard-coded server names. In case of any connection problems your query will fail. Also, you can add as many servers you want without changing the code. — Slava Murygin, Jun 23 '16 at 23:58

score 1 · Answer 2 · edited Jun 20 '20 at 09:12

Is there a way to return only rows for which database exists on the server names mentioned in the table?

I want it to be part of where clause.

If I understand your question correctly, you can use where exists

e.g. (you need to have server1 as linked server from the server that you are running the query from)

select * from 
schema.yourTable
where exists (select 1 from Server1.dbname.schemaName.yourServerList
                 where DatabaseName = 'b1')

How to check if database exist on Remote Server through SQL query

2 Answers2

Linked

Related