суббота, 17 января 2015 г.

MS SQL - скрипт вывода размеров таблиц базы



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]

Комментариев нет:

Отправить комментарий