----------------Analyze-------
create table #t
(
name nvarchar(128),
rows varchar(50),
reserved varchar(50),
data varchar(50),
index_size varchar(50),
unused varchar(50)
)
declare @id nvarchar(128)
declare c cursor for
select name from sysobjects where xtype='U'
open c
fetch c into @id
while @@fetch_status = 0 begin
insert into #t
exec sp_spaceused @id
fetch c into @id
end
close c
deallocate c
select * from #t
order by convert(int, substring(data, 1, len(data)-3)) desc
drop table #t
--------------------------------------------------------------------------------------------------------------------------
---- Finding Page count for Each table separately -----------------------------------------------------------
select
index_id, partition_number, alloc_unit_type_desc
,index_level, page_count,
avg_page_space_used_in_percent
from
sys.dm_db_index_physical_stats
(
db_id() /*Database */
,object_id(N'dbo.TableName') /* Table (Object_ID) */
,1 /* Index ID */
,null /* Partition ID – NULL – all partitions */
,'detailed' /* Mode */
)
--------------------------------------------------------------------------------------------------------------------------
--------------------------ReOrganize the same table-------------
ALTER INDEX ALL ON dbo.TableName REORGANIZE
--------------------------------------------------------------------------------------------------------------------------
