Run "sp_msforeachdb" in a Java application

Question

Hi, StackOverflow community :)

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

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

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

It works fine on Microsoft SQL Server Management Studio, but when I try to execute it in my Java program (including JDBC drivers for SQL Server), it says that it does not return any result .

My Java code is as follows:

 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 everyone who tries to help me. Have a nice day :)

EDIT 1:

I'm not sure if the SQL Server JDBC driver supports my query, so I will try to achieve my goal differently.

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

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

So now I am asking if anyone can help me get to this solution using SQL queries via Java JDBC for the SQL Server driver .

I also want to thank the quickest answers I received from Tim Lenner and Mark Rottwevel .

Answer 1

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

• true : result: ResultSet
• false : result - the number of updates

If the result is true , you use getResultSet() to retrieve the ResultSet , otherwise getUpdateCount() to get the number of updates. If the number of updates is -1 , then there are no more results. Note that the number of updates will also be -1 if the current result is ResultSet . It is also useful to know that getResultSet() should return null if there are no more results or the result is equal to the number of updates.

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

Results if getMoreResults() returns false and getUpdateCount() returns -1 (as also described in Javadoc)

Essentially, this means that if you want to process all the results correctly, you need to do something like below. Keep in mind that I really did not try to do this with your expression, and I'm not sure if the SQL Server JDBC driver correctly implements several results, so it may 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 in Java SQL: Statement.hasResultSet ()?

Answer 2

If you do not receive an error message, one problem may be that sp_msforeachdb will return a separate result set for each database, rather than one set with all records. In this case, you can try a bit of dynamic SQL to concatenate all 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 the INFORMATION_SCHEMA views, as it’s easier to see the name of the schema, by the way:

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

Remember that this string concatenation method ( select @sql = foo from bar ) may not work the way you intend through a linked server (it will only capture the last record). Just a little caution.

Answer 3

UPDATE

I have found a solution!

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

My last code is as follows:

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

He tried, and my file has everything I want.

Thank you all for your help! :)