Execute “sp_msforeachdb” in a Java application

Question

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.

Answer 1

If you're not getting an error, one issue might be that sp_msforeachdb will return a separate result set for each database rather than one set with all records. That being the case, you might try a bit of dynamic SQL to union-up all of your rows:

-- Use sys.tables
declare @sql nvarchar(max)
select @sql = coalesce(@sql + ' union all ', '') + 'select ''' + quotename(name) + ''' as database_name, * from ' + quotename(name) + '.sys.tables'
from sys.databases
select @sql = @sql + ' order by database_name, name'
exec sp_executesql @sql

I still sometimes use INFORMATION_SCHEMA views as well, since it's easier to see the schema name, among other things:

-- Use INFORMATION_SCHEMA.TABLES to easily get schema name
declare @sql nvarchar(max)
select @sql = coalesce(@sql + ' union all ', '') + 'select * from ' + quotename(name) + '.INFORMATION_SCHEMA.TABLES where TABLE_TYPE = ''BASE TABLE'''
from sys.databases
select @sql = @sql + ' order by TABLE_CATALOG, TABLE_SCHEMA, TABLE_NAME'
exec sp_executesql @sql

Be aware that this method of string concatenation (select @sql = foo from bar) may not work as you intend through a linked server (it will only grab the last record). Just a small caveat.

Answer 2

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. Be aware that I did not actually try it with your statement, nor am I sure if the SQL Server JDBC driver correctly implements multiple results, so it might not work:

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()?

Answer 3

UPDATE

I've found the solution !

After reading an article about sp_spaceused being used with Java, I figured out that I was in the same case.

My final code is the following :

this.instances = instances;
for(int i = 0 ; i < this.instances.size() ; i++)
{
    try
    {
        Class.forName("com.microsoft.sqlserver.jdbc.SQLServerDriver");
        this.connect = DriverManager.getConnection("jdbc:sqlserver://" + this.instances.get(i), "tluser", "result");

        this.statement = this.connect.prepareCall("{call sp_msforeachdb(?)}");
        this.statement.setString(1, "Use ?; SELECT DB_NAME() AS DB, name FROM sys.tables WHERE DB_NAME() NOT IN('master', 'model', 'msdb', 'tempdb')");
        this.resultats = this.statement.execute();

        while(true)
        {
            int rowCount = this.statement.getUpdateCount();
            if(rowCount > 0)
            {
                this.statement.getMoreResults();
                continue;
            }
            if(rowCount == 0)
            {
                this.statement.getMoreResults();
                continue;
            }

            ResultSet rs = this.statement.getResultSet();
            if(rs != null)
            {
                while (rs.next())
                {
                     this.sortie.ecrireResultats(rs); // Write the results to a file
                }
                rs.close();
                this.statement.getMoreResults();
                continue;
            }
            break;
        }
        this.statement.close();
    }
    catch(Exception e)
    {
        e.printStackTrace();
    }
}

It tried it out and my file has everything I want in it.

Thank you all for your help ! :)