stored - Ejecute "sp_msforeachdb" en una aplicación Java

procedimiento almacenado java sql server

Question

Hola comunidad de StackOverflow :)

Vengo a ti para compartir uno de mis problemas ...

Tengo que extraer una lista de cada tabla en cada base de datos de una instancia de SQL Server , encontré esta consulta:

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

Funciona perfectamente en Microsoft SQL Server Management Studio, pero cuando intento ejecutarlo en mi programa Java (que incluye controladores JDBC para SQL Server) dice que no devuelve ningún resultado .

Mi código Java es el siguiente:

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

Gracias a todos los que intentarán ayudarme, ten un buen día :)

EDIT 1:

No estoy seguro de que el controlador JDBC para SQL Server sea compatible con mi consulta, así que intentaré llegar a mi objetivo de otra manera.

Lo que intento obtener es una lista de todas las tablas para cada base de datos en una instancia de SQL Server , el formato de salida será el siguiente:

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

Así que ahora estoy preguntando si alguien me puede ayudar a llegar a esa solución usando consultas SQL a través del controlador JDBC para SQL Server de Java .

También deseo agradecer las respuestas muy rápidas que recibí de Tim Lehner y Mark Rotteveel .

Answer 1

ACTUALIZAR

¡Encontré la solución!

Después de leer un artículo sobre el uso de sp_spaceused con Java , descubrí que estaba en el mismo caso.

Mi código final es el siguiente:

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

Lo probé y mi archivo tiene todo lo que quiero en él.

Gracias por toda tu ayuda ! :)

Answer 2

Si no obtiene un error, un problema podría ser que sp_msforeachdb devolverá un conjunto de resultados por separado para cada base de datos en lugar de un conjunto con todos los registros. Siendo ese el caso, puede intentar un poco de SQL dinámico para unir todas sus filas:

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

Todavía uso algunas veces las vistas INFORMATION_SCHEMA, ya que es más fácil ver el nombre del esquema, entre otras cosas:

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

Tenga en cuenta que este método de concatenación de cadenas ( select @sql = foo from bar ) puede no funcionar como pretende a través de un servidor vinculado (solo obtendrá el último registro). Solo una pequeña advertencia.

Answer 3

Si una instrucción puede devolver resultados executeQuery o múltiples , no debe usar executeQuery , sino execute() , este método devuelve un boolean indica el tipo del primer resultado:

• true : el resultado es un ResultSet
• false : el resultado es un recuento de actualización

Si el resultado es true , entonces use getResultSet() para recuperar el ResultSet ; de lo contrario, getUpdateCount() para recuperar el recuento de actualizaciones. Si la cuenta de actualización es -1 significa que no hay más resultados. Tenga en cuenta que la cuenta de actualización también será -1 cuando el resultado actual sea un ResultSet . También es bueno saber que getResultSet() debería devolver nulo si no hay más resultados o si el resultado es un recuento de actualizaciones.

Ahora, si desea recuperar más resultados, llame a getMoreResults() (o su hermano aceptando un parámetro int ). El valor de retorno de boolean tiene el mismo significado que el de execute() , ¡así que false no significa que no hay más resultados!

Solo hay más resultados si getMoreResults() devuelve false y getUpdateCount() devuelve -1 (como también se documenta en el Javadoc)

Básicamente, esto significa que si desea procesar correctamente todos los resultados, debe hacer algo como a continuación. Tenga en cuenta que en realidad no lo intenté con su extracto, ni estoy seguro de si el controlador JDBC de SQL Server implementa correctamente varios resultados, por lo que podría no funcionar:

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

NOTA: Parte de esta respuesta se basa en mi respuesta a Java SQL: Statement.hasResultSet ()?