Count rows in all tables

				
--**************************************
-- Name: Count rows in all tables
-- Description:It provides a row count of each table in your current database (or if you want, it can do the server). When you are tuning, this gives you a good start on where to focus attention. Also, for 3rd party products, it shows the tables that are not in use and can be deleted or hidden.
-- By: russell dalton
--
-- Inputs:Within the sql, there is a line indicated to comment out if you want to do all databases.
--
-- Returns:The procedure returns table information and row counts for each table. By default the output is sorted by row size.
--
--This code is copyrighted and has-- limited warranties.Please see http://www.Planet-Source-Code.com/vb/scripts/ShowCode.asp?txtCodeId=641&lngWId=5--for details.--**************************************

set nocount on
set quoted_identifier off
declare @dbname varchar(40)
-------------------------------------------------------------------------------------
/* To run for all databases, comment out the line below.			 */
------------------------------------------------------------------------------------
select @dbname = db_name() 
------------------------------------------------------------------------------------
-- table to hold row counts so it can be sorted
create table #output
(dbname varchar(40),
owner varchar(30),
table_name varchar(30),
Row_count int)
-- This cursor loops through the current database or if the line above is commented out, it loops
-- through all databases but the ones excluded.
DECLARE db_cursor CURSOR for
	SELECT name 
	FROM master..sysdatabases
	 where name not in ('tempdb', 'Northwind', 'pubs', 'msdb',
			 'master', 'dbauseonly', 'dbautility', 'model') 
	and name = isnull(@dbname, name)
	order by name
OPEN db_cursor
FETCH NEXT FROM db_cursor INTO @dbname
WHILE (@@fetch_status <> -1)
BEGIN
select ' *********************** ' + @dbname + ' **************************'
-- This exec includes the next 60 or so lines of code. Be careful editing. 
-- Remember quotation marks start off embeded.
	exec("use " + @dbname + "
		declare @tblname varchar(30)
		declare @owner varchar(30)
		declare @sql_inner varchar(255)
		declare @sql_inner2 varchar(255)
		
		-- loop through all the tables in the current database
		DECLARE tbl_cursor CURSOR for
		select u.name, o.name from sysobjects o, sysusers u
		where o.type = 'U'
		and o.uid = u.uid 
		order by o.name, u.name
		OPEN tbl_cursor
		FETCH NEXT FROM tbl_cursor INTO @owner,@tblname
		WHILE (@@fetch_status <> -1)
		BEGIN
		
-- several of these declares and selects are from another procedure i'm working on. 
-- Not all are actually used.
declare @objname varchar(92)		/* the table to check for indexes */
declare @objid int		/* the object id of the table */
declare @indid int		/* the index id of an index */
declare @keys varchar(200)	/* string to build up index key in */
declare @inddesc varchar(68)	/* string to build up index desc in */
declare @dbname varchar(30)
declare @owner_name varchar(30)
declare @table_name varchar(30)
declare @row_count int
declare @database_name varchar(30)
declare @sqlcode varchar(255)
select @objname = @tblname
select @table_name = @objname
select @database_name = db_name()
select @owner_name = ''
/*
** Check to see the the table exists and initialize @objid.
*/
select @objid = object_id(@objname)
select @owner_name = u.name
from sysusers u, sysobjects o
where u.uid = o.uid
and @objid = id
-- build string to insert table and row count into temp table #output
select @sqlcode = 'insert into #output
(dbname, owner, table_name, row_count)
select ' + '''' + @database_name + '''' + +', '+ '''' + @owner + '''' + ',' + '''' + @tblname+'''' + ',' + ' count(*) from ' + @tblname
-- execute insert
exec(@sqlcode)
		FETCH NEXT FROM tbl_cursor INTO @owner,@tblname
	END")
--finally the end of the exec statement from way up there.
DEALLOCATE tbl_cursor
declare @total_records int
-- find total row count to compute percents
select @total_records = sum(i.row_count) 
from #output i 
-- create output. By using the space command, I get the numbers to be right aligned.
select dbname, owner, table_name, 
	substring(space(10-len(row_count))+convert(varchar(10),row_count),1,10) row_count, 
	substring(space(5-len(row_count*100/@total_records))+
		convert(varchar(10), convert(decimal(6,2), row_count * 100.00/@total_records)),1,10) percent_of_db
from #output
order by dbname, row_count desc
-- delete data for next database if multiple ones being done.
delete #output
FETCH NEXT FROM db_cursor INTO @dbname
END
-- Clean it up
DEALLOCATE db_cursor
drop table #output