Looping tables?

Hi, I would like to know if is possible to loop through all tables (via pure
sql statement or SP) on a database and gives me the record count from each
one...
Because I need to know the table wich has more records on it! Can you help
me ?
Thanks!You can use the undocumented sp_foreachtable:
sp_foreachtable ('select ''?'', count (*) from ?')
Tom
----
Thomas A. Moreau, BSc, PhD, MCSE, MCDBA, MCITP, MCTS
SQL Server MVP
Toronto, ON Canada
https://mvp.support.microsoft.com/profile/Tom.Moreau
"Paulo" <prbspfc@.uol.com.br> wrote in message
news:eJlXstHSIHA.5208@.TK2MSFTNGP04.phx.gbl...
Hi, I would like to know if is possible to loop through all tables (via pure
sql statement or SP) on a database and gives me the record count from each
one...
Because I need to know the table wich has more records on it! Can you help
me ?
Thanks!|||On Dec 27, 5:26=A0pm, "Paulo" <prbs...@.uol.com.br> wrote:
> Hi, I would like to know if is possible to loop through all tables (via pu=[/vbcol
]
re[vbcol=seagreen]
> sql statement or SP) on a database and gives me the record count from each=[/vbcol
]
[vbcol=seagreen]
> one...
> Because I need to know the table wich has more records on it! Can you help=[/vbcol
]
[vbcol=seagreen]
> me ?
> Thanks!
This is a bit of a hack but works for me:
select so.name,rowcnt
from sysindexes si, sysobjects so
where si.id =3D so.id
and so.type in ('U')
and si.status =3D 2066
order by so.name|||On SQL 2005?
"Tom Moreau" <tom@.dont.spam.me.cips.ca> escreveu na mensagem
news:e8MQlAISIHA.6036@.TK2MSFTNGP03.phx.gbl...
> You can use the undocumented sp_foreachtable:
> sp_foreachtable ('select ''?'', count (*) from ?')
> --
> Tom
> ----
> Thomas A. Moreau, BSc, PhD, MCSE, MCDBA, MCITP, MCTS
> SQL Server MVP
> Toronto, ON Canada
> https://mvp.support.microsoft.com/profile/Tom.Moreau
>
> "Paulo" <prbspfc@.uol.com.br> wrote in message
> news:eJlXstHSIHA.5208@.TK2MSFTNGP04.phx.gbl...
> Hi, I would like to know if is possible to loop through all tables (via
> pure
> sql statement or SP) on a database and gives me the record count from each
> one...
> Because I need to know the table wich has more records on it! Can you help
> me ?
> Thanks!
>|||Very very good man!
Thanks a lot !
"SB" <othellomy@.yahoo.com> escreveu na mensagem
news:68856583-1664-4038-adfb-ca87e89bbc02@.d4g2000prg.googlegroups.com...
On Dec 27, 5:26 pm, "Paulo" <prbs...@.uol.com.br> wrote:
> Hi, I would like to know if is possible to loop through all tables (via
> pure
> sql statement or SP) on a database and gives me the record count from each
> one...
> Because I need to know the table wich has more records on it! Can you help
> me ?
> Thanks!
This is a bit of a hack but works for me:
select so.name,rowcnt
from sysindexes si, sysobjects so
where si.id = so.id
and so.type in ('U')
and si.status = 2066
order by so.name|||On Dec 27, 5:00=A0pm, "Tom Moreau" <t...@.dont.spam.me.cips.ca> wrote:
> You can use the undocumented sp_foreachtable:
> sp_foreachtable ('select ''?'', count (*) from ?')
> --
> =A0 =A0Tom
> ----
> Thomas A. Moreau, BSc, PhD, MCSE, MCDBA, MCITP, MCTS
> SQL Server MVP
> Toronto, ON =A0 Canadahttps://mvp.support.microsoft.com/profile/Tom.Moreau=[/vbcol
]
[vbcol=seagreen]
> "Paulo" <prbs...@.uol.com.br> wrote in message
> news:eJlXstHSIHA.5208@.TK2MSFTNGP04.phx.gbl...
> Hi, I would like to know if is possible to loop through all tables (via pu=[/vbcol
]
re[vbcol=seagreen]
> sql statement or SP) on a database and gives me the record count from each=[/vbcol
]
[vbcol=seagreen]
> one...
> Because I need to know the table wich has more records on it! Can you help=[/vbcol
]
[vbcol=seagreen]
> me ?
> Thanks!
That should be
sp_msforeachtable 'select ''?'', count (*) from ?'|||It works for 7.0, 2000 and 2005.
Tom
----
Thomas A. Moreau, BSc, PhD, MCSE, MCDBA, MCITP, MCTS
SQL Server MVP
Toronto, ON Canada
https://mvp.support.microsoft.com/profile/Tom.Moreau
"Paulo" <prbspfc@.uol.com.br> wrote in message
news:%23RBSYEISIHA.1184@.TK2MSFTNGP04.phx.gbl...
On SQL 2005?
"Tom Moreau" <tom@.dont.spam.me.cips.ca> escreveu na mensagem
news:e8MQlAISIHA.6036@.TK2MSFTNGP03.phx.gbl...
> You can use the undocumented sp_foreachtable:
> sp_foreachtable ('select ''?'', count (*) from ?')
> --
> Tom
> ----
> Thomas A. Moreau, BSc, PhD, MCSE, MCDBA, MCITP, MCTS
> SQL Server MVP
> Toronto, ON Canada
> https://mvp.support.microsoft.com/profile/Tom.Moreau
>
> "Paulo" <prbspfc@.uol.com.br> wrote in message
> news:eJlXstHSIHA.5208@.TK2MSFTNGP04.phx.gbl...
> Hi, I would like to know if is possible to loop through all tables (via
> pure
> sql statement or SP) on a database and gives me the record count from each
> one...
> Because I need to know the table wich has more records on it! Can you help
> me ?
> Thanks!
>|||On Dec 27, 5:11=A0pm, "Paulo" <prbs...@.uol.com.br> wrote:
> Very very good man!
> Thanks a lot !
> "SB" <othell...@.yahoo.com> escreveu na mensagemnews:68856583-1664-4038-adf=[/vbcol
]
b-ca87e89bbc02@.d4g2000prg.googlegroups.com...[vbcol=seagreen]
> On Dec 27, 5:26 pm, "Paulo" <prbs...@.uol.com.br> wrote:
>
ch[vbcol=seagreen]
>
lp[vbcol=seagreen]
>
> This is a bit of a hack but works for me:
> select so.name,rowcnt
> from sysindexes si, sysobjects so
> where si.id =3D so.id
> and so.type in ('U')
> and si.status =3D 2066
> order by so.name
Also refer
[url]http://sqlblogcasts.com/blogs/madhivanan/archive/2007/11/02/different-ways-t=[/url
]
o-count-rows-from-a-table.aspx|||Since I don't like to use undocumented procedures, since they can go away at
any time, here is the script I use for this
/* Start Script */
Create table #tmpRowCounts (
TblName varchar(128),
RowCt int
)
DECLARE crgetrows CURSOR
FOR select name from sysobjects where xtype = 'U' order by name
DECLARE @.tblname varchar(128)
OPEN crgetrows
FETCH NEXT FROM crgetrows INTO @.tblname
WHILE (@.@.fetch_status <> -1)
BEGIN
IF (@.@.fetch_status <> -2)
BEGIN
Execute('
Declare @.rowcount int
select @.rowcount = count(*) from ' + @.tblname + '
Insert into #tmpRowCounts values(''' + @.tblname + ''',@.rowcount)
')
END
FETCH NEXT FROM crgetrows INTO @.tblname
END
CLOSE crgetrows
DEALLOCATE crgetrows
Select * from #tmpRowCounts
Drop table #tmpRowCounts
GO
/* End Script */
"Paulo" <prbspfc@.uol.com.br> wrote in message
news:eJlXstHSIHA.5208@.TK2MSFTNGP04.phx.gbl...
> Hi, I would like to know if is possible to loop through all tables (via
> pure sql statement or SP) on a database and gives me the record count from
> each one...
> Because I need to know the table wich has more records on it! Can you help
> me ?
> Thanks!
>|||> On SQL 2005?
Here's a SQL 2005-specific method:
SELECT
t.name,
SUM(rows) AS Rows
FROM sys.tables t
JOIN sys.partitions p ON
t.object_id = p.object_id
WHERE
p.index_id IN(0,1)
GROUP BY
t.name
Hope this helps.
Dan Guzman
SQL Server MVP
"Paulo" <prbspfc@.uol.com.br> wrote in message
news:%23RBSYEISIHA.1184@.TK2MSFTNGP04.phx.gbl...
> On SQL 2005?
> "Tom Moreau" <tom@.dont.spam.me.cips.ca> escreveu na mensagem
> news:e8MQlAISIHA.6036@.TK2MSFTNGP03.phx.gbl...
>