SET QUOTED_IDENTIFIER ON
GO
SET ANSI_NULLS ON
GO
create proc Show_HugeTables
(
@top varchar(3)
)
as begin
/*************************************************************************************************
Purpose: To list the size of all tables in the database in descending order (that is biggere tables first).
Output can be restricted to top n tables. That is you can pass the value 3 to @top parameter to
see the top 3 biggest tables in your database.
NOTE: Always create this procedure in the same database in which you want to check the table size.
Written by: Afroz Azam
Tested on: SQL Server 2000,2005
Date created: January-07-2010
Date modified:
Email: syedazamdxb@gmail.com
Examples:
To list all the user tables in the database along with their sizes:
EXEC Show_HugeTables
To see the top three biggest tables in your database:
EXEC Show_HugeTables '3'
*************************************************************************************************/
declare @table as nvarchar(500) ,
@str as nvarchar(500),
@str1 as nvarchar(1500)
select name, 'N' as stat into #temp from sysobjects where xtype = 'U'
create table #temp1
( name varchar(50),
rows bigint,
reserved varchar(100),
data varchar(100),
inex_size varchar(100),
unused varchar(100)
)
declare cur1 cursor local for
select name from #temp
open cur1
fetch next from cur1 into @table
while @@fetch_status =0
begin
set @str = 'sp_spaceused' + ' '+ @table
insert into #temp1
exec sp_executesql @str
fetch next from cur1 into @table
end
set @str1 ='select top '+ @top +' name,left(data,len(data)-2) as DATA_KB,left(data,len(data)-2)/1024 as DATA_MB ,'
+'left(data,len(data)-2)/1024/1024 as DATA_GB from #temp1 order by left(data,len(data)-2)/1024 desc'
exec sp_executesql @str1
end
GO
SET QUOTED_IDENTIFIER OFF
GO
SET ANSI_NULLS ON
GO
