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.
| Author |
Topic |
|
TimS
Posting Yak Master
198 Posts |
 Posted - 2005-05-06 : 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