We help IT Professionals succeed at work.

How to get the number of rows from all the tables in a particular database

Hi Friends,
             I have a Database consisting of around 1000 tables. So now, my requirement is to know
How many tables is having the data?
I mean, i should get only the tables consisting of data.
Can you please help me out? Waiting for your reply?


Thanks In Advance
Sandeep IVS
Comment
Watch Question

Commented:
Hi,

There is a stored procedure called SP_SOS which will give you a nice statistic of your database.

Link: http://media.techtarget.com/searchSQLServer/downloads/URL_for_SQL_Script_download_Tip_on_Sp_SOS.doc

The code contains some examples of how to use it.

Regards
zurb

James MurrellProduct Specialist

Commented:
this should help

EXEC sp_spaceused ''
and you can see the number of row,DB space used,index space used /per table
Specifically:

if exists (select * from sysobjects where name = [rowcount])
    drop table [rowcount]
go

CREATE TABLE [RowCount](
  TableName sysname,
  [RowCount] int
)

go

EXEC sp_MSForEachTable 'INSERT [RowCount](TableName, [RowCount]) SELECT ''?'', COUNT(*) FROM ?'
SELECT * FROM [RowCount]


Lee
Hi Guys,
 I got it..This is the way...

if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[GetRecordCountsForAllTables]') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
drop procedure [dbo].[GetRecordCountsForAllTables]
GO
SET QUOTED_IDENTIFIER OFF
GO
SET ANSI_NULLS OFF
GO

CREATE  Procedure dbo.GetRecordCountsForAllTables AS

select  'Owner'=convert(char(10),t.TABLE_SCHEMA),
      'Table Name'=convert(char(25),t.TABLE_NAME),
      'Record Count'=max(i.rows)
from sysindexes i, INFORMATION_SCHEMA.TABLES t
where t.TABLE_NAME = object_name(i.id)
      and t.TABLE_TYPE = 'BASE TABLE'
group by t.TABLE_SCHEMA, t.TABLE_NAME
GO

Exec Dbo.GetRecordCountsForAllTables

SET QUOTED_IDENTIFIER OFF
GO
SET ANSI_NULLS ON
GO



Regards,
Sandeep IVS