如何检查数据库是否存在于远程服务器上通过SQL查询 [英] How to check if database exist on Remote Server through SQL query
问题描述
我在SQL Server 2012中有一个表如下。
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.
但是在我的例子中,我想要它成为where子句的一部分。
推荐答案
所以,我认为你已经将所有的服务器链接,并使用了一个帐户,它可以读取架构。不是脚本将是这样:
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;
这篇关于如何检查数据库是否存在于远程服务器上通过SQL查询的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持IT屋!