June 1, 2006 at 5:10 am
>any of the variants looking at the metadata
what variants?
Tim Wilkinson
"If it doesn't work in practice, you're using the wrong theory"
- Immanuel Kant
June 1, 2006 at 5:43 am
revised code using count(*) instead of getting first column.
obviously
------------------------------------------------------------------
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))
insert @tab
select distinct o.name
from sysobjects o
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(*)from ' +table_name
from @tab
where id = @i
print @sql
exec sp_executesql @sql
set @i= @i +1
end
select * from DBA_RowNumber
June 2, 2006 at 6:08 am
Well, 'any variants' meaning any way you want but actually counting the actual rows. (afaik, the only place I can think of where you may find metadata rowcounts in 7.0 or 2k is sysindexes.rowcnt or sysindexes.rows - the latter being a computed column with max value of 2147483647, the former is a - bigint in SQL 2k, a binary in 7.0)
/Kenneth
Viewing 3 posts - 16 through 17 (of 17 total)
You must be logged in to reply to this topic. Login to reply