Determine number of record and record size 
Author Message
 Determine number of record and record size
I need to know the number of records and the average size
of each record for every table in my database. I tried
sp_spaceused but have to execute it for over 200 tables
which is not acceptable.

I am looking for an answer that will allow me to select
and insert the information into a table so that I can
report against it.

Thanks in advance.
Harry



Tue, 29 Nov 2005 23:09:40 GMT
 Determine number of record and record size

Harry,

You can use a undocumented call sp_msforeachtable to run sp_spaceused on all
the tables in the current database.Heres the code:

DBCC UPDATEUSAGE
GO
create table #spspaceused
(
table_name varchar(80),
no_of_rows bigint,
reserved varchar(20),
data varchar(20),
index_size varchar(20),
unused varchar(20)
)
GO
insert #spspaceused

GO
select table_name,no_of_rows from #spspaceused
GO

Do note that integer values as reserved,data etc are stored as varchar in
order to match with sp_spaceused output.Make sure to do the necessary
scrubbing to take the count.Also, note that, as mentioned above
sp_msforeachtable is a undocu call and hence you should refrain from using
it in production code.

--
Dinesh.
SQL Server FAQ at
http://www.tkdinesh.com


Quote:
> I need to know the number of records and the average size
> of each record for every table in my database. I tried
> sp_spaceused but have to execute it for over 200 tables
> which is not acceptable.

> I am looking for an answer that will allow me to select
> and insert the information into a table so that I can
> report against it.

> Thanks in advance.
> Harry



Tue, 29 Nov 2005 23:27:07 GMT
 Determine number of record and record size
Harry,

First line should be... DBCC UPDATEUSAGE ('<database_name>')

--
Dinesh.
SQL Server FAQ at
http://www.tkdinesh.com


Quote:
> Harry,

> You can use a undocumented call sp_msforeachtable to run sp_spaceused on
all
> the tables in the current database.Heres the code:

> DBCC UPDATEUSAGE
> GO
> create table #spspaceused
> (
> table_name varchar(80),
> no_of_rows bigint,
> reserved varchar(20),
> data varchar(20),
> index_size varchar(20),
> unused varchar(20)
> )
> GO
> insert #spspaceused

> GO
> select table_name,no_of_rows from #spspaceused
> GO

> Do note that integer values as reserved,data etc are stored as varchar in
> order to match with sp_spaceused output.Make sure to do the necessary
> scrubbing to take the count.Also, note that, as mentioned above
> sp_msforeachtable is a undocu call and hence you should refrain from using
> it in production code.

> --
> Dinesh.
> SQL Server FAQ at
> http://www.tkdinesh.com



> > I need to know the number of records and the average size
> > of each record for every table in my database. I tried
> > sp_spaceused but have to execute it for over 200 tables
> > which is not acceptable.

> > I am looking for an answer that will allow me to select
> > and insert the information into a table so that I can
> > report against it.

> > Thanks in advance.
> > Harry



Tue, 29 Nov 2005 23:32:41 GMT
 
 [ 3 post ] 

 Relevant Pages 

1. data space (database size) how database size calculate for known record numbers

2. Find certain Number of Records / Print only certain number of records

3. Determining record size and file access methods

4. Dynamic SQL and determining number of records retrieved

5. How can I determine record numbers?

6. Determining Number of records in database

7. HELP: Determining number of records in result set

8. Determining Record Number in MSACCESS

9. combining records and determining record count

10. Size of tables, Number of records

11. Increment Number for next record from previous record value

12. I am getting duplicate records, I mean everything is duplicate even Access record number


 
Powered by phpBB® Forum Software