SandeepIVS
asked on
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
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
this should help
EXEC sp_spaceused ''
and you can see the number of row,DB space used,index space used /per table
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
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
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
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