Hi John,

This script below will give you details for all tables in DB.


SET NOCOUNT ON
CREATE TABLE #temp(
rec_id int IDENTITY (1, 1),
table_name varchar(128),
nbr_of_rows int,
data_space decimal(15,2),
index_space decimal(15,2),
total_size decimal(15,2),
total_sizeg decimal(15,2),
percent_of_db decimal(15,2),
db_size decimal(15,2),
db_sizeg decimal(15,2))

-- Get all tables, names, and sizes
EXEC sp_MSforeachtable @command1="insert into #temp(nbr_of_rows,
data_space, index_space) exec sp_MStablespace '?'",
@command2="update #temp set table_name = '?' where rec_id = (select
max(rec_id) from #temp)"

-- Set the total_size and total database size fields
UPDATE #temp
SET total_size = (data_space + index_space), db_size = (SELECT
SUM(data_space + index_space) FROM #temp)

-- Convert total_size to GIG
UPDATE #temp
SET total_sizeg = total_size /1048000

-- Convert db_size to GIG
UPDATE #temp
SET db_sizeg = db_size /1048000

-- Set the percent of the total database size
UPDATE #temp
SET percent_of_db = (total_size/db_size) * 100

-- Get the data
SELECT @@ServerName as ServerName, table_name as TableName, nbr_of_rows
as [Rows],
data_space as DataKB, index_space as IndexKB,
total_size as TotalSizeKB,
total_sizeg as TotalSizeGB,
percent_of_db as [%Usage],
db_size as DBSizeKB, db_sizeg as DBSizeGB,
(convert(varchar(8),getdate(),112)) as RunDate
FROM #temp
ORDER BY total_size DESC





-----Original Message-----
From: John King
[mailto:mssqldba-ezmlmshield-x40833365.[Email address protected]
Sent: 31 August 2007 09:10
To: LazyDBA Discussion
Subject: RE: RecordCount of Each Table in entire Database



Hi All,

Me again...

I have this

select o.name, i.rows
from sysobjects o, sysindexes i
where i.id = OBJECT_ID(o.name)
and i.indid < 2 and o.xtype = 'u'

I now need to add the table size to the script.

Any suggestions?

TIA.

8.47am here leasve at 16.45...THEN my Weekend should start....but have to
work tomorrow sigh...


-----Original Message-----
From: John King
[mailto:mssqldba-ezmlmshield-x76197238.[Email address protected]
Sent: 31 August 2007 08:37 AM
To: LazyDBA Discussion
Subject: RecordCount of Each Table in entire Database


Hi All,

Could sombody please supply me with a script to bring back the number of
records in each table in a Database.

TIA

Happy Friday to one and all......

Regards
John King

This e-mail and its contents are subject to Albaraka Bank Limited's
Disclaimer and Confidentiality Clause, which can be viewed at:
http://www.albaraka.co.za/e-mail-disclaimer.htm Should you be unable to
access the link provided, please send a blank e-mail to
[Email address protected]



---------------------------------------------------------------------
TO REPLY TO EVERYBODY , PLEASE CLICK REPLY-ALL, NOT JUST REPLY
To post a dba job: http://jobs.lazydba.com
To subscribe : http://www.LazyDBA.com
To unsubscribe: http://www.lazydba.com/unsubscribe.html




---------------------------------------------------------------------
TO REPLY TO EVERYBODY , PLEASE CLICK REPLY-ALL, NOT JUST REPLY
To post a dba job: http://jobs.lazydba.com
To subscribe : http://www.LazyDBA.com
To unsubscribe: http://www.lazydba.com/unsubscribe.html


-----------------------------------------
Please click on the following link to read the
Old Mutual legal notice:
<http://www.oldmutual.co.za/e-mail_legal_notice>
Alternatively, send a "blank" e-mail to
<mailto:[Email address protected]


MS Sql Server LazyDBA home page