A function to get row count - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2000 Forums

Transact-SQL (2000)

A function to get row count

Author

Topic

ygeorge
Yak Posting Veteran

68 Posts

Posted - 2006-02-24 : 12:07:23

Hi,

I need to have a function to get the row count by giving a table name. So I did this -

create function getRowCount (@TableName varchar(200))
returns int
as
begin
declare @ExecStmt nvarchar(2000)
declare @iOut int
select @ExecStmt = 'select @iOut = count(1) from ' + @TableName
execute sp_executesql @ExecStmt, N'@iOut int OUTPUT', @iOut
return (@iOut)
end

Unfortunately, the stored procedure cannot be executed in the function. Can anybody help to make it work?

Thanks,

G.

Srinika
Master Smack Fu Yak Hacker

1378 Posts

Posted - 2006-02-24 : 12:22:37

U should be able to use a stored procedure or the front end (eg. ADO ) can do that

TG
Master Smack Fu Yak Hacker

6065 Posts

Posted - 2006-03-02 : 16:01:49

If (for some reason) you need it in a function this should be much faster than using count(1). However, this requires your tables to have primary keys and the counts may not be 100% accurate (especially at times of high contention)


use pubs

if object_id('dbo.getRowCount') > 0
	drop function dbo.GetRowCount
go

create function dbo.getRowCount (@TableName varchar(200))
returns int
as
begin
	declare @rc int
	select @rc = rowcnt 
	from sysindexes with (nolock)
	where	id = object_id(@tablename)
	and indid < 2

	return @rc
end

go

select	dbo.getRowCount(table_name)  [rowcount]
	,table_name
from	information_schema.tables
where	TABLE_TYPE = 'base table'

output:
rowcount    table_name        
----------- ------------------
23          authors
3           discounts
43          employee
14          jobs
8           pub_info
8           publishers
86          roysched
21          sales
6           stores
25          titleauthor
18          titles

Be One with the Optimizer
TG

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources