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 AS data_size_mb,
index_size_kb/1024 AS index_size_mb,
unused_size_kb/1024 AS unused_size_mb
FROM @TableInfo
ORDER BY convert(int, data_size_kb) DESC
Результат со схемой
/************************************************************
* * k.moskvichev ©
* Time: 17.08.2018 14:15:15
************************************************************/
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' ,'')
DECLARE @result VARCHAR(8000);
SELECT
s.name + '.' + q.table_name AS NAME
,q.reserved_size_kb
,q.data_size_kb
,q.index_size_kb
,q.unused_size_kb
,q.reserved_size_kb / 1024 / 1024 AS reserved_size_gb
,q.data_size_kb / 1024 AS data_size_mb
,q.index_size_kb / 1024 AS index_size_mb
,q.unused_size_kb / 1024 AS unused_size_mb
FROM
@TableInfo q
INNER JOIN
sys.tables t
ON q.table_name = t.name
LEFT OUTER JOIN
sys.schemas s
ON t.schema_id = s.schema_id
ORDER BY
CONVERT(INT ,index_size_kb) DESC
--convert(int, data_size_kb) DESC