adminbd

Записи по mssql,oracle,windows и linux

Have a Question?

If you have any question you can ask below or enter what you are looking for!

Как узнать размер таблиц в БД sql. Узнать размер индексов в mssql.

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

 

 

Leave a Reply

Your email address will not be published. Required fields are marked *

You can use these HTML tags and attributes <a href="" title=""> <abbr title=""> <acronym title=""> <b> <blockquote cite=""> <cite> <code> <del datetime=""> <em> <i> <q cite=""> <s> <strike> <strong>