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(
	name		varchar(100),
	rows		int null,
	reserved	varchar(20) null,
	data		varchar(20) null,
	indexp		varchar(20) null,
	unused		varchar(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