Technical Article

Display Rows/Space Used by all user tables

,

This procedure will display the rows, reserved, data, index and unused space used in KB for all user tables in the database.  The idea is to call sp_spaceused repeatedly. 

Limitation: certain versions of sp_spaceused will truncate the table name to 20 characters.
Speed will depend on current activity level and locking in the database.  It will be nice if Microsoft will write sp_spaceused with the nolock option.  You can always create your own version of sp_spaceused with the necessary modifications 🙂

SET QUOTED_IDENTIFIER OFF 
GO
SET ANSI_NULLS ON 
GO

Create      PROCEDURE usp_UserTableSpace AS
Declare
 @cmd varchar(255),
 @name varchar(128)
 
Declare table_cursor Cursor for
select name from sysobjects with (nolock) where type = 'u'
for read only

set nocount on

create table #UserTableSpace(
namevarchar(100),
rowsint null,
reservedvarchar(20) null,
datavarchar(20) null,
indexpvarchar(20) null,
unusedvarchar(20) null
)

open table_cursor
fetch next from table_cursor into @name

while @@fetch_status = 0 
begin
set @cmd = "insert #UserTableSpace(name, rows, reserved, data, indexp, unused) exec('sp_spaceused " + @name + ", false')"
exec(@cmd)

if @@error <> 0 print "An error occurred during command execution.  Please contact the DBA."
fetch next from table_cursor into @name
end

close table_cursor
deallocate table_cursor

select
 name,
 rows,
 substring(reserved, 1, len(reserved)-3) as reserved,
 substring(data, 1, len(data)-3) as data,
 substring(indexp, 1, len(indexp)-3) as indexp,
 substring(unused, 1, len(unused)-3) as unused
from #UserTableSpace
order by name



GO
SET QUOTED_IDENTIFIER OFF 
GO
SET ANSI_NULLS ON 
GO

Rate

Share

Share

Rate