A new version of RowCount

,

Hello,

Here is a (another one, but improved) stored procedure that
returns rowcounts for all
tables in the current database, or only for @tablename, if
provided.
Output can be ordered by name or by number of rows.

Improvement : Rowcount is right aligned and formatted with
thousand separator.

To use it :

- open a connection on your favorite server, master database;
- [eventually adapt the script if you prefer a space for
thousand separator];
- run the script.

Since the stored proc is named sp_ *, it can be called from
any db on the server.
[tested on SQL 7/2000]

Congratulations for your site.

Jean-Louis PAUL
France.

if exists (select * from sysobjects where id = object_id('dbo.sp_rowcount') and sysstat & 0xf = 4)
	DROP PROCEDURE dbo.sp_rowcount
GO

/*************************************************************************************************************************
 - Object  : Returns rowcounts for all tables in the current database, or only for @tablename, if provided.
		Rowcount is right aligned and formatted with thousand separator.
 - Params : 
	- @tablename : a specific table
	- @ordered_by_rows : (1) = ordered by rowcount DESC, (0) = ordered by name ASC
 - Author : Jean-Louis PAUL
 - Date   : 08/07/2002
 - Remark : Adapted from a script written by : douglas a. bass; many thanks to him.
*************************************************************************************************************************/
CREATE PROCEDURE sp_rowcount

	@tablename 		VARCHAR(32) = null,
	@ordered_by_rows   	BIT = 1

AS

DECLARE	@dbname	VARCHAR(32)

SET NOCOUNT ON 

IF @tablename IS not null
	IF not exists (SELECT * FROM sysobjects WHERE id = object_id(@tablename) and sysstat & 0xf = 3)
	BEGIN

		SELECT @dbname = db_name()
		RAISERROR ('Object "%s" does not exist in database "%s" or is not a user table.', 
			   16, 1, @tablename, @dbname)
	END


-- Format the Rowcount with thousand separator + right aligned
SELECT 	'Rows' = convert(VARCHAR(20), 
	replicate(' ' , 20 - len(convert(VARCHAR(20), cast(i.rows as money), 1)) + 3) + 
	convert(VARCHAR(20),  
		left(
			-- >> please, uncomment the 2 lines below, if you prefer space as thousand separator <<
			--replace (
			convert(VARCHAR(20), cast(i.rows as money), 1), 
			--',', ' '), 
			datalength(convert(VARCHAR(20), cast(i.rows as money), 1)) - 3))), 
	'Name' = o.name 
FROM 	sysobjects o 
INNER JOIN sysindexes i 
	ON (o.id = i.id)
WHERE 	LOWER(o.type) = 'u'
AND 	o.id = isnull(object_id(@tablename), o.id)
AND	i.indid < 2
ORDER BY 	
CASE @ordered_by_rows WHEN 0 THEN o.name ELSE NULL END ASC, 
CASE @ordered_by_rows WHEN 1 THEN i.rows ELSE NULL END DESC

Rate

Share

Share

Rate