home
Returns the number of rows in each table in a database.
Author Nigel Rivett
if exists (select * from sysobjects where id = object_id(N'[dbo].[sp_GetRowsForAllTables]') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
drop procedure [dbo].[sp_GetRowsForAllTables]
GO
Create Procedure sp_GetRowsForAllTables
@DBName varchar(128) = null
as
set nocount on
if @DBName is null
set @DBName = db_name()
create table #a
(TableName varchar(128), norows int null, id int identity(1,1))
declare @id int ,
@maxID int ,
@TableName varchar(128) ,
@FKName varchar(128) ,
@cmd nvarchar(1000) ,
@rc int,
@spcmd varchar(1000)
set @cmd = 'exec ' + @DBName + '..sp_executesql N''insert #a (TableName)
select TABLE_NAME from information_schema.tables
where TABLE_TYPE = ''''BASE TABLE'''' ''
'
exec (@cmd)
select @id = 0 ,
@maxID = max(id)
from #a
while @id < @maxID
begin
select @id = min(id)
from #a
where id > @id
select @TableName = TableName
from #a
where id = @id
set @cmd = 'exec ' + @DBName + '..sp_executesql N''update #a set norows = (select rows from sysindexes where indid in (0,1) and id = object_id(''''' + @TableName + '''''))'
set @cmd = @cmd + ' where #a.id = ' + convert(varchar(10),@id) + ''''
exec (@cmd)
if @rc <> 0 or @@error <> 0
begin
raiserror('failed %s',16,-1,@TableName)
return
end
end
select * from #a
drop table #a
go
home