Skip to content

Instantly share code, notes, and snippets.

@mikaelnet
Created April 4, 2016 09:41
Show Gist options
  • Save mikaelnet/a7b77542bab09f8fc409d0b31b220205 to your computer and use it in GitHub Desktop.
Save mikaelnet/a7b77542bab09f8fc409d0b31b220205 to your computer and use it in GitHub Desktop.
List the size of all tables in database on SQL Server
select 'Database Name: ', db_name()
set nocount on
if exists(select name from tempdb..sysobjects where name='##tmp') drop table ##tmp
create table ##tmp(nam varchar(50), rows int, res varchar(15),data varchar(15),ind_sze varchar(15),unsed varchar(15))
go
declare @tblname varchar(50)
declare tblname CURSOR for select name from sysobjects where xtype='U'
open tblname
Fetch next from tblname into @tblname
WHILE @@FETCH_STATUS = 0
BEGIN
insert into ##tmp
exec sp_spaceused @tblname
FETCH NEXT FROM tblname INTO @tblname
END
CLOSE tblname
deallocate tblname
go
select nam Table_Name,rows Total_Rows,res Total_Table_Size,data Data_size,ind_sze Index_Size,unsed Unused_Space from ##tmp order by len(res) desc, res desc
drop table ##tmp
Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment