반응형
데이터베이스에 있는 모든 테이블의 크기를 가져옵니다.
상당히 큰 SQL Server 데이터베이스를 상속했습니다.데이터가 들어 있는 것으로 보아 생각보다 많은 공간을 차지하는 것 같습니다.
각 테이블이 디스크에서 얼마나 많은 공간을 소비하고 있는지 쉽게 확인할 수 있는 방법이 있습니까?
SELECT
t.NAME AS TableName,
s.Name AS SchemaName,
p.rows,
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
TotalSpaceMB DESC, t.Name
SQL Server Management Studio(SSMS)를 사용하는 경우 쿼리를 실행하는 대신(이 경우 중복 행을 반환) 표준 보고서를 실행할 수 있습니다.
- 데이터베이스를 오른쪽 클릭합니다.
- [ Reports ]> [ Standard Reports ]> [ Disk Usage By Table ]으로 이동합니다.
주의: 데이터베이스 호환성 수준을 90 이상으로 설정해야 올바르게 작동합니다.http://msdn.microsoft.com/en-gb/library/bb510680.aspx 를 참조해 주세요.
주의: 및 Azure SQL 데이터베이스 사용 시 이 보고서를 사용할 수 없습니다.
sp_space used는 테이블, 인덱스 뷰 또는 전체 데이터베이스에서 사용되는 디스크 공간에 대한 정보를 가져올 수 있습니다.
예를 들어 다음과 같습니다.
USE MyDatabase; GO
EXEC sp_spaceused N'User.ContactInfo'; GO
그러면 ContactInfo 테이블의 디스크 사용량 정보가 보고됩니다.
모든 테이블에 대해 동시에 사용하려면:
USE MyDatabase; GO
sp_msforeachtable 'EXEC sp_spaceused [?]' GO
또한 SQL 서버의 마우스 오른쪽 버튼 표준 리포트 기능 내에서 Disk 사용량을 가져올 수 있습니다.이 보고서를 보려면 개체 탐색기의 서버 개체에서 데이터베이스 개체로 이동한 후 데이터베이스를 마우스 오른쪽 버튼으로 누르십시오.표시되는 메뉴에서 Reports, Standard Reports, "Disk Usage by Partition: [DatabaseName]" 순으로 선택합니다.
다른 방법이 있습니다.오브젝트 탐색기에서 SQL Server Management Studio를 사용하여 데이터베이스로 이동하여 테이블을 선택합니다.
그런 다음 개체 탐색기 세부 정보를 엽니다(F7 키를 누르거나 View -> Object Explorer Details로 이동합니다).개체 탐색기 세부 정보 페이지에서 열 머리글을 마우스 오른쪽 버튼으로 클릭하고 페이지에 표시할 열을 사용하도록 설정합니다.원하는 열별로 데이터를 정렬할 수도 있습니다.
exec sp_spaceused N'dbo.MyTable'
모든 테이블에 대해 다음을 사용합니다.(폴의 코멘트에서 추가)
exec sp_MSForEachTable 'exec sp_spaceused [?]'
몇 번 검색한 결과, 모든 테이블에 대한 정보를 쉽게 얻을 수 있는 방법을 찾을 수 없었습니다.데이터베이스에서 사용된 모든 공간을 반환하는 sp_space라는 이름의 편리한 저장 프로시저가 있습니다.테이블 이름과 함께 제공된 경우 해당 테이블에서 사용된 공간을 반환합니다.그러나 저장 프로시저가 반환하는 결과는 열이 문자 값이기 때문에 정렬할 수 없습니다.
다음 스크립트는 원하는 정보를 생성합니다.
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
Azure의 경우 다음을 사용했습니다.
SSMS v17.x+가 필요합니다.
나는 사용했다;
User Sparrow가 언급한 바와 같이 다음과 같습니다.
, 펴보세요.Databases[ Tables ]를 선택합니다.
그런 다음 F7 키를 누릅니다.row count
다음과 같이 합니다.
SSMS는 Azure 데이터베이스에 연결되어 있습니다.
위의 쿼리는 테이블에서 사용되는 공간(색인 포함)을 찾는 데 유용하지만 테이블에서 인덱스가 사용하는 공간의 크기를 비교하려면 다음 쿼리를 사용합니다.
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
WHERE
i.is_primary_key = 0 -- fix for size discrepancy
GROUP BY
i.OBJECT_ID,
i.index_id,
i.name
ORDER BY
OBJECT_NAME(i.OBJECT_ID),
i.index_id
여러 파티션 및/또는 필터링된 인덱스로 작업할 때 Marc_s의 답변은 잘못된 결과를 제공합니다.또한 데이터와 인덱스의 크기를 구분하지 않으며, 이는 종종 매우 관련이 있습니다.제안된 몇 가지 수정이 핵심 문제를 해결하지 못하거나 단순히 잘못된 것일 수도 있습니다.
다음 쿼리는 이러한 모든 문제를 해결합니다.
SELECT
[object_id] = t.[object_id]
,[schema_name] = s.[name]
,[table_name] = t.[name]
,[index_name] = CASE WHEN i.[type] in (0,1,5) THEN null ELSE i.[name] END -- 0=Heap; 1=Clustered; 5=Clustered Columnstore
,[object_type] = CASE WHEN i.[type] in (0,1,5) THEN 'TABLE' ELSE 'INDEX' END
,[index_type] = i.[type_desc]
,[partition_count] = p.partition_count
,[row_count] = p.[rows]
,[data_compression] = CASE WHEN p.data_compression_cnt > 1 THEN 'Mixed'
ELSE ( SELECT DISTINCT p.data_compression_desc
FROM sys.partitions p
WHERE i.[object_id] = p.[object_id] AND i.index_id = p.index_id
)
END
,[total_space_MB] = cast(round(( au.total_pages * (8/1024.00)), 2) AS DECIMAL(36,2))
,[used_space_MB] = cast(round(( au.used_pages * (8/1024.00)), 2) AS DECIMAL(36,2))
,[unused_space_MB] = cast(round(((au.total_pages - au.used_pages) * (8/1024.00)), 2) AS DECIMAL(36,2))
FROM sys.schemas s
JOIN sys.tables t ON s.schema_id = t.schema_id
JOIN sys.indexes i ON t.object_id = i.object_id
JOIN (
SELECT [object_id], index_id, partition_count=count(*), [rows]=sum([rows]), data_compression_cnt=count(distinct [data_compression])
FROM sys.partitions
GROUP BY [object_id], [index_id]
) p ON i.[object_id] = p.[object_id] AND i.[index_id] = p.[index_id]
JOIN (
SELECT p.[object_id], p.[index_id], total_pages = sum(a.total_pages), used_pages = sum(a.used_pages), data_pages=sum(a.data_pages)
FROM sys.partitions p
JOIN sys.allocation_units a ON p.[partition_id] = a.[container_id]
GROUP BY p.[object_id], p.[index_id]
) au ON i.[object_id] = au.[object_id] AND i.[index_id] = au.[index_id]
WHERE t.is_ms_shipped = 0 -- Not a system table
MB 및 GB 단위로 크기를 가져오기 위해 테이블 파티션을 처리한 @xav 응답으로 확장됩니다.SQL Server 2008/2012에서 테스트 완료 (주문)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
SSMS의 'table properties - storage' 페이지에 있는 것과 동일한 수치를 계산할 필요가 있는 경우 SSMS에서와 같은 방법으로 계산해야 합니다(sql server 2005 이상에서 동작하며 LOB 필드가 있는 테이블에서도 정상적으로 동작합니다.「used_pages」는 정확한 인덱스 크기를 나타내기 위해 사용할 수 없기 때문입니다).
;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
테이블 파티셔닝을 사용하고 있으며, 중복된 레코드로 인해 상기의 쿼리에 문제가 있었습니다.
이것이 필요한 사용자는 "테이블별 Disk 사용량" 보고서를 생성할 때 SQL Server 2014에서 실행하는 쿼리를 아래에 볼 수 있습니다.이전 버전의 SQL Server에서도 작동한다고 생각합니다.
그것은 마법처럼 작용한다.
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
Marc_s의 답변과는 조금 다른 내용입니다.이 페이지를 자주 다시 표시하기 때문에 대부분의 행의 첫 번째 순서로 순서가 매겨져 있습니다.
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
-- 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
다음은 다음 단계를 수행하여 모든 테이블의 크기를 빠르게 가져오는 방법을 보여 줍니다.
지정된 T-SQL 명령을 작성하여 모든 데이터베이스 테이블을 나열합니다.
select 'exec sp_spaceused ' + TABLE_NAME from INFORMATION_SCHEMA.TABLES where TABLE_TYPE = 'BASE TABLE'데이터베이스 테이블 목록을 복사하여 새 쿼리 분석기 창에 복사합니다.
exec sp_spaceused table1 exec sp_spaceused table2 exec sp_spaceused table3 exec sp_spaceused table4 exec sp_spaceused table5SQL 쿼리 아나라이저에서 맨 위의 툴바옵션 Results to file ( Ctrl+ + )를 선택합니다.
마지막으로 위의 툴바에서 빨간색으로 표시된 Execute 버튼을 누릅니다.
이제 모든 테이블의 데이터베이스 크기가 컴퓨터의 파일에 저장됩니다.
1개의 데이터베이스로 모든 테이블사이즈를 가져오려면 다음 쿼리를 사용할 수 있습니다.
Exec sys.sp_MSforeachtable ' sp_spaceused "?" '
모든 결과를 임시 테이블에 삽입하도록 변경한 후 임시 테이블에서 선택할 수 있습니다.
Insert into #TempTable Exec sys.sp_MSforeachtable ' sp_spaceused "?" '
Select * from #TempTable
Greg Low 씨에게 이 질문을 해 주셔서 감사합니다.
SELECT o.name AS ObjectName,
SUM(reserved_page_count) * 8.0 / 1024 AS SizeinMB
FROM sys.dm_db_partition_stats AS ps
INNER JOIN sys.sysobjects AS o
ON ps.object_id = o.id
GROUP BY o.name
ORDER BY SizeinMB DESC;
그러면 각 테이블의 크기와 레코드 수가 표시됩니다.
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
OSQL을 사용한 명령어프롬프트:
OSQL -E -d <*databasename*> -Q "exec sp_msforeachtable 'sp_spaceused [?]'" > result.txt
테이블에는 더 많은 파티션 파일이 있으며 파일 순서를 표시해야 합니다.
SELECT
T1.Name AS TableName,
T5.Name AS SchemaName,
T3.partition_number AS PartionNumber,
T3.Rows AS RowsCount,
SUM(T4.total_pages) * 8 AS TotalSpaceKB,
SUM(T4.used_pages) * 8 AS UsedSpaceKB,
(SUM(T4.total_pages) - SUM(T4.used_pages)) * 8 AS UnusedSpaceKB
FROM
sys.objects T1 INNER JOIN
sys.indexes T2 ON T1.object_id = T2.object_id INNER JOIN
sys.partitions T3 ON T2.object_id = T3.object_id AND T2.index_id = T3.index_id INNER JOIN
sys.allocation_units T4 ON T3.partition_id = T4.container_id LEFT JOIN
sys.schemas T5 ON T1.schema_id = T5.schema_id
WHERE
T1.type='U'
GROUP BY
T1.Name, T5.Name, T3.Rows,T3.partition_number
ORDER BY
T1.Name,T3.partition_number;
marc_s 답변 위에 몇 개의 컬럼을 추가했습니다.
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
이 투고는 SQL Server 2000에만 관련된 것으로, 현재 환경에서 동작하는 것으로 테스트되고 있습니다.
이 코드는 단일 데이터베이스뿐만 아니라 단일 인스턴스의 가능한 모든 데이터베이스에 액세스합니다.
두 개의 임시 테이블을 사용하여 적절한 데이터를 수집한 다음 결과를 하나의 '라이브' 테이블에 덤프합니다.
반환되는 데이터는 DatabaseName, DatabaseTableName, Rows(테이블 내), data(테이블 크기(KB 단위), 엔트리 데이터(스크립트를 마지막으로 실행한 시기를 확인하는 데 도움이 됩니다)입니다.
이 코드의 단점은 '데이터' 필드가 int로 저장되지 않고('KB' 문자는 해당 필드에 저장됨), 정렬에 유용하다는 것입니다(전혀 필요하지 않습니다).
이 코드가 누군가를 도와 시간을 절약해주길 바란다!
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]
rsp_DatabaseTableSizes 테이블은 다음 방법으로 작성되었습니다.
CREATE TABLE [dbo].[rsp_DatabaseSizes](
[DatabaseName] [varchar](1000) NULL,
[dbSize] [decimal](15, 2) NULL,
[DateUpdated] [smalldatetime] NULL
) ON [PRIMARY]
GO
위의 @Mark answer에서 최신 크기 통계를 강제 적용하기 위해 @updateusage='true'를 추가했습니다(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
다음은 크기 내림차순으로 정렬된 1GB보다 큰 테이블을 가져오기 위한 쿼리 예제 쿼리입니다.
USE YourDB
GO
DECLARE @Mult float = 8
SET @Mult = @Mult / POWER(2, 20) -- Use POWER(2, 10) for MBs
; WITH CTE AS
(
SELECT
i.object_id,
Rows = MAX(p.rows),
TotalSpaceGB = ROUND(SUM(a.total_pages) * @Mult, 0),
UsedSpaceGB = ROUND(SUM(a.used_pages) * @Mult, 0)
FROM
sys.indexes i
JOIN
sys.partitions p ON i.object_id = p.object_id AND i.index_id = p.index_id
JOIN
sys.allocation_units a ON p.partition_id = a.container_id
WHERE
i.object_id > 255
GROUP BY
i.object_id
HAVING
SUM(a.total_pages) * @Mult > 1
)
SELECT
SchemaName = s.name,
TableName = t.name,
c.TotalSpaceGB,
c.UsedSpaceGB,
UnusedSpaceGB = c.TotalSpaceGB - c.UsedSpaceGB,
[RowCount] = c.Rows
FROM
CTE c
JOIN
sys.tables t ON t.object_id = c.object_id
JOIN
sys.schemas s ON t.schema_id = s.schema_id
ORDER BY
c.TotalSpaceGB DESC
marc_s의 응답(허용된 응답)에 대한 간단한 확장으로서 컬럼 수를 반환하고 필터링을 허용하도록 조정됩니다.
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
이 문의는 사용하기 쉽고 신속하다는 것을 알았습니다.
select schema_name(tab.schema_id) + '.' + tab.name as [table],
cast(sum(spc.used_pages * 8)/1024.00 as numeric(36, 2)) as used_mb,
cast(sum(spc.total_pages * 8)/1024.00 as numeric(36, 2)) as allocated_mb
from sys.tables (nolock) tab
inner join sys.indexes (nolock) ind
on tab.object_id = ind.object_id
inner join sys.partitions (nolock) part
on ind.object_id = part.object_id and ind.index_id = part.index_id
inner join sys.allocation_units (nolock) spc
on part.partition_id = spc.container_id
group by schema_name(tab.schema_id) + '.' + tab.name
order by sum(spc.used_pages) desc
데이터베이스의 빈 공간만 신경쓰고 개별 테이블은 신경 쓰지 않는 경우 다음을 고려할 수 있습니다.
ETL 상황처럼 데이터베이스에 대량의 데이터가 삽입되었다가 삭제되면 파일 그룹이 자동으로 증가하지만 자동으로 축소되지 않기 때문에 데이터베이스에서 사용되지 않는 공간이 너무 많아집니다.
데이터베이스의 [Properties]페이지를 사용하면, 이 경우에 해당하는지를 확인할 수 있습니다.축소(데이터베이스 우클릭)> [태스크(Tasks)]> [수축(Shrink)]를 클릭하여 공간을 할당할 수 있습니다.그러나 근본적인 이유가 아직 남아 있는 경우 데이터베이스는 다시 확장됩니다(또한 다시 확장하기 위해 추가 시간이 소요되며 충분히 확장될 때까지 속도가 느려집니다.그러므로 이 경우에는 실행하지 마십시오).
CREATE TABLE #tmp_table_info
(
id int identity(1,1),
tblname varchar(200)
);
CREATE TABLE #SpaceUsed
(
TableName sysname
,NumRows BIGINT
,ReservedSpace VARCHAR(50)
,DataSpace VARCHAR(50)
,IndexSize VARCHAR(50)
,UnusedSpace VARCHAR(50)
)
insert into #tmp_table_info
select s.name+'.'+t.name
from sys.tables t
inner join sys.schemas s on t.schema_id = s.schema_id
where t.type = 'U';
declare @min int =1,@max int = 0
select @max = count(*)
from #tmp_table_info
while(@min<=@max)
begin
declare @tablename varchar(200)
select @tablename=tblname
from #tmp_table_info
where id =@min
DECLARE @str VARCHAR(500)
SET @str = 'sp_spaceused '''+@tablename+''''
INSERT INTO #SpaceUsed
EXEC (@str)
set @min =@min + 1
end;
select @@SERVERNAME as servername,DB_NAME() as DatabaseName,CONVERT(numeric(18,0),REPLACE(ReservedSpace,' KB','')) / 1024 as ReservedSpace_MB,
CONVERT(numeric(18,0),REPLACE(DataSpace,' KB','')) / 1024 as DataSpace_MB,
CONVERT(numeric(18,0),REPLACE(IndexSize,' KB','')) / 1024 as IndexSpace_MB,
CONVERT(numeric(18,0),REPLACE(UnusedSpace,' KB','')) / 1024 as UnusedSpace_MB from #SpaceUsed
drop table #tmp_table_info
drop table #SpaceUsed
언급URL : https://stackoverflow.com/questions/7892334/get-size-of-all-tables-in-database
반응형
'source' 카테고리의 다른 글
| EMDERVAL을 사용하여 닫힌 Excel 워크북의 값을 참조하시겠습니까? (0) | 2023.04.12 |
|---|---|
| SQL Server 2008 열 이름 변경 (0) | 2023.04.07 |
| 테이블(아직 존재하지 않는 경우)에 열을 추가합니다. (0) | 2023.04.07 |
| datetime2 데이터 유형을 datetime 데이터 유형으로 변환하면 값이 범위를 벗어납니다. (0) | 2023.04.07 |
| 비프라이머리 키에 대한 외부 키 (0) | 2023.04.07 |