create procedure sp_rows

/*
** Title:       Row Return
** Version:	2.0.1
** Date:        20 XII 1999
** Author:      Jarlath O'Grady MCP
** SQL Version: 6.5, 7.0
** Description: Returns the number of rows within a/all table(s)
** Parameters:	@table 	- Name of table
**		@type	- Type of table (System tables are ignored by default)
** Comments:	With no table parameter the number of rows in all tables is displayed.
**		If a table name is not found, then the number of rows of tables with
**		a name like @table are returned
*/

	@table	varchar(30) = null,
	@type	char(1) = 'u'
as 

declare	@rows	int,
	@count	int,
	@print	varchar(255),
		@ver	decimal (18, 5)


select	@ver = convert (decimal (18,5), substring (@@version, 21, 6))

if 	@ver != 6.5
	begin

	print	'This procedure is designed to run on version SQL Server 6.5!.'
	print	''

	end

if	@type = 'u' and @table is not null
	begin
	select	@count = count(1)
	from	sysobjects
	where	type = 's'
	and	object_name(id) = @table

	if	@count > 0
		begin
		select	@print = "Table "+@table+" is a system table. @type parameter needs to be set to 's'"
		print	@print 
		return
		end
	end

if 	@table is not null
	begin

	select	@count = count(1)
	from	sysindexes
	where	object_name(id) = @table
	and	indid < 2
	
	if	@count = 0
		begin
		select	@print = 'The table "'+@table+'" does not exist'
		print	@print

		select	@count = count(1)
		from	sysindexes, sysobjects
		where	object_name(sysindexes.id) = sysobjects.name
		and	object_name(sysindexes.id) like '%'+@table+'%'

		and	indid < 2
		and	type in ('u',@type)

		print	''
		if	@count = 0
			begin
			select	@print = 'There are no table names like "'+@table+'"'
			print	@print 
			end
		else
			begin
			select	@print = 'The following '+convert (varchar, @count)+' tables are like "'+@table+'"'
			print	@print 
			print 	""

			select	convert (varchar, object_name(sysindexes.id)) as 'Table', rows as 'Rows',
				case
					when 	type = 'u' then
						'user table'
					when 	type = 's' then
						'system table'
				end as Type
			from	sysindexes, sysobjects
			where	object_name(sysindexes.id) = sysobjects.name
			and	object_name(sysindexes.id) like '%'+@table+'%'

			and	indid < 2
			and	type in ('u',@type)
			order by object_name(sysindexes.id)
			end
		end
	else
		begin
		select	@rows = rows
		from	sysindexes, sysobjects
		where	object_name(sysindexes.id) = sysobjects.name
		and	object_name(sysindexes.id) = @table

		and	indid < 2
		and	type in ('u',@type)
		order by object_name(sysindexes.id)

		select	@print = 'The table "'+@table+'" contains '+convert(varchar, @rows)+' rows.'
		print	@print
		end
	end

else
	begin
	select	@count = count(1)
	from	sysindexes, sysobjects
	where	object_name(sysindexes.id) = sysobjects.name
	and	indid < 2
	and	type in ('u',@type)

	select	@print = 'No @table parameter. Row count of all '+convert (varchar, @count)+' tables.'
	print	@print
	print	''
	print	'Ordered by Name'
	print	''

	select	convert (varchar, object_name(sysindexes.id)) as 'Table', rows as 'Rows',
		case
			when 	type = 'u' then
				'user table'
			when 	type = 's' then
				'system table'
		end as Type
	from	sysindexes, sysobjects

	where	object_name(sysindexes.id) = sysobjects.name
	and	indid < 2
	and	type in ('u',@type)
	order by object_name(sysindexes.id) 

	print	''

	print	'Ordered by Rows'
	print	''

	select	rows as 'Rows', convert (varchar, object_name(sysindexes.id)) as 'Table',
		case
			when 	type = 'u' then
				'user table'
			when 	type = 's' then
				'system table'
		end as Type
	from	sysindexes, sysobjects
	where	object_name(sysindexes.id) = sysobjects.name
	and	indid < 2
	and	type in ('u',@type)

	order by rows
	end