DBCC UPDATEUSAGE (0);
CREATE TABLE #t(
[имя таблицы] varchar(255)
,[строк] varchar(255)
,[зарезервировано] varchar(255)
,[всего данных] varchar(255)
,[размер индексов] varchar(255)
,[свободно] varchar(255)
);
INSERT INTO #t
exec sp_msforeachtable N'exec sp_spaceused ''?''';
SELECT * FROM #t ORDER BY CONVERT(bigint, REPLACE([всего данных], ' KB', '')) DESC;
DROP TABLE #t;
DECLARE @TableInfo TABLE (
table_name sysname,
row_count int,
reserved_size_kb nvarchar(50),
data_size_kb nvarchar(50),
index_size_kb nvarchar(50),
unused_size_kb nvarchar(50)
)
INSERT INTO @TableInfo
EXEC sp_MSforeachtable 'sp_spaceused ''?'''
UPDATE @TableInfo
SET
data_size_kb = replace(data_size_kb, 'KB', ''),
reserved_size_kb = replace(reserved_size_kb, 'KB', ''),
index_size_kb = replace(index_size_kb, 'KB', ''),
unused_size_kb = replace(unused_size_kb, 'KB', '')
SELECT *,
reserved_size_kb/1024 AS reserved_size_mb,
data_size_kb/1024
…
Читать далее