Выполнить sp_msforeachdb в приложении Java

Question

Привет, сообщество Stackru:)

Я пришел к вам, чтобы поделиться одной из моих проблем...

Я должен извлечь список каждой таблицы в каждой базе данных экземпляра SQL Server, я нашел этот запрос:

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

Он отлично работает в Microsoft SQL Server Management Studio, но когда я пытаюсь выполнить его в своей Java-программе (которая включает в себя драйверы JDBC для SQL Server), он говорит, что не возвращает никакого результата.

Мой Java-код выглядит следующим образом:

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

Спасибо всем, кто попытается мне помочь,Хорошего дня:)

РЕДАКТИРОВАТЬ 1:

Я не уверен, что драйвер JDBC для SQL Server поддерживает мой запрос, поэтому я попытаюсь достичь своей цели другим способом.

Я пытаюсь получить список всех таблиц для каждой базы данных на экземпляре SQL Server. Выходной формат будет следующим:

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

Итак, теперь я спрашиваю, может ли кто-нибудь помочь мне получить это решение, используя запросы SQL через драйвер JDBC для SQL Server Java.

Я также хотел бы поблагодарить очень быстрые ответы, которые я получил от Tim Lehner и Mark Rotteveel.

Answer 1

Если выписка может вернуть нет или несколько результатов, вы не должны использовать executeQuery, но execute() вместо этого этот метод возвращает boolean с указанием типа первого результата:

• true: результат ResultSet
• false: результат - количество обновлений

Если результат true тогда вы используете getResultSet() чтобы получить ResultSet, иначе getUpdateCount() чтобы получить количество обновлений. Если счетчик обновлений -1 значит, больше нет результатов. Обратите внимание, что количество обновлений также будет -1 когда текущий результат ResultSet, Также хорошо знать, что getResultSet() должен возвращать ноль, если результатов больше нет или если результатом является количество обновлений.

Теперь, если вы хотите получить больше результатов, вы звоните getMoreResults() (или его брат принимает int параметр). Возвращаемое значение boolean имеет то же значение, что и execute(), так false не означает, что больше нет результатов!

Больше нет результатов, если getMoreResults() возвращает ложь и getUpdateCount() возвращается -1 (как также задокументировано в Javadoc)

По сути это означает, что если вы хотите правильно обработать все результаты, вам нужно сделать что-то вроде ниже. Помните, что я на самом деле не пробовал его с вашим утверждением, и я не уверен, что драйвер JDBC для SQL Server правильно реализует несколько результатов, поэтому он может не работать:

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();
}

ПРИМЕЧАНИЕ. Часть этого ответа основана на моем ответе на Java SQL: Statement.hasResultSet()?

Answer 2

ОБНОВИТЬ

Я нашел решение!

Прочитав статью об использовании sp_spaceused с Java, я понял, что был в том же случае.

Мой окончательный код следующий:

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();
    }
}

Он попробовал это, и в моем файле есть все, что я хочу.

Спасибо за вашу помощь! :)

Answer 3

Если вы не получаете сообщение об ошибке, одной из проблем может быть sp_msforeachdb вернет отдельный набор результатов для каждой базы данных, а не один набор со всеми записями. В таком случае вы можете попробовать немного динамического SQL для объединения всех ваших строк:

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

Я до сих пор иногда использую представления INFORMATION_SCHEMA, поскольку среди прочего легче увидеть имя схемы:

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

Помните, что этот метод объединения строк (select @sql = foo from bar) может работать не так, как вы собираетесь через связанный сервер (он будет захватывать только последнюю запись). Просто небольшая оговорка.