Pobierz rozmiar wszystkich tabel w bazie danych
Odziedziczyłem dość dużą bazę danych SQL Server. Wydaje się, że zajmuje więcej miejsca, niż bym się spodziewał, biorąc pod uwagę dane, które zawiera.
Czy istnieje łatwy sposób na określenie, ile miejsca na dysku zajmuje każda tabela?
20 answers
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
t.Name
Warning: date(): Invalid date.timezone value 'Europe/Kyiv', we selected the timezone 'UTC' for now. in /var/www/agent_stack/data/www/doraprojects.net/template/agent.layouts/content.php on line 54
2017-05-02 17:19:02
Jeśli używasz SQL Server Management Studio (SSMS), zamiast uruchamiać zapytanie ( które w moim przypadku zwróciło zduplikowane wiersze) możesz uruchomić standardowy raport.
- Kliknij prawym przyciskiem myszy na bazie danych
- przejdź do Raporty > standardowe raporty > użycie dysku Według Tabeli
Uwaga: poziom zgodności bazy danych musi być ustawiony na 90 lub wyższy, aby to działało poprawnie. Zobacz też http://msdn.microsoft.com/en-gb/library/bb510680.aspx
Warning: date(): Invalid date.timezone value 'Europe/Kyiv', we selected the timezone 'UTC' for now. in /var/www/agent_stack/data/www/doraprojects.net/template/agent.layouts/content.php on line 54
2016-08-02 18:57:53
Sp_spaceused może uzyskać informacje o przestrzeni dyskowej używanej przez tabelę, indeksowany widok lub całą bazę danych.
Na przykład:
USE MyDatabase; GO
EXEC sp_spaceused N'User.ContactInfo'; GO
Wyświetla informacje o użyciu dysku w tabeli ContactInfo.
Aby użyć tego dla wszystkich tabel jednocześnie:
USE MyDatabase; GO
sp_msforeachtable 'EXEC sp_spaceused [?]' GO
Możesz również uzyskać użycie dysku z poziomu standardowej funkcjonalności raportów SQL Server klikając prawym przyciskiem myszy. Aby przejść do tego raportu, przejdź z obiektu serwera w Object Explorer, przejdź do baz danych obiekt, a następnie kliknij prawym przyciskiem myszy dowolną bazę danych. Z menu, które się pojawi, wybierz Raporty, następnie Raporty standardowe, a następnie "użycie dysku przez partycję: [Nazwa bazy danych]".
Warning: date(): Invalid date.timezone value 'Europe/Kyiv', we selected the timezone 'UTC' for now. in /var/www/agent_stack/data/www/doraprojects.net/template/agent.layouts/content.php on line 54
2011-10-25 16:24:22
Po kilku poszukiwaniach nie mogłem znaleźć łatwego sposobu, aby uzyskać informacje na temat wszystkich tabel. Istnieje przydatna procedura składowana o nazwie sp_spaceused, która zwróci całą przestrzeń używaną przez bazę danych. Jeśli podano nazwę tabeli, zwraca ona przestrzeń używaną przez tę tabelę. Jednak wyniki zwracane przez procedurę składowaną nie są sortowalne, ponieważ kolumny są wartościami znakowymi.
Poniższy skrypt wygeneruje informacje, których szukam.
create table #TableSize (
Name varchar(255),
[rows] int,
reserved varchar(255),
data varchar(255),
index_size varchar(255),
unused varchar(255))
create table #ConvertedSizes (
Name varchar(255),
[rows] int,
reservedKb int,
dataKb int,
reservedIndexSize int,
reservedUnused int)
EXEC sp_MSforeachtable @command1="insert into #TableSize
EXEC sp_spaceused '?'"
insert into #ConvertedSizes (Name, [rows], reservedKb, dataKb, reservedIndexSize, reservedUnused)
select name, [rows],
SUBSTRING(reserved, 0, LEN(reserved)-2),
SUBSTRING(data, 0, LEN(data)-2),
SUBSTRING(index_size, 0, LEN(index_size)-2),
SUBSTRING(unused, 0, LEN(unused)-2)
from #TableSize
select * from #ConvertedSizes
order by reservedKb desc
drop table #TableSize
drop table #ConvertedSizes
Warning: date(): Invalid date.timezone value 'Europe/Kyiv', we selected the timezone 'UTC' for now. in /var/www/agent_stack/data/www/doraprojects.net/template/agent.layouts/content.php on line 54
2011-10-25 16:17:01
exec sp_spaceused N'dbo.MyTable'
Dla wszystkich tabel ,użyj..(dodanie z komentarzy Pawła)
exec sp_MSForEachTable 'exec sp_spaceused [?]'
Warning: date(): Invalid date.timezone value 'Europe/Kyiv', we selected the timezone 'UTC' for now. in /var/www/agent_stack/data/www/doraprojects.net/template/agent.layouts/content.php on line 54
2016-12-24 17:43:08
Oto inna metoda: używając SQL Server Management Studio, w Object Explorer , przejdź do bazy danych i wybierz tabele
Następnie otwórz Object Explorer Details (naciskając F7 lub przechodząc do View->Object Explorer Details). Na stronie szczegółów Eksploratora obiektów kliknij prawym przyciskiem myszy nagłówek kolumny i włącz kolumny, które chcesz zobaczyć na stronie. Możesz sortować dane według dowolnej kolumny też.
Warning: date(): Invalid date.timezone value 'Europe/Kyiv', we selected the timezone 'UTC' for now. in /var/www/agent_stack/data/www/doraprojects.net/template/agent.layouts/content.php on line 54
2018-01-18 17:11:31
Powyższe zapytania są dobre do znalezienia ilości miejsca używanego przez tabelę( indeksy włączone), ale jeśli chcesz porównać, ile miejsca jest używane przez indeksy w tabeli, użyj tego zapytania:
SELECT
OBJECT_NAME(i.OBJECT_ID) AS TableName,
i.name AS IndexName,
i.index_id AS IndexID,
8 * SUM(a.used_pages) AS 'Indexsize(KB)'
FROM
sys.indexes AS i JOIN
sys.partitions AS p ON p.OBJECT_ID = i.OBJECT_ID AND p.index_id = i.index_id JOIN
sys.allocation_units AS a ON a.container_id = p.partition_id
GROUP BY
i.OBJECT_ID,
i.index_id,
i.name
ORDER BY
OBJECT_NAME(i.OBJECT_ID),
i.index_id
Warning: date(): Invalid date.timezone value 'Europe/Kyiv', we selected the timezone 'UTC' for now. in /var/www/agent_stack/data/www/doraprojects.net/template/agent.layouts/content.php on line 54
2013-07-11 18:36:42
Jeśli chcesz obliczyć dokładnie te same liczby, które znajdują się na stronie "Właściwości tabeli - Przechowywanie" w SSMS, musisz policzyć je tą samą metodą, co w SSMS (działa dla sql server 2005 i nowszych ... a także działa poprawnie dla tabel z polami LOB - ponieważ samo zliczanie "used_pages"nie jest wystarczające, aby pokazać dokładny rozmiar indeksu):
;with cte as (
SELECT
t.name as TableName,
SUM (s.used_page_count) as used_pages_count,
SUM (CASE
WHEN (i.index_id < 2) THEN (in_row_data_page_count + lob_used_page_count + row_overflow_used_page_count)
ELSE lob_used_page_count + row_overflow_used_page_count
END) as pages
FROM sys.dm_db_partition_stats AS s
JOIN sys.tables AS t ON s.object_id = t.object_id
JOIN sys.indexes AS i ON i.[object_id] = t.[object_id] AND s.index_id = i.index_id
GROUP BY t.name
)
select
cte.TableName,
cast((cte.pages * 8.)/1024 as decimal(10,3)) as TableSizeInMB,
cast(((CASE WHEN cte.used_pages_count > cte.pages
THEN cte.used_pages_count - cte.pages
ELSE 0
END) * 8./1024) as decimal(10,3)) as IndexSizeInMB
from cte
order by 2 desc
Warning: date(): Invalid date.timezone value 'Europe/Kyiv', we selected the timezone 'UTC' for now. in /var/www/agent_stack/data/www/doraprojects.net/template/agent.layouts/content.php on line 54
2014-01-31 03:39:32
-- Show the size of all the tables in a database sort by data size descending
SET NOCOUNT ON
DECLARE @TableInfo TABLE (tablename varchar(255), rowcounts int, reserved varchar(255), DATA varchar(255), index_size varchar(255), unused varchar(255))
DECLARE @cmd1 varchar(500)
SET @cmd1 = 'exec sp_spaceused ''?'''
INSERT INTO @TableInfo (tablename,rowcounts,reserved,DATA,index_size,unused)
EXEC sp_msforeachtable @command1=@cmd1
SELECT * FROM @TableInfo ORDER BY Convert(int,Replace(DATA,' KB','')) DESC
Warning: date(): Invalid date.timezone value 'Europe/Kyiv', we selected the timezone 'UTC' for now. in /var/www/agent_stack/data/www/doraprojects.net/template/agent.layouts/content.php on line 54
2014-01-06 11:27:26
Używamy partycjonowania tabel i mieliśmy pewne problemy z kwerendami podanymi powyżej z powodu zduplikowanych rekordów.
Dla tych, którzy tego potrzebują, możesz znaleźć poniżej Zapytanie uruchamiane przez SQL Server 2014 podczas generowania raportu "użycie dysku według tabeli". Zakładam, że działa również z poprzednimi wersjami SQL Server.
To działa jak urok.SELECT
a2.name AS [tablename],
a1.rows as row_count,
(a1.reserved + ISNULL(a4.reserved,0))* 8 AS reserved,
a1.data * 8 AS data,
(CASE WHEN (a1.used + ISNULL(a4.used,0)) > a1.data THEN (a1.used + ISNULL(a4.used,0)) - a1.data ELSE 0 END) * 8 AS index_size,
(CASE WHEN (a1.reserved + ISNULL(a4.reserved,0)) > a1.used THEN (a1.reserved + ISNULL(a4.reserved,0)) - a1.used ELSE 0 END) * 8 AS unused
FROM
(SELECT
ps.object_id,
SUM (
CASE
WHEN (ps.index_id < 2) THEN row_count
ELSE 0
END
) AS [rows],
SUM (ps.reserved_page_count) AS reserved,
SUM (
CASE
WHEN (ps.index_id < 2) THEN (ps.in_row_data_page_count + ps.lob_used_page_count + ps.row_overflow_used_page_count)
ELSE (ps.lob_used_page_count + ps.row_overflow_used_page_count)
END
) AS data,
SUM (ps.used_page_count) AS used
FROM sys.dm_db_partition_stats ps
WHERE ps.object_id NOT IN (SELECT object_id FROM sys.tables WHERE is_memory_optimized = 1)
GROUP BY ps.object_id) AS a1
LEFT OUTER JOIN
(SELECT
it.parent_id,
SUM(ps.reserved_page_count) AS reserved,
SUM(ps.used_page_count) AS used
FROM sys.dm_db_partition_stats ps
INNER JOIN sys.internal_tables it ON (it.object_id = ps.object_id)
WHERE it.internal_type IN (202,204)
GROUP BY it.parent_id) AS a4 ON (a4.parent_id = a1.object_id)
INNER JOIN sys.all_objects a2 ON ( a1.object_id = a2.object_id )
INNER JOIN sys.schemas a3 ON (a2.schema_id = a3.schema_id)
WHERE a2.type <> N'S' and a2.type <> N'IT'
ORDER BY a3.name, a2.name
Warning: date(): Invalid date.timezone value 'Europe/Kyiv', we selected the timezone 'UTC' for now. in /var/www/agent_stack/data/www/doraprojects.net/template/agent.layouts/content.php on line 54
2016-04-14 01:08:00
Mała zmiana w odpowiedzi Mar_c, ponieważ tak często wracam na tę stronę, uporządkowana przez większość pierwszych wierszy:
SELECT
t.NAME AS TableName,
s.Name AS SchemaName,
p.rows AS RowCounts,
SUM(a.total_pages) * 8 AS TotalSpaceKB,
SUM(a.used_pages) * 8 AS UsedSpaceKB,
(SUM(a.total_pages) - SUM(a.used_pages)) * 8 AS UnusedSpaceKB
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
--p.rows DESC --Uncomment to order by amount rows instead of size in KB.
SUM(a.total_pages) DESC
Warning: date(): Invalid date.timezone value 'Europe/Kyiv', we selected the timezone 'UTC' for now. in /var/www/agent_stack/data/www/doraprojects.net/template/agent.layouts/content.php on line 54
2017-01-20 14:02:25
To da ci rozmiary i liczbę rekordów dla każdej tabeli.
set ANSI_NULLS ON
set QUOTED_IDENTIFIER ON
GO
-- Get a list of tables and their sizes on disk
ALTER PROCEDURE [dbo].[sp_Table_Sizes]
AS
BEGIN
-- SET NOCOUNT ON added to prevent extra result sets from
-- interfering with SELECT statements.
SET NOCOUNT ON;
DECLARE @table_name VARCHAR(500)
DECLARE @schema_name VARCHAR(500)
DECLARE @tab1 TABLE(
tablename VARCHAR (500) collate database_default
,schemaname VARCHAR(500) collate database_default
)
CREATE TABLE #temp_Table (
tablename sysname
,row_count INT
,reserved VARCHAR(50) collate database_default
,data VARCHAR(50) collate database_default
,index_size VARCHAR(50) collate database_default
,unused VARCHAR(50) collate database_default
)
INSERT INTO @tab1
SELECT Table_Name, Table_Schema
FROM information_schema.tables
WHERE TABLE_TYPE = 'BASE TABLE'
DECLARE c1 CURSOR FOR
SELECT Table_Schema + '.' + Table_Name
FROM information_schema.tables t1
WHERE TABLE_TYPE = 'BASE TABLE'
OPEN c1
FETCH NEXT FROM c1 INTO @table_name
WHILE @@FETCH_STATUS = 0
BEGIN
SET @table_name = REPLACE(@table_name, '[','');
SET @table_name = REPLACE(@table_name, ']','');
-- make sure the object exists before calling sp_spacedused
IF EXISTS(SELECT id FROM sysobjects WHERE id = OBJECT_ID(@table_name))
BEGIN
INSERT INTO #temp_Table EXEC sp_spaceused @table_name, false;
END
FETCH NEXT FROM c1 INTO @table_name
END
CLOSE c1
DEALLOCATE c1
SELECT t1.*
,t2.schemaname
FROM #temp_Table t1
INNER JOIN @tab1 t2 ON (t1.tablename = t2.tablename )
ORDER BY schemaname,t1.tablename;
DROP TABLE #temp_Table
END
Warning: date(): Invalid date.timezone value 'Europe/Kyiv', we selected the timezone 'UTC' for now. in /var/www/agent_stack/data/www/doraprojects.net/template/agent.layouts/content.php on line 54
2011-10-25 16:18:40
Aby uzyskać wszystkie wielkości tabeli w jednej bazie danych, możesz użyć tego zapytania:
Exec sys.sp_MSforeachtable ' sp_spaceused "?" '
I możesz go zmienić, aby wstawić cały wynik do tabeli tymczasowej, a następnie wybrać z tabeli tymczasowej.
Insert into #TempTable Exec sys.sp_MSforeachtable ' sp_spaceused "?" '
Select * from #TempTable
Warning: date(): Invalid date.timezone value 'Europe/Kyiv', we selected the timezone 'UTC' for now. in /var/www/agent_stack/data/www/doraprojects.net/template/agent.layouts/content.php on line 54
2015-04-25 06:14:59
Dodałem jeszcze kilka kolumn na górze marc_s odpowiedź:
with fs
as
(
select i.object_id,
p.rows AS RowCounts,
SUM(a.total_pages) * 8 AS TotalSpaceKb
from sys.indexes i 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
WHERE
i.OBJECT_ID > 255
GROUP BY
i.object_id,
p.rows
)
SELECT
t.NAME AS TableName,
fs.RowCounts,
fs.TotalSpaceKb,
t.create_date,
t.modify_date,
( select COUNT(1)
from sys.columns c
where c.object_id = t.object_id ) TotalColumns
FROM
sys.tables t INNER JOIN
fs ON t.OBJECT_ID = fs.object_id
WHERE
t.NAME NOT LIKE 'dt%'
AND t.is_ms_shipped = 0
ORDER BY
t.Name
Warning: date(): Invalid date.timezone value 'Europe/Kyiv', we selected the timezone 'UTC' for now. in /var/www/agent_stack/data/www/doraprojects.net/template/agent.layouts/content.php on line 54
2013-09-07 00:18:12
Oto sposób na szybkie uzyskanie wszystkich rozmiarów tabel za pomocą następujących kroków:
-
Napisz podane polecenia T-SQL aby wyświetlić listę wszystkich tabel bazy danych:
select 'exec sp_spaceused ' + TABLE_NAME from INFORMATION_SCHEMA.TABLES where TABLE_TYPE = 'BASE TABLE'
-
Teraz skopiuj listę tabel bazy danych i skopiuj ją do nowego okna analizatora zapytań
exec sp_spaceused table1 exec sp_spaceused table2 exec sp_spaceused table3 exec sp_spaceused table4 exec sp_spaceused table5
W SQL query analyzer Wybierz z górnego paska narzędzi opcję wyniki do pliku (Ctrl + Shift + F ).
Teraz wreszcie trafiony przycisk Execute oznaczony na Czerwono z powyższego paska narzędzi .
-
Baza danych Rozmiar wszystkich tabel jest teraz przechowywana w pliku na twoim komputerze.
Warning: date(): Invalid date.timezone value 'Europe/Kyiv', we selected the timezone 'UTC' for now. in /var/www/agent_stack/data/www/doraprojects.net/template/agent.layouts/content.php on line 54
2016-12-27 22:55:11
Rozszerzenie @xav odpowiedź które obsługiwało partycje tabel, aby uzyskać rozmiar w MB i GB.
Testowane na SQL Server 2008/2012 (skomentował linię gdzie is_memory_optimized = 1
)
SELECT
a2.name AS TableName,
a1.rows as [RowCount],
--(a1.reserved + ISNULL(a4.reserved,0)) * 8 AS ReservedSize_KB,
--a1.data * 8 AS DataSize_KB,
--(CASE WHEN (a1.used + ISNULL(a4.used,0)) > a1.data THEN (a1.used + ISNULL(a4.used,0)) - a1.data ELSE 0 END) * 8 AS IndexSize_KB,
--(CASE WHEN (a1.reserved + ISNULL(a4.reserved,0)) > a1.used THEN (a1.reserved + ISNULL(a4.reserved,0)) - a1.used ELSE 0 END) * 8 AS UnusedSize_KB,
CAST(ROUND(((a1.reserved + ISNULL(a4.reserved,0)) * 8) / 1024.00, 2) AS NUMERIC(36, 2)) AS ReservedSize_MB,
CAST(ROUND(a1.data * 8 / 1024.00, 2) AS NUMERIC(36, 2)) AS DataSize_MB,
CAST(ROUND((CASE WHEN (a1.used + ISNULL(a4.used,0)) > a1.data THEN (a1.used + ISNULL(a4.used,0)) - a1.data ELSE 0 END) * 8 / 1024.00, 2) AS NUMERIC(36, 2)) AS IndexSize_MB,
CAST(ROUND((CASE WHEN (a1.reserved + ISNULL(a4.reserved,0)) > a1.used THEN (a1.reserved + ISNULL(a4.reserved,0)) - a1.used ELSE 0 END) * 8 / 1024.00, 2) AS NUMERIC(36, 2)) AS UnusedSize_MB,
--'| |' Separator_MB_GB,
CAST(ROUND(((a1.reserved + ISNULL(a4.reserved,0)) * 8) / 1024.00 / 1024.00, 2) AS NUMERIC(36, 2)) AS ReservedSize_GB,
CAST(ROUND(a1.data * 8 / 1024.00 / 1024.00, 2) AS NUMERIC(36, 2)) AS DataSize_GB,
CAST(ROUND((CASE WHEN (a1.used + ISNULL(a4.used,0)) > a1.data THEN (a1.used + ISNULL(a4.used,0)) - a1.data ELSE 0 END) * 8 / 1024.00 / 1024.00, 2) AS NUMERIC(36, 2)) AS IndexSize_GB,
CAST(ROUND((CASE WHEN (a1.reserved + ISNULL(a4.reserved,0)) > a1.used THEN (a1.reserved + ISNULL(a4.reserved,0)) - a1.used ELSE 0 END) * 8 / 1024.00 / 1024.00, 2) AS NUMERIC(36, 2)) AS UnusedSize_GB
FROM
(SELECT
ps.object_id,
SUM (CASE WHEN (ps.index_id < 2) THEN row_count ELSE 0 END) AS [rows],
SUM (ps.reserved_page_count) AS reserved,
SUM (CASE
WHEN (ps.index_id < 2) THEN (ps.in_row_data_page_count + ps.lob_used_page_count + ps.row_overflow_used_page_count)
ELSE (ps.lob_used_page_count + ps.row_overflow_used_page_count)
END
) AS data,
SUM (ps.used_page_count) AS used
FROM sys.dm_db_partition_stats ps
--===Remove the following comment for SQL Server 2014+
--WHERE ps.object_id NOT IN (SELECT object_id FROM sys.tables WHERE is_memory_optimized = 1)
GROUP BY ps.object_id) AS a1
LEFT OUTER JOIN
(SELECT
it.parent_id,
SUM(ps.reserved_page_count) AS reserved,
SUM(ps.used_page_count) AS used
FROM sys.dm_db_partition_stats ps
INNER JOIN sys.internal_tables it ON (it.object_id = ps.object_id)
WHERE it.internal_type IN (202,204)
GROUP BY it.parent_id) AS a4 ON (a4.parent_id = a1.object_id)
INNER JOIN sys.all_objects a2 ON ( a1.object_id = a2.object_id )
INNER JOIN sys.schemas a3 ON (a2.schema_id = a3.schema_id)
WHERE a2.type <> N'S' and a2.type <> N'IT'
--AND a2.name = 'MyTable' --Filter for specific table
--ORDER BY a3.name, a2.name
ORDER BY ReservedSize_MB DESC
Warning: date(): Invalid date.timezone value 'Europe/Kyiv', we selected the timezone 'UTC' for now. in /var/www/agent_stack/data/www/doraprojects.net/template/agent.layouts/content.php on line 54
2018-04-24 10:26:38
Mój post jest istotny tylko dla SQL Server 2000 i został przetestowany pod kątem pracy w moim środowisku.
Ten kod uzyskuje dostęp do wszystkich możliwych baz danych pojedynczej instancji, a nie tylko pojedynczej bazy danych.
Używam dwóch tabel temp, aby pomóc zebrać odpowiednie dane, a następnie wrzucić wyniki do jednej tabeli "Live".
Zwracane dane to: DatabaseName, DatabaseTableName, Rows( w tabeli), data (rozmiar tabeli w KB wydaje mi się), entry data (uważam to za przydatne dla wiedząc, kiedy ostatni raz uruchomiłem skrypt).
Spadkiem tego kodu jest to, że pole "data" nie jest przechowywane jako int( znaki " KB " są przechowywane w tym polu), a to byłoby przydatne (ale nie do końca konieczne)do sortowania.
Mam nadzieję, że ten kod pomoże komuś tam i zaoszczędzi mu trochę czasu!
CREATE PROCEDURE [dbo].[usp_getAllDBTableSizes]
AS
BEGIN
SET NOCOUNT OFF
CREATE TABLE #DatabaseTables([dbname] sysname,TableName sysname)
CREATE TABLE #AllDatabaseTableSizes(Name sysname,[rows] VARCHAR(18), reserved VARCHAR(18), data VARCHAR(18), index_size VARCHAR(18), unused VARCHAR(18))
DECLARE @SQL nvarchar(4000)
SET @SQL='select ''?'' AS [Database], Table_Name from [?].information_schema.tables WHERE TABLE_TYPE = ''BASE TABLE'' '
INSERT INTO #DatabaseTables(DbName, TableName)
EXECUTE sp_msforeachdb @Command1=@SQL
DECLARE AllDatabaseTables CURSOR LOCAL READ_ONLY FOR
SELECT TableName FROM #DatabaseTables
DECLARE AllDatabaseNames CURSOR LOCAL READ_ONLY FOR
SELECT DBName FROM #DatabaseTables
DECLARE @DBName sysname
OPEN AllDatabaseNames
DECLARE @TName sysname
OPEN AllDatabaseTables
WHILE 1=1 BEGIN
FETCH NEXT FROM AllDatabaseNames INTO @DBName
FETCH NEXT FROM AllDatabaseTables INTO @TName
IF @@FETCH_STATUS<>0 BREAK
INSERT INTO #AllDatabaseTableSizes
EXEC ( 'EXEC ' + @DBName + '.dbo.sp_spaceused ' + @TName)
END
--http://msdn.microsoft.com/en-us/library/aa175920(v=sql.80).aspx
INSERT INTO rsp_DatabaseTableSizes (DatabaseName, name, [rows], data)
SELECT [dbname], name, [rows], data FROM #DatabaseTables
INNER JOIN #AllDatabaseTableSizes
ON #DatabaseTables.TableName = #AllDatabaseTableSizes.Name
GROUP BY [dbname] , name, [rows], data
ORDER BY [dbname]
--To be honest, I have no idea what exact duplicates we are dropping
-- but in my case a near enough approach has been good enough.
DELETE FROM [rsp_DatabaseTableSizes]
WHERE name IN
(
SELECT name
FROM [rsp_DatabaseTableSizes]
GROUP BY name
HAVING COUNT(*) > 1
)
DROP TABLE #DatabaseTables
DROP TABLE #AllDatabaseTableSizes
CLOSE AllDatabaseTables
DEALLOCATE AllDatabaseTables
CLOSE AllDatabaseNames
DEALLOCATE AllDatabaseNames
END
--EXEC [dbo].[usp_getAllDBTableSizes]
W razie potrzeby, Tabela rsp_databasetablesizes została utworzona poprzez:
CREATE TABLE [dbo].[rsp_DatabaseSizes](
[DatabaseName] [varchar](1000) NULL,
[dbSize] [decimal](15, 2) NULL,
[DateUpdated] [smalldatetime] NULL
) ON [PRIMARY]
GO
Warning: date(): Invalid date.timezone value 'Europe/Kyiv', we selected the timezone 'UTC' for now. in /var/www/agent_stack/data/www/doraprojects.net/template/agent.layouts/content.php on line 54
2013-07-11 19:06:51
Z wiersza polecenia używając OSQL :
OSQL -E -d <*databasename*> -Q "exec sp_msforeachtable 'sp_spaceused [?]'" > result.txt
Warning: date(): Invalid date.timezone value 'Europe/Kyiv', we selected the timezone 'UTC' for now. in /var/www/agent_stack/data/www/doraprojects.net/template/agent.layouts/content.php on line 54
2016-05-27 15:32:16
Jako proste rozszerzenie odpowiedzi marc_s (tej, która została zaakceptowana), jest to dostosowane do zwracania liczby kolumn i pozwala na filtrowanie:
SELECT *
FROM
(
SELECT
t.NAME AS TableName,
s.Name AS SchemaName,
p.rows AS RowCounts,
COUNT(DISTINCT c.COLUMN_NAME) as ColumnCount,
SUM(a.total_pages) * 8 AS TotalSpaceKB,
(SUM(a.used_pages) * 8) AS UsedSpaceKB,
(SUM(a.total_pages) - SUM(a.used_pages)) * 8 AS UnusedSpaceKB
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
INNER JOIN
INFORMATION_SCHEMA.COLUMNS c ON t.NAME = c.TABLE_NAME
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
) AS Result
WHERE
RowCounts > 1000
AND ColumnCount > 10
ORDER BY
UsedSpaceKB DESC
Warning: date(): Invalid date.timezone value 'Europe/Kyiv', we selected the timezone 'UTC' for now. in /var/www/agent_stack/data/www/doraprojects.net/template/agent.layouts/content.php on line 54
2016-10-10 17:26:36
W odpowiedzi @Mark powyżej dodano @updateusage= 'true', aby wymusić najnowsze statystyki rozmiaru ( https://msdn.microsoft.com/en-us/library/ms188776.aspx):
SET NOCOUNT ON
DECLARE @TableInfo TABLE (tablename varchar(255), rowcounts int, reserved varchar(255), DATA varchar(255), index_size varchar(255), unused varchar(255))
DECLARE @cmd1 varchar(500)
SET @cmd1 = 'exec sp_spaceused @objname =''?'', @updateusage =''true'' '
INSERT INTO @TableInfo (tablename,rowcounts,reserved,DATA,index_size,unused)
EXEC sp_msforeachtable @command1=@cmd1
SELECT * FROM @TableInfo ORDER BY Convert(int,Replace(DATA,' KB','')) DESC
Warning: date(): Invalid date.timezone value 'Europe/Kyiv', we selected the timezone 'UTC' for now. in /var/www/agent_stack/data/www/doraprojects.net/template/agent.layouts/content.php on line 54
2015-10-08 16:22:50