SET nocount ON
IF NOT object_id('tempdb..[#space]') IS NULL DROP TABLE [#space]
declare @TABLE [sysname]
CREATE TABLE [#space] (
[name] [nvarchar](128),
[rows] [char](11),
[rows_int] [int],
[reserved] [varchar](18),
[DATA] [varchar](18),
[data_int] [int],
[index_size] [varchar](18),
[unused] [varchar](18),
[unused_int] [int]
)
declare [c] cursor FOR
SELECT name
FROM sysobjects
WHERE type = N'U'
ORDER BY name
open [c]
while (1 = 1)
begin
fetch next FROM [c] INTO @TABLE
IF @@fetch_status = -1 break
IF @@fetch_status = -2 continue
INSERT INTO [#space] ([name], [rows], [reserved], [DATA], [index_size], [unused])
exec sp_spaceused @TABLE , N'TRUE'
end
close [c]
deallocate [c]
UPDATE [#space]
SET [data_int] = cast(REPLACE([DATA], N' KB', N'') AS [int]),
[unused_int] = cast(REPLACE([unused], N' KB', N'') AS [int]),
[rows_int] = cast([rows] AS [int])
SELECT * FROM [#space]
ORDER BY --rows_int desc
[data_int] DESC
SELECT cast(sum([unused_int]) AS varchar) + ' KB' AS [unused] FROM [#space]
Комментариев нет:
Отправить комментарий