HOW TO find the biggest tables in a database
acperkins has an answer:
Create Table #temp (
table_name sysname ,
row_count int,
reserved_size varchar(50),
data_size varchar(50),
index_size varchar(50),
unused_size varchar(50))
SET NOCOUNT ON
insert #temp exec sp_msforeachtable 'sp_spaceused ''?'''
select a.table_name,
a.row_count,
count(*) as col_count,
a.data_size
from #temp a
inner join information_schema.columns b on a.table_name = b.table_name
group by
a.table_name, a.row_count, a.data_size
Order by CAST(Replace(a.data_size, ' KB', '') as integer) desc
drop table #temp
sp_MSforeachtable is an undocumented stored procedure that can be used to perform the same actions for all tables in the database.
Create Table #temp (
table_name sysname ,
row_count int,
reserved_size varchar(50),
data_size varchar(50),
index_size varchar(50),
unused_size varchar(50))
SET NOCOUNT ON
insert #temp exec sp_msforeachtable 'sp_spaceused ''?'''
select a.table_name,
a.row_count,
count(*) as col_count,
a.data_size
from #temp a
inner join information_schema.columns b on a.table_name = b.table_name
group by
a.table_name, a.row_count, a.data_size
Order by CAST(Replace(a.data_size, ' KB', '') as integer) desc
drop table #temp
sp_MSforeachtable is an undocumented stored procedure that can be used to perform the same actions for all tables in the database.
Comments
Post a Comment