drop proc usp_DBA_GetRowcntPerTbl go

CREATE PROC usp_DBA_GetRowcntPerTbl AS

SET NOCOUNT ON

DECLARE @tbl_count SMALLINT

DECLARE @name VARCHAR(70)

SELECT identity(INT,1,1) AS recno,name as tablename, tableowner = CONVERT(sysname,USER_NAME(uid)) INTO #tblnames FROM sysobjects WHERE type='u' and name<>'dtproperties' ORDER BY name

SELECT @tbl_count=COUNT(recno) FROM #tblnames

CREATE TABLE #tbl_row_counts(tbl_name VARCHAR(70),row_count INT,reserved VARCHAR(20),data VARCHAR(20),index_size VARCHAR(20),unused VARCHAR(20))

WHILE @tbl_count<>0

BEGIN

SELECT @name=tableowner+'.'+tablename FROM #tblnames WHERE recno=@tbl_count

INSERT INTO #tbl_row_counts EXEC sp_SPACEUSED @name SET @tbl_count=@tbl_count-1

END

SELECT tbl_name,row_count FROM #tbl_row_counts ORDER BY row_count desc--tbl_name

SET NOCOUNT OFF

create procedure usp_DBA_RowCount @pbOrder bit = 0 as /************************************************************************************************** Procedure: usp_DBA_RowCount

Version: 1.1

Purpose: Returns rowcounts for all tables in the current database, specify order by rowscount or table name.

History: Douglas Bass 07/29/1999 Keith Biddle 11/3/2002

Input Params: 0 = Order by rows - default 1 = order by table name, in fact any value that <>0.

Called by: Manual via QA.

Caveat: This procedure makes use of sysindexes. **************************************************************************************************/ DECLARE @bOrderByRows bit

SELECT @bOrderByRows = 0

IF @pbOrder = @bOrderByRows

BEGIN select o.name, i.rows from sysobjects o inner join sysindexes i on (o.id = i.id) where o.type = 'u' and i.indid < 2 order by i.rows desc

END

ELSE

BEGIN select o.name, i.rows from sysobjects o inner join sysindexes i on (o.id = i.id) where o.type = 'u' and i.indid < 2 order by i.name asc

END