query to find size of all tables in a database

Question

Post reply

query to find size of all tables in a database

dram

SSC Enthusiast

Points: 148
More actions
June 13, 2007 at 2:29 pm

#177056

Hi,
My user has requested me to give him a query or a procedure,
to get the size of all the tables in a particular database
without passing any parameters from his side.
I tried to find it out using information_schema.tables,but i can't figure it out properly.
Also i tried if i could create a query,pass it to a variable and execute it like, exec(@sql),it didn't help me much.
Can anyone help me solve this issue
dram

Viewing 11 posts - 1 through 10 (of 10 total)

You must be logged in to reply to this topic. Login to reply

Sergiy SSC Guru Points: 110208 More actions · Answer 1

Search this forum for sp_MSforeachtable and sp_spaceused.

BOL may also give you some useful information.

_____________
Code for TallyGenerator

JacekO SSCertifiable Points: 6610 More actions · Answer 2

Size of a table is not a precise requirement but if you run this you should get the number of datapages used by each table and number of records.

DBCC UPDATEUSAGE (yourdatabase_name) WITH COUNT_ROWS

SELECT name, dpages, rows

FROM sysindexes

WHERE indid = 0

AND impid = -1

ORDER BY name

---------------------------------------------
[font="Verdana"]Nothing is impossible.
It is just a matter of time and money.[/font]

Jus SSCrazy Points: 2536 More actions · Answer 3

--Sorry..the Script provided by me is to find out the spaces of Databases..hence am rolling back my posting.. ---

JacekO SSCertifiable Points: 6610 More actions · Answer 4

If you need the size of the tables including the index pages then use this

SELECT name, dpages, reserved, rows

FROM sysindexes

WHERE indid = 0

AND impid = -1

ORDER BY name

Where reserved is the total size of data and index pages

---------------------------------------------
[font="Verdana"]Nothing is impossible.
It is just a matter of time and money.[/font]

Sugesh Kumar One Orange Chip Points: 27311 More actions · Answer 5

create table #spacedetails (name varchar(100),totrows bigint,reserved varchar(30), data varchar(30),index_size varchar(30), unused varchar(30))

insert into #spacedetails

exec sp_MSForeachtable @command1 = "sp_spaceused '?'"

select name,totrows, replace(data,'KB','') as 'Data in KB',

replace(Index_Size,'KB','') as 'Index Size in KB' from #spacedetails order by name

drop table #spacedetails

This script will fullfil your needs.

Cheers,
Sugeshkumar Rajendran
SQL Server MVP
http://sugeshkr.blogspot.com

Sugesh Kumar One Orange Chip Points: 27311 More actions · Answer 6

If you want you can sum the data,index and reserved columns to find out the exact space used by the tables.

Cheers,
Sugeshkumar Rajendran
SQL Server MVP
http://sugeshkr.blogspot.com

Sergiy SSC Guru Points: 110208 More actions · Answer 7

For up-to-date results you need to use:

@command1 = 'sp_spaceused ''?'', ''true'''

_____________
Code for TallyGenerator

jmcgarvey Default port Points: 1489 More actions · Answer 8

-- Create the temp table for further querying

CREATE TABLE #temp(

ServerName varchar(30),

DBName varchar(40),

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),

percent_of_db decimal(15,12),

db_size 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),

ServerName = @@ServerName,

DBName = DB_Name()

-- Set the percent of the total database size

UPDATE #temp

SET percent_of_db = (total_size/db_size) * 100

-- Get the data

-- Modify the percent_of_db int the where clause as

-- desired

SELECT

ServerName,

DBName,

table_name,

nbr_of_rows,

data_space,

index_space,

total_size,

cast(percent_of_db as decimal(5,2)) as PCNT,

db_size

FROM #temp

where percent_of_db > .99

ORDER BY total_size DESC

-- Comment out the following line if you want to do further querying

DROP TABLE #temp

dram SSC Enthusiast Points: 148 More actions · Answer 9

Hi all,

Thank you for all the help.

I have got the reqd result using sp_MSForeachtable.....

dram

Michael Valentine Jones SSC Guru Points: 64818 More actions · Answer 10

Here is a ready built script that will do what you want.

Script to analyze table space usage

http://www.sqlteam.com/forums/topic.asp?TOPIC_ID=61762