Sandip's Programming Zen

An attempt to share tech/coding experiences

How to get row count of every table in database

with 2 comments

One of my client application having performance issues and I found out that it is because of very large number of data in some of the tables, any application pages rendering data by referring those tables are bound to be slow. We could fix the issue by archiving unnecessary data. But I was wondering how can I keep an eye on such tables.

Solution: I made an sql script which run periodically (through sql agent) and populate a table with all table names and their row count info.

Script:

declare @query nvarchar(100)
declare @declare nvarchar(50)
declare @tablename varchar(50)
declare @rowcount int
declare table_cur cursor for SELECT name
FROM sysobjects WITH (NOLOCK)
WHERE xtype = ‘U’

open table_cur fetch next from table_cur into @tablename while @@fetch_status = 0
begin
set @declare = ‘@rowcount int output’
set @query = ‘set @rowcount = (select count(*) from ‘+@tablename + ‘)’
exec sp_executesql @query, @declare, @rowcount OUTPUT
insert into tablerowcount values (@tablename,@rowcount)
fetch next from table_cur into @tablename

end
close table_cur
deallocate table_cur

Once you have that table you can do virtually anthing to get yourself notified.

Hope this helps someone.

Advertisements

Written by Sandip

June 12, 2008 at 12:46 pm

Posted in MS SQL

2 Responses

Subscribe to comments with RSS.

  1. thanks. it helps me.

    colin

    July 25, 2008 at 6:00 pm

  2. u saved me lotta time. thank you

    andrea

    April 1, 2010 at 7:39 am


Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

%d bloggers like this: