vrijdag, november 24, 2006

Space used - 2000

-- DBCC UPDATEUSAGE (0) WITH NO_INFOMSGS;

declare AllTables cursor fast_forward for
select name from sysobjects where type='u'

open AllTables

declare @TableName varchar(255)
create table #SpaceUsed
(
TableName varchar(255),
NbrRows varchar(255),
Reserved varchar(255),
Data varchar(255),
Index_Size varchar(255),
[Free] varchar(255)
)

fetch from AllTables into @TableName
while @@FETCH_STATUS=0
begin
insert into #SpaceUsed
exec sp_spaceused @TableName
fetch next from AllTables into @TableName
end

close AllTables
deallocate AllTables

select * from #SpaceUsed order by TableName

drop table #SpaceUsed

2 Comments:

At 12/05/2006 4:54 p.m., Blogger killspid said...

Funny. The original query didn't work in SQL 2000 because of the combination of an INSERT into a table variable from an EXECUTE statement.
Corrected now by using a temporary table.

 
At 12/09/2006 11:18 a.m., Blogger WesleyB said...

It's the little differences that makes it great :-) Would the Upgrade Advisor detect this?

 

Een reactie posten

<< Home