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
– Sean Lange, Commented Jun 22, 2016 at 16:41
@SeanLange 'your remote query here' is the query, I am looking for. :) — atp9
– atp9, Commented Jun 22, 2016 at 16:42
I think I am missing something. What are you trying to do here? — Sean Lange
– Sean Lange, Commented Jun 22, 2016 at 16:47
I am trying to return list or clients whose databases are reachable and not offline -through sql query. — atp9
– atp9, Commented Jun 22, 2016 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
– Slava Murygin, Commented Jun 22, 2016 at 20:53

Slava Murygin · Accepted Answer · 2016-06-22 21:16:05Z

2

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;

answered Jun 22, 2016 at 21:16

Slava Murygin

1,9551 gold badge11 silver badges10 bronze badges

Sign up to request clarification or add additional context in comments.

2 Comments

atp9 Over a year ago

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.

Slava Murygin Over a year ago

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.

Community · Accepted Answer · 2020-06-20 09:12:55Z

1

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')

edited Jun 20, 2020 at 9:12

CommunityBot

11 silver badge

answered Jun 22, 2016 at 21:21

Kin Shah

6126 silver badges25 bronze badges

Collectives™ on Stack Overflow

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

2 Answers 2

2 Comments

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

2 Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related