Find the size of the each table in a database of SQL Server
We can find out the size of each table in the database by executing the query given below.We can find out the size occupied by the data in the table, number of pages occupied and all.
set nocount on
DECLARE @tablename VARCHAR(250)
DECLARE db_cursor CURSOR FOR
select table_schema+'.'+table_name
from information_schema.tables
where table_type='BASE TABLE'
OPEN db_cursor
FETCH NEXT FROM db_cursor INTO @tablename
WHILE @@FETCH_STATUS = 0
BEGIN
DECLARE @sqltableusage
TABLE(name varchar(250),rows int,reserved varchar(20),
data varchar(20), index_used varchar(20), unused varchar(20))
insert into @sqltableusage
Exec sp_spaceused @tablename
FETCH NEXT FROM db_cursor INTO @tablename
END
CLOSE db_cursor
DEALLOCATE db_cursor
--select * from @sqltableusage
select name,rows,replace(reserved,'KB','') Reserved_KB,replace(data,'KB','') Data_KB,
replace(index_used,'KB','') Index_Used_KB,replace(unused,'KB','') Unused_KB,
convert(decimal(14,2),(replace(reserved,'KB','')))/1024 as Reserved_MB,
convert(decimal(14,2),(replace(data,'KB','')))/1024 as Data_MB,
convert(decimal(14,2),(replace(index_used,'KB','')))/1024 as Index_Used_MB,
convert(decimal(14,2),(replace(unused,'KB','')))/1024 as Unused_MB
from @sqltableusage
Hope the above helps
Thanks
