query to find size of all tables in a database
-
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
-
Search this forum for sp_MSforeachtable and sp_spaceused.
BOL may also give you some useful information.
_____________
Code for TallyGenerator -
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] -
--Sorry..the Script provided by me is to find out the spaces of Databases..hence am rolling back my posting..
--- -
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] -
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 -
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 -
For up-to-date results you need to use:
@command1 = 'sp_spaceused ''?'', ''true'''
_____________
Code for TallyGenerator -
-- 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
-
Hi all,
Thank you for all the help.
I have got the reqd result using sp_MSForeachtable.....
dram
-
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
Viewing 11 posts - 1 through 10 (of 10 total)
You must be logged in to reply to this topic. Login to reply