Systems Engineering and RDBMS

More on sp_MSforeachTable

Posted by decipherinfosys on June 8, 2007

In one of our previous blog post, we had covered two un-documented but very handy system stored procedures in SQL Server. One of those was sp_MSforeachTable. Take a look at the post to see what this is for and what it can do for you. In this post, we are going to address the issue of collecting the data that is streamed through this command into a table so that we do not hit the 100 query result sets issue. In other words, if you run a command like:

EXEC sp_MSforeachtable @command1=’select ”?”, count(*) from ?’

and you have more than 100 tables in your schema, then you will get the error message:

“The query has exceeded the maximum number of result sets that can be displayed in the results grid. Only the first 100 result sets are displayed in the grid.”

Not to mention that the output is not readable since it is in the form of separate result sets. You can circumvent the error by chosing to run in a text mode but that is not desirable either since that still is not a readable format. Best thing to do would be to dump the records into a table and then read from it, example:

/*Create the global temporary table – you can choose to create a physical table if you want to preserve the records for longer*/

create table ##table (table_name varchar(50), num_rows int);

EXEC sp_MSForEachTable @command1=’INSERT INTO ##table(Table_Name,Num_Rows) select ”?”, count(*) from ? ‘;

/*Look at the record counts now*/

select * from ##table

Doing this, you can get away from the record-sets issue. For other ways of counting the number of records in SQL Server, you can look at our previous blog post here.

2 Responses to “More on sp_MSforeachTable”

  1. […] 1) SQL Server – here and here. […]

  2. […] sp_MSForEachTable – here. […]

Sorry, the comment form is closed at this time.


Get every new post delivered to your Inbox.

Join 88 other followers

%d bloggers like this: