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 
