Insert rowcount into table

[wltenney]

I need to create and populate a table with the table name and row count for selected tables. Getting the row count is the problem. The following script illustrates what I need to do, but the select count(*) statement returns an invalid object error on table_name.

create table tablerowcount
(tablename sysname,
rowcount bigint)

insert tablerowcount
select table_name, (select count(*) from table_name)
from information_schema.columns
where column_name = 'sub'

Would appreciate anyone who could point me in the right direction.

Thanks

 

[tlbroadbent]

You can use the system table to get the row count.

Insert tablerowcount
Select o.Name. i.rowcnt
From Sysobjects o
Join sysindexes i
on o.id=i.id
Where i.indid<2

The sysindexes rowcnt may vary slightly from the actual row count because there is some delay in updating statistics, particularly on very active sytems or following mass inserts or deletes. However, if you are gathering a snapshot, I find the sysindexes rowcnt is accurate enough for my needs.

You could get the actual rowcnt of each table using count(*) but this process will be much slower. Use the undocumented system stored procedure, sp_msforeachtable to iterate through the tables in a database and run a query on each those tables.

Insert tablerowcount
exec sp_msforeachtable 'Select ''?'', count(*) From ?' If you want to get the best answer for your question read faq183-874 and thread183-468158.
Terry L. Broadbent - DBA
SQL Server Page:

http://tlbroadbent.home.attbi.com/sql/sql_articles.htm

 

[wltenney]

Thanks Terry,

I added a little code to select for tables with the desired column:

insert tablerwcount
select o.name, i.rowcnt, c.name
from syscolumns c
join sysobjects o
on c.id = o.id
join sysindexes i
on o.id = i.id
where c.name = 'sub' and
i.indid < 2 and o.xtype = 'U' and i.rowcnt > 0

One further question. While the lag in updating sysobjects may be small, it could be significant if some tables are reported with no rows. Will DBCC UPDATEUSAGE WITH COUNT_ROWS force an update of the rowcnt?

Thanks

 

[tlbroadbent]

DBCC UPDATEUSAGE (0) WITH COUNT_ROWS will update the rowcnt on index 0 or 1 for all tables in the current DB. If the DB contains large tables, this could be very slow. If you want to get the best answer for your question read faq183-874 and thread183-468158.
Terry L. Broadbent - DBA
SQL Server Page:

http://tlbroadbent.home.attbi.com/sql/sql_articles.htm