执行& quot; sp_msforeachdb& quot;在Java应用程序中 [英] Execute "sp_msforeachdb" in a Java application

问题描述

Hi StackOverflow社区:)

我来找你分享我的一个问题...

我必须提取一个SQL Server实例的每个数据库中每个表的列表，我发现了这个查询:

  EXEC sp_msforeachdb'使用?;SELECT DB_NAME()AS DB，* FROM sys.tables' 

它可以在Microsoft SQL Server Management Studio上完美运行，但是当我尝试在Java程序中执行 (包括用于SQL Server的JDBC驱动程序)时，它说它不返回任何结果.

我的Java代码如下:

  this.statement = this.connect.createStatement();//创建语句this.resultats = this.statement.executeQuery("EXEC sp_msforeachdb'使用?; SELECT DB_NAME()AS DB，* FROM sys.tables'");//执行查询并将结果存储在ResultSet中this.sortie.ecrireResultats(this.statement.getResultSet());//将ResultSet写入文件 

感谢任何会尝试帮助我的人， 今天愉快:)

我不确定SQL Server的JDBC驱动程序是否支持我的查询，因此我将尝试以另一种方式实现目标.

我要获取的是 SQL Server实例上每个数据库的所有表的列表，输出格式如下:

  + ----------- + -------- +|数据库|桌子|+ ----------- + -------- + 

所以现在我要问有人可以帮助我通过Java的JDBC for SQL Server驱动程序使用SQL查询来获得该解决方案.

我也想感谢我从 Tim Lehner 和解决方案

如果语句不能返回结果或返回多个结果，则不应使用 executeQuery ，而应使用 execute() 代替，此方法返回一个 boolean ，指示第一个结果的类型:

• true :结果是 ResultSet
• false :结果是更新计数

如果结果为 true ，则您使用 Java SQL:Statement.hasResultSet()? 的答案.

Hi StackOverflow community :)

I come to you to share one of my problems...

I have to extract a list of every table in each database of a SQL Server instance, I found this query :

EXEC sp_msforeachdb 'Use ?; SELECT DB_NAME() AS DB, * FROM sys.tables'

It works perfectly on Microsoft SQL Server Management Studio but when I try to execute it in my Java program (that includes JDBC drivers for SQL Server) it says that it doesn't return any result.

My Java code is the following :

this.statement = this.connect.createStatement(); // Create the statement
this.resultats = this.statement.executeQuery("EXEC sp_msforeachdb 'Use ?; SELECT DB_NAME() AS DB, * FROM sys.tables'"); // Execute the query and store results in a ResultSet

this.sortie.ecrireResultats(this.statement.getResultSet()); // Write the ResultSet to a file

Thanks to anybody who will try to help me, Have a nice day :)

EDIT 1 :

I'm not sure that the JDBC driver for SQL Server supports my query so I'll try to get to my goal in another way.

What I'm trying to get is a list of all the tables for each database on a SQL Server instance, the output format will be the following :

+-----------+--------+
| Databases | Tables |
+-----------+--------+

So now I'm asking can someone help me to get to that solution using SQL queries thru Java's JDBC for SQL Server driver.

I also wish to thanks the very quick answers I got from Tim Lehner and Mark Rotteveel.

解决方案

If a statement can return no or multiple results, you should not use executeQuery, but execute() instead, this method returns a boolean indicating the type of the first result:

• true: result is a ResultSet
• false : result is an update count

If the result is true, then you use getResultSet() to retrieve the ResultSet, otherwise getUpdateCount() to retrieve the update count. If the update count is -1 it means there are no more results. Note that the update count will also be -1 when the current result is a ResultSet. It is also good to know that getResultSet() should return null if there are no more results or if the result is an update count.

Now if you want to retrieve more results, you call getMoreResults() (or its brother accepting an int parameter). The return value of boolean has the same meaning as that of execute(), so false does not mean there are no more results!

There are only no more results if the getMoreResults() returns false and getUpdateCount() returns -1 (as also documented in the Javadoc)

Essentially this means that if you want to correctly process all results you need to do something like below:

boolean result = stmt.execute(...);
while(true)
    if (result) {
        ResultSet rs = stmt.getResultSet();
        // Do something with resultset ...
    } else {
        int updateCount = stmt.getUpdateCount();
        if (updateCount == -1) {
            // no more results
            break;
        }
        // Do something with update count ...
    }
    result = stmt.getMoreResults();
}

NOTE: Part of this answer is based on my answer to Java SQL: Statement.hasResultSet()?

这篇关于执行& quot; sp_msforeachdb& quot;在Java应用程序中的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！