Parent Table Name for sp_spaceused

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

Old Forums

CLOSED - General SQL Server

Parent Table Name for sp_spaceused

Author

Topic

pmc181
Starting Member

4 Posts

Posted - 2006-07-23 : 11:30:52

Can anybody help with this?
I need to modify the script below so that the result set shows the entire parent table name. Right now the script result set returns all the rows with the same table name as Changes_log, but I need the whole name for each table like P8905.Changes_log. I am using this script to see which changes_log tables need trimmed out to keep the db running better.

CREATE TABLE #TableSizes
(
table_name SYSNAME,
row_count int,
reserved_size varchar(10),
data_size varchar(10),
index_size varchar(10),
unused_size varchar(10)
)

INSERT #TableSizes
EXEC sp_MSforeachtable 'sp_spaceused ''?''', @whereand = "and name like '%changes_log%'"

SELECT * FROM #TableSizes ORDER BY table_name

Thanks!

Paul

nr
SQLTeam MVY

12543 Posts

Posted - 2006-07-23 : 12:29:36

The reason is that sp_spaceused doesn't return the table owner.
You can write your own sp_spaceused or replace sp_msforeachtable - I would do the latter - something like

create proc s_gettablesizes
@whereand varchar(1000)
as
set nocount on
CREATE TABLE #r
(
owner sysname,
table_name SYSNAME,
row_count int,
reserved_size varchar(10),
data_size varchar(10),
index_size varchar(10),
unused_size varchar(10)
)

CREATE TABLE #a
(
table_name SYSNAME,
row_count int,
reserved_size varchar(10),
data_size varchar(10),
index_size varchar(10),
unused_size varchar(10)
)

CREATE TABLE #b
(
id int identity (1,1) ,
owner sysname,
table_name SYSNAME,
)

declare @sql varchar(8000)
select @sql = 'select user_name(uid), name
from sysobjects where xtype = ''U''
' + @whereand

insert #b (owner,table_name)
exec (@sql)

declare @i int, @owner sysname, @table_name sysname, @name nvarchar(776)
select @i = 1
while @i < (select max(id) from #b)
begin
select @i = min(id) from #b where id > @i
select @owner = owner, @table_name = table_name, @name = owner +'.' + @table_name from #b where id = @i

delete #a
insert #a
exec sp_spaceused @name
insert #r select @owner, * from #a
end

select owner+ '.' + table_name table_name ,
row_count ,
reserved_size ,
data_size ,
index_size ,
unused_size
from #r
go

==========================================
Cursors are useful if you don't know sql.
DTS can be used in a similar way.
Beer is not cold and it isn't fizzy.