|
TimS
Posting Yak Master
USA
198 Posts |
 Posted - 05/06/2005 : 19:06:38
|
--
-- Create a script to cut & paste to new QA window and it shows space
-- used using the sp_spaceused proc.
-- Note not using indexes with status of 8 because it error out
-- reason for error unknown
-- Tim S
--
SET NOCOUNT ON;
PRINT '
create table #my_space
(
name nvarchar(128),
rows int null,
reserved nvarchar(128),
data nvarchar(128),
index_size nvarchar(128),
unused nvarchar(128)
);
';
PRINT 'SET NOCOUNT ON;';
SELECT 'INSERT INTO #my_space(name, rows, reserved, data, index_size, unused) ' +
'EXEC sp_spaceused @objname = ' +
QUOTENAME(QUOTENAME(USER_NAME(uid)) + '.' +
QUOTENAME(name), '''') -- + ', @updateusage = ''true'';'
FROM sysobjects obj
where xtype in ('S','U') AND EXISTS (
SELECT * FROM sysindexes ind
WHERE obj.id = ind.id AND ind.indid < 2 AND ind.status <> 8
);
PRINT '
select name, rows, reserved, data, index_size, unused
from #my_space
ORDER BY CAST(REPLACE(data, ''KB'', '''') AS INT) DESC;
'; |
|
Script to show space used
