Get no of Rows using system table?
-
Hi,
Can any one help to find the no of rows in every table of a Database using system table?
-
this will work but might take a while to run on a many tabled DB with multi million row tables. Count_Big is, i think, only available in sql2005 if not on this version replace count_big with count.
create table DBA_RowNumber
(id int identity, table_name varchar (1000), No_Of_Rows bigint)
go
declare @no int, @i int, @sql nvarchar(4000)
declare @tab table (id int identity, table_name varchar (1000), column_name varchar(1000))
insert @tab
select distinct o.name , c.COLUMN_NAME
from sysobjects o
join information_schema.columns c
on c.table_name = o.name
and ORDINAL_POSITION=1
where o.type = 'u' --user tables only
select @no =scope_identity() , @i = 1 ,@sql =''
while @i <= @no
begin
select @sql = 'insert DBA_RowNumber (table_name,No_Of_Rows)'+char(10)
+' select ''' +table_name+''', count_big('+column_name+')from ' +table_name
from @tab
where id = @i
print @sql
exec sp_executesql @sql
set @i= @i +1
end
select * from DBA_RowNumber
-
Or:
- select
o.name, i.rowcnt
- from sysobjects o
- join sysindexes i
- on i.indid < 2
- and i.id = o.id
- where
o.xtype = 'U'
Tim Wilkinson
"If it doesn't work in practice, you're using the wrong theory"
- Immanuel Kant -
yes they have a spurious entry with indid = 0. indid 1 is always clustered.
I don't know what the forward-compatible, MS-sanctioned version of the code is though.
Tim Wilkinson
"If it doesn't work in practice, you're using the wrong theory"
- Immanuel Kant -
yes the information schema views as well as having an irritatingly long schema name are still quite basic and designed more like reports than useful programming tools. the sys schema has lots of good stuff in it though. But I don't know if this value is still available anywhere in the new views.
Tim Wilkinson
"If it doesn't work in practice, you're using the wrong theory"
- Immanuel Kant -
Thanks all for responding to my query...
gr8 stuff....
-
Note that you should run "DBCC UPDATEUSAGE (MyDatabase)" first for Tim's query to be accurate. That is the best method though.
Ryan Randall
Solutions are easy. Understanding the problem, now, that's the hard part.
-
True for v8 and earlier. In v9, MS have actually managed to keep the row count (and the good old rowmodctr) updated automatically. How clever!
Tim Wilkinson
"If it doesn't work in practice, you're using the wrong theory"
- Immanuel Kant -
sys.partitions.rows (bigint) - Approximate number of rows in this partition.
Note approx, but then given we are finding the height of all tables in the DB, this sounds like more of an admin task (perhaps planning or maintenance) than say a stats report. So approx. counts would probably do.
Tim Wilkinson
"If it doesn't work in practice, you're using the wrong theory"
- Immanuel Kant -
I didn't know that (about the v9 update) - thanks Tim. The boys at MS have been working hard...
Ryan Randall
Solutions are easy. Understanding the problem, now, that's the hard part.
-
Still, it's essential to first make the decision if the count needed should be approximate or absolute.
For all purposes, if approximate is good enough, then any of the variants looking at the metadata in the systables is ok.
If you do need an absolute and correct value, no deviations allowed, then there's no substitute for the good old 'select count(*) from myTable' - not even in 2005. It's been enhanced in 2005, yes, but still doesn't guarantee realtime correctness.
/Kenneth
Viewing 15 posts - 1 through 15 (of 17 total)
You must be logged in to reply to this topic. Login to reply