Добро пожаловать в форум, Guest  >>   Войти | Регистрация | Поиск | Правила | В избранное | Подписаться
Все форумы / Microsoft SQL Server Новый топик    Ответить
 Как скриптом найти самые большие таблицы в БД (обязательно включая кучи)  [new]
ssms
Member

Откуда:
Сообщений: 124
Приветствую.

Подскажите пожалуйста, как скриптом найти самые большие таблицы в БД?

Обязательно включая кучи, т.е. где нет индекса в таблице.


Искал обычно по sysindexes, но в выборку не входят таблицы без индекса.
18 май 18, 10:32    [21419572]     Ответить | Цитировать Сообщить модератору
 Re: Как скриптом найти самые большие таблицы в БД (обязательно включая кучи)  [new]
Критик
Member

Откуда: Москва / Калуга
Сообщений: 33273
Блог
пкм на базе, Reports, отчет "Disk Usage By Top Tables"
18 май 18, 10:37    [21419589]     Ответить | Цитировать Сообщить модератору
 Re: Как скриптом найти самые большие таблицы в БД (обязательно включая кучи)  [new]
TaPaK
Member

Откуда: Kiev
Сообщений: 6794
ssms,

первый же скрипт в гугл
https://stackoverflow.com/questions/7892334/get-size-of-all-tables-in-database
18 май 18, 10:40    [21419600]     Ответить | Цитировать Сообщить модератору
 Re: Как скриптом найти самые большие таблицы в БД (обязательно включая кучи)  [new]
ssms
Member

Откуда:
Сообщений: 124
Критик
пкм на базе, Reports, отчет "Disk Usage By Top Tables"


репортсы не нужны.
18 май 18, 10:41    [21419611]     Ответить | Цитировать Сообщить модератору
 Re: Как скриптом найти самые большие таблицы в БД (обязательно включая кучи)  [new]
ssms
Member

Откуда:
Сообщений: 124
TaPaK
ssms,

первый же скрипт в гугл
https://stackoverflow.com/questions/7892334/get-size-of-all-tables-in-database



Немного переделал скрипт:

SELECT 
    t.NAME AS TableName,
    s.Name AS SchemaName,
    p.rows AS RowCounts,
    SUM(a.total_pages) * 8 AS TotalSpaceKB, 
    CAST(ROUND(((SUM(a.total_pages) * 8) / 1024.00), 2) AS NUMERIC(36, 2)) AS TotalSpaceMB,
    SUM(a.used_pages) * 8 AS UsedSpaceKB, 
    CAST(ROUND(((SUM(a.used_pages) * 8) / 1024.00), 2) AS NUMERIC(36, 2)) AS UsedSpaceMB, 
    (SUM(a.total_pages) - SUM(a.used_pages)) * 8 AS UnusedSpaceKB,
    CAST(ROUND(((SUM(a.total_pages) - SUM(a.used_pages)) * 8) / 1024.00, 2) AS NUMERIC(36, 2)) AS UnusedSpaceMB
FROM 
    sys.tables t
INNER JOIN      
    sys.indexes i ON t.OBJECT_ID = i.object_id
INNER JOIN 
    sys.partitions p ON i.object_id = p.OBJECT_ID AND i.index_id = p.index_id
INNER JOIN 
    sys.allocation_units a ON p.partition_id = a.container_id
LEFT OUTER JOIN 
    sys.schemas s ON t.schema_id = s.schema_id
WHERE 
    t.NAME NOT LIKE 'dt%' 
    AND t.is_ms_shipped = 0
    AND i.OBJECT_ID > 255 
GROUP BY 
    t.Name, s.Name, p.Rows
ORDER BY 
    SUM(a.total_pages) DESC



Спасибо, похоже то что нужно.
18 май 18, 10:44    [21419620]     Ответить | Цитировать Сообщить модератору
 Re: Как скриптом найти самые большие таблицы в БД (обязательно включая кучи)  [new]
TaPaK
Member

Откуда: Kiev
Сообщений: 6794
ssms,

ну и
автор
Искал обычно по sysindexes, но в выборку не входят таблицы без индекса.

false
18 май 18, 10:47    [21419626]     Ответить | Цитировать Сообщить модератору
 Re: Как скриптом найти самые большие таблицы в БД (обязательно включая кучи)  [new]
ssms
Member

Откуда:
Сообщений: 124
TaPaK
ssms,

ну и
автор
Искал обычно по sysindexes, но в выборку не входят таблицы без индекса.

false


reserved
dpages

Показывают разве?
18 май 18, 11:26    [21419730]     Ответить | Цитировать Сообщить модератору
 Re: Как скриптом найти самые большие таблицы в БД (обязательно включая кучи)  [new]
TaPaK
Member

Откуда: Kiev
Сообщений: 6794
ssms
TaPaK
ssms,

ну и
пропущено...

false


reserved
dpages

Показывают разве?


но в выборку не входят таблицы без индекса. так входят?
18 май 18, 11:37    [21419763]     Ответить | Цитировать Сообщить модератору
 Re: Как скриптом найти самые большие таблицы в БД (обязательно включая кучи)  [new]
ssms
Member

Откуда:
Сообщений: 124
TaPaK
ssms
пропущено...


reserved
dpages

Показывают разве?


но в выборку не входят таблицы без индекса. так входят?


Как надо точно не входят.
18 май 18, 11:42    [21419786]     Ответить | Цитировать Сообщить модератору
 Re: Как скриптом найти самые большие таблицы в БД (обязательно включая кучи)  [new]
Владислав Колосов
Member

Откуда:
Сообщений: 7407
ssms,

цикл по таблицам, sp_spaceused.
18 май 18, 12:02    [21419843]     Ответить | Цитировать Сообщить модератору
 Re: Как скриптом найти самые большие таблицы в БД (обязательно включая кучи)  [new]
alexeyvg
Member

Откуда: Moscow
Сообщений: 30825
Ещё вариант:
SELECT 
	schema_name(o.schema_id) + '.' +  o.name as [Table Name], 
	fg.name as [File Group],
	SUM (p.reserved_page_count) as [reserved_page_count],
	convert(decimal(13,2), SUM (p.reserved_page_count) * 8. / 1024 / 1024) as [reservedpages GB],
	SUM (p.used_page_count) as [used_page_count],
	convert(decimal(13,2), SUM (p.used_page_count) * 8. / 1024 / 1024) as [usedpages GB],
	SUM (
		CASE
			WHEN (p.index_id < 2) THEN (p.in_row_data_page_count + p.lob_used_page_count + p.row_overflow_used_page_count)
			ELSE p.lob_used_page_count + p.row_overflow_used_page_count
		END
		) as pages,
	SUM (
		CASE
			WHEN (p.index_id < 2) THEN p.row_count
			ELSE 0
		END
		) as [rowCount]
FROM sys.dm_db_partition_stats p (nolock)
	join sys.objects as o (nolock)
		on o.object_id = p.object_id
		and o.type = 'U'
	join sys.sysindexes i (nolock)
		on i.id = o.object_id
		and i.indid in (0,1)
	join sys.filegroups fg (nolock)
		on fg.data_space_id = i.groupid
group by o.name, o.schema_id, fg.name
order by [reservedpages GB] desc
Вроде должен всё показывать...
Он ещё по файлгруппам группирует, но это можно убрать
18 май 18, 13:41    [21420239]     Ответить | Цитировать Сообщить модератору
 Re: Как скриптом найти самые большие таблицы в БД (обязательно включая кучи)  [new]
Критик
Member

Откуда: Москва / Калуга
Сообщений: 33273
Блог
ssms
Критик
пкм на базе, Reports, отчет "Disk Usage By Top Tables"


репортсы не нужны.


Reports + Profiler
18 май 18, 14:45    [21420467]     Ответить | Цитировать Сообщить модератору
Все форумы / Microsoft SQL Server Ответить