overview query

I'm trying to write a query to get a table that contains some basic information such as table names, table sizes (in rows) and such.

I am able to do a query to get the table names:
SELECT name FROM dbo.sysobjects WHERE type='U' and name !='dtproperties' ORDER BY name

This shows a listing sorta like:
table1
table2
table3
table4

I would like to do a
SELECT COUNT(*) table1
SELECT COUNT(*) table2
SELECT COUNT(*) table3
SELECT COUNT(*) table4

so that my output looks like
table1 13
table2 47
table3 0
table4 93

anyone know a way i can do this in 1 sql query, or know a way to use multiple sql statements with an asp.net datagrid?don't know of a single select to handel your request but could yo ucall a stored procedure from your ASP page?

create procedure TableRowCount
as
declare @.TableName sysname
create table #Tmp (TableName sysname, [RowCount] int)
select @.TableName = min([name]) from sysobjects WHERE type='U' and [name] !='dtproperties'
while (@.TableName is not null) begin
insert into #Tmp
exec('select ''' + @.TableName + ''', count(*) from ' + @.TableName)
select @.TableName = min([name]) from sysobjects WHERE type='U' and [name] !='dtproperties' and [name] > @.TableName
end
select cast(TableName as varchar) as TableName, [RowCount] From #Tmp
return 0
go

exec TableRowCount
go|||thanks! you're a genius. now i just have to figure out what all that means. i'm not used to stored procedures, but that gives the output i need, in the format i need it.

Thanks again!|||Basicaly, I created a temporary table to hold the results of counting the number of records in a table, then stepped through the sysobjects table looking for records that matched your original where clause. Once a table was found I did the count and stuffed the result set into the temp table. When there were no more table to count I returned the result set. All of this is basic stuff and Books Online will answer any questions you have.

I should have put comments in the code!|||na, it's fine how it was, because I understood most of it, and i'm just a sql beginner.

I just didn't understand the #Tmp being a temporary table, since i'd never seen those, but that's the idea i was going to try, but keeping track of the table was going to be a pain.

Hopefully now I'll be able to finish my application :)|||would #Tmp be what i'm hearing be referred to as a cursor? My boss does a lot of programming, and suggested that i make a cursor, but i had never heard that terminology.|||No #Tmp is a sesion temporary table. Once you log off the table is destroyed.

You can read up on cursors in Microsofts's Books Online. The have their uses but are VERY resource intensive.

The solution I posted is an alternative to a cursor.|||ok cool

you bet i'll be reading those ;) I'd really eventually like to do some more sql programming, it's kinda just going along with my asp.net stuff right now, but that's next on the list, since data is important to all applications ;)

thanks again paul