Sp_msforeachtable Returns Just First Table In C#

Question

I Want To List Tables Of a database with sp_msforeachtable.i did it well in SSMS(i wrote it in sp_list2 stored procedure), but when i want to use this stored procedure in c#,it justs return first table of database in list box!

the code in C#:

            SqlCommand cmd = cnn.CreateCommand();
            cmd.CommandType = CommandType.StoredProcedure;
            cmd.CommandText = "sp_list2";
            SqlParameter inparam = cmd.Parameters.Add("@dbname", SqlDbType.NVarChar);
            inparam.Direction = ParameterDirection.Input;
            inparam.Value = "DB";
            SqlDataReader rdr = cmd.ExecuteReader();
            dap.SelectCommand = cmd;
            while (rdr.Read())
            {
              listBox1.Items.Add(rdr[0]);//this is tables name
            }

My Stored Procedure :

create procedure sp_list2(@dbname nvarchar(max))
as
declare @query as nvarchar(max)
--declare @dbname as nvarchar(max)
SET @query=''
SET @query =@query + @dbname + '..sp_msforeachtable '' select ''''?'''' as ''''Table'''',           count(*) as ''''Rows'''' from ? ''' 
EXEC sp_executesql @query

Answer 1

You need to put rdr.NextResult(); inside your for loop:

while (rdr.Read())
{
    listBox1.Items.Add(rdr[0]);//this is tables name
    rdr.NextResult();
}

sp_MsForEachTable returns multiple result sets (one for each table), so you have to move to the next result set using NextResult when there are no further rows to read from the current one.

However, as it looks like you are trying to get the number of rows in each table, you could alternatively do this in a query that would return just one result set:

select t.name, p.rows from sys.tables t, sys.partitions p
where t.object_id = p.object_id

Answer 2

Consider querying from sys.tables instead of sp_msforeachtable:

select name from [DbName].sys.tables

This will return all tables in a single rowset.