nested select

is it possible to make this query right?
SELECT name ,(select count(*) FROM NAME) FROM sysobjects
WHERE xtype='U'
I'm trying to get the list of the tables names from the current database and
for each table to get it's rows number. I dont want to use functions. I need
to do it in one query.
Regards
OfirNope, you can't have a nested SELECT where you want the table itself to be a
value from outer SELECT.
Something like this should work, but the rowcount is not 100% accurate:
SELECT o.name, i.rows
FROM sysobjects AS o
INNER JOIN
sysindexes AS i
ON o.id = i.id
WHERE o.type = 'u'
AND i.indid in (1, 0)
You could use sp_spaceused instead. See Books Online for more info. Also,
see if this helps:
http://vyaskn.tripod.com/sp_show_biggest_tables.htm
--
HTH,
Vyas, MVP (SQL Server)
SQL Server Articles and Code Samples @. http://vyaskn.tripod.com/
"ofir" <ofir@.discussions.microsoft.com> wrote in message
news:FDFA7C45-49BE-4EAE-A2C6-A2C7428E0AC3@.microsoft.com...
is it possible to make this query right?
SELECT name ,(select count(*) FROM NAME) FROM sysobjects
WHERE xtype='U'
I'm trying to get the list of the tables names from the current database and
for each table to get it's rows number. I dont want to use functions. I need
to do it in one query.
Regards
Ofir|||Hi
You can also try using this
create table #rowcount (tablename varchar(128), rowcnt int)
exec sp_MSforeachtable
'insert into #rowcount select ''?'', count(*) from ?'
select * from #rowcount
drop table #rowcount
but, please note that sp_msforeachtable is an undocumented stored procedure.
best Regards,
Chandra
http://chanduas.blogspot.com/
http://groups.msn.com/SQLResource/
---
"ofir" wrote:

> is it possible to make this query right?
> SELECT name ,(select count(*) FROM NAME) FROM sysobjects
> WHERE xtype='U'
> I'm trying to get the list of the tables names from the current database a
nd
> for each table to get it's rows number. I dont want to use functions. I ne
ed
> to do it in one query.
> Regards
> Ofir|||what do u mean 'the rowcount is not 100%'
"Narayana Vyas Kondreddi" <answer_me@.hotmail.com> wrote in message
news:ucnoqMSYFHA.252@.TK2MSFTNGP12.phx.gbl...
> Nope, you can't have a nested SELECT where you want the table itself to be
> a
> value from outer SELECT.
> Something like this should work, but the rowcount is not 100% accurate:
> SELECT o.name, i.rows
> FROM sysobjects AS o
> INNER JOIN
> sysindexes AS i
> ON o.id = i.id
> WHERE o.type = 'u'
> AND i.indid in (1, 0)
>
> You could use sp_spaceused instead. See Books Online for more info. Also,
> see if this helps:
> http://vyaskn.tripod.com/sp_show_biggest_tables.htm
> --
> HTH,
> Vyas, MVP (SQL Server)
> SQL Server Articles and Code Samples @. http://vyaskn.tripod.com/
>
> "ofir" <ofir@.discussions.microsoft.com> wrote in message
> news:FDFA7C45-49BE-4EAE-A2C6-A2C7428E0AC3@.microsoft.com...
> is it possible to make this query right?
> SELECT name ,(select count(*) FROM NAME) FROM sysobjects
> WHERE xtype='U'
> I'm trying to get the list of the tables names from the current database
> and
> for each table to get it's rows number. I dont want to use functions. I
> need
> to do it in one query.
> Regards
> Ofir
>|||sysindexes maintains a rowcount, but it's not 100% accurate. Only reliable
way is to SELECT COUNT(*) from the table.
--
HTH,
Vyas, MVP (SQL Server)
SQL Server Articles and Code Samples @. http://vyaskn.tripod.com/
"ofir" <ofir@.millenium.org.il> wrote in message
news:u$O0SHTYFHA.1404@.TK2MSFTNGP09.phx.gbl...
what do u mean 'the rowcount is not 100%'
"Narayana Vyas Kondreddi" <answer_me@.hotmail.com> wrote in message
news:ucnoqMSYFHA.252@.TK2MSFTNGP12.phx.gbl...
> Nope, you can't have a nested SELECT where you want the table itself to be
> a
> value from outer SELECT.
> Something like this should work, but the rowcount is not 100% accurate:
> SELECT o.name, i.rows
> FROM sysobjects AS o
> INNER JOIN
> sysindexes AS i
> ON o.id = i.id
> WHERE o.type = 'u'
> AND i.indid in (1, 0)
>
> You could use sp_spaceused instead. See Books Online for more info. Also,
> see if this helps:
> http://vyaskn.tripod.com/sp_show_biggest_tables.htm
> --
> HTH,
> Vyas, MVP (SQL Server)
> SQL Server Articles and Code Samples @. http://vyaskn.tripod.com/
>
> "ofir" <ofir@.discussions.microsoft.com> wrote in message
> news:FDFA7C45-49BE-4EAE-A2C6-A2C7428E0AC3@.microsoft.com...
> is it possible to make this query right?
> SELECT name ,(select count(*) FROM NAME) FROM sysobjects
> WHERE xtype='U'
> I'm trying to get the list of the tables names from the current database
> and
> for each table to get it's rows number. I dont want to use functions. I
> need
> to do it in one query.
> Regards
> Ofir
>