데이터베이스의 모든 테이블 크기 가져오기

질문자 :Eric

상당히 큰 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

marc_s

SSMS(SQL Server Management Studio )를 사용하는 경우 쿼리( 제 경우에는 중복 행 반환 )를 실행하는 대신 표준 보고서를 실행할 수 있습니다.

1. 데이터베이스를 마우스 오른쪽 버튼으로 클릭
2. 보고서 > 표준 보고서 > 테이블별 디스크 사용량으로 이동합니다.

참고: 이 기능이 제대로 작동하려면 데이터베이스 호환성 수준을 90 이상으로 설정해야 합니다. http://msdn.microsoft.com/en-gb/library/bb510680.aspx 참조

Kevin Brydon

sp_spaceused는 테이블, 인덱싱된 뷰 또는 전체 데이터베이스에서 사용하는 디스크 공간에 대한 정보를 얻을 수 있습니다.

예를 들어:

 USE MyDatabase; GO EXEC sp_spaceused N'User.ContactInfo'; GO

ContactInfo 테이블에 대한 디스크 사용량 정보를 보고합니다.

한 번에 모든 테이블에 사용하려면 다음을 수행합니다.

 USE MyDatabase; GO sp_msforeachtable 'EXEC sp_spaceused [?]' GO

SQL Server의 마우스 오른쪽 버튼 클릭 표준 보고서 기능 내에서 디스크 사용량을 확인할 수도 있습니다. 이 보고서를 보려면 개체 탐색기의 서버 개체에서 탐색하고 데이터베이스 개체로 이동한 다음 데이터베이스를 마우스 오른쪽 단추로 클릭합니다. 표시되는 메뉴에서 보고서, 표준 보고서, "파티션별 디스크 사용량: [DatabaseName]"을 차례로 선택합니다.

Axle

다른 방법은 다음과 같습니다. SQL Server Management Studio를 사용하여 개체 탐색기 에서 데이터베이스로 이동하여 테이블을 선택합니다.

그런 다음 개체 탐색기 세부 정보를 엽니다(F7 키 를 누르거나 보기->개체 탐색기 세부 정보로 이동 ). 개체 탐색기 세부 정보 페이지에서 열 머리글을 마우스 오른쪽 버튼으로 클릭하고 페이지에서 보고 싶은 열을 활성화합니다. 모든 열을 기준으로 데이터를 정렬할 수도 있습니다.

Sparrow

 exec sp_spaceused N'dbo.MyTable'

모든 테이블에 대해 ..를 사용하십시오.(Paul의 의견에서 추가)

 exec sp_MSForEachTable 'exec sp_spaceused [?]'

Royi Namir

몇 가지 검색 후에 모든 테이블에 대한 정보를 쉽게 얻을 수 있는 방법을 찾지 못했습니다. 데이터베이스에서 사용하는 모든 공간을 반환하는 sp_spaceused라는 편리한 저장 프로시저가 있습니다. 테이블 이름이 제공되면 해당 테이블이 사용하는 공간을 반환합니다. 그러나 열이 문자 값이기 때문에 저장 프로시저에서 반환된 결과는 정렬할 수 없습니다.

다음 스크립트는 내가 찾고 있는 정보를 생성합니다.

 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

Eric

위의 쿼리는 테이블(인덱스 포함)이 사용하는 공간의 양을 찾는 데 유용하지만 테이블의 인덱스가 사용하는 공간을 비교하려면 다음 쿼리를 사용하십시오.

 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

Jens Frandsen

Azure의 경우 다음을 사용했습니다.

SSMS v17.x+가 있어야 합니다.

나는 사용했다;

이를 통해 User Sparrow가 다음과 같이 언급했습니다 .

Databases 열고 테이블을 선택하십시오.
그런 다음 키 F7을 row count 가 표시되어야 합니다.
같이:

여기에서 SSMS는 Azure 데이터베이스에 연결됩니다.

Irf

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

Santhoshkumar KB

SSMS의 '테이블 속성 - 저장소' 페이지에 있는 정확히 동일한 숫자를 계산해야 하는 경우 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

sqladmin

우리는 테이블 파티셔닝을 사용하며 중복 레코드로 인해 위에 제공된 쿼리에 약간의 문제가 있었습니다.

이것이 필요한 사람들을 위해 "테이블별 디스크 사용량" 보고서를 생성할 때 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

xav

현재 승인된 답변은 2600개 이상의 찬성표를 가지고 있지만 여러 파티션 및/또는 필터링된 인덱스로 작업할 때 잘못된 결과를 제공합니다. 또한 데이터와 인덱스의 크기를 구분하지 않으며, 이는 종종 매우 관련이 있습니다. 몇 가지 제안된 수정 사항은 핵심 문제를 해결하지 못하거나 단순히 잘못되었습니다.

다음 쿼리는 이러한 모든 문제를 해결합니다.

 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

Blade

-- 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

Mark

Mar_c의 답변 에 대한 약간의 변경은 이 페이지를 너무 자주 방문했기 때문에 대부분의 행의 첫 번째 순서대로 정렬되었습니다.

 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

Joel Harkes

이렇게 하면 각 테이블의 크기와 레코드 수를 알 수 있습니다.

 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

William Walseth

하나의 데이터베이스에서 모든 테이블 크기를 얻으려면 다음 쿼리를 사용할 수 있습니다.

 Exec sys.sp_MSforeachtable ' sp_spaceused "?" '

그리고 모든 결과를 임시 테이블에 삽입하고 임시 테이블에서 선택하도록 변경할 수 있습니다.

 Insert into #TempTable Exec sys.sp_MSforeachtable ' sp_spaceused "?" ' Select * from #TempTable

Ardalan Shahgholi

다음 단계에 따라 모든 테이블의 크기를 빠르게 얻는 방법은 다음과 같습니다.

1. 지정된 T-SQL 명령 을 작성하여 모든 데이터베이스 테이블을 나열합니다.

    select 'exec sp_spaceused ' + TABLE_NAME from INFORMATION_SCHEMA.TABLES where TABLE_TYPE = 'BASE TABLE'

2. 이제 데이터베이스 테이블 목록을 복사하고 새 쿼리 분석기 창에 복사합니다.

    exec sp_spaceused table1 exec sp_spaceused table2 exec sp_spaceused table3 exec sp_spaceused table4 exec sp_spaceused table5

3. SQL 쿼리 분석기 에서 상단 도구 모음 옵션 에서 파일로 결과를 선택합니다 ( Ctrl + Shift + F ).

4. 이제 마지막으로 위의 도구 모음 에서 빨간색으로 표시된 실행 버튼을 누르십시오.

5. 이제 모든 테이블 의 데이터베이스 크기가 컴퓨터의 파일에 저장됩니다.

   

Anjan Kant

OSQL을 사용하는 명령 프롬프트에서:

 OSQL -E -d <*databasename*> -Q "exec sp_msforeachtable 'sp_spaceused [?]'" > result.txt

user4658783

나는 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

Alan Cardoso

내 게시물은 SQL Server 2000에만 관련이 있으며 내 환경에서 작동하도록 테스트되었습니다.

이 코드는 단일 데이터베이스뿐만 아니라 단일 인스턴스의 모든 가능한 데이터베이스에 액세스합니다.

두 개의 임시 테이블을 사용하여 적절한 데이터를 수집한 다음 결과를 하나의 '라이브' 테이블에 덤프합니다.

반환된 데이터는 DatabaseName, DatabaseTableName, Rows(테이블 내), 데이터(테이블 크기를 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

Andrew

다음은 크기 내림차순으로 정렬된 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

Sergey

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

Zach Smith

이 쿼리는 사용하기 쉽고 빠릅니다.

 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

Daxesh Radadiya

개별 테이블이 아닌 데이터베이스에서 낭비되는 빈 공간에만 관심이 있는 경우 다음을 고려할 수 있습니다.

ETL 상황과 같이 데이터베이스에 많은 양의 데이터 삽입 및 삭제가 발생하는 경우 파일 그룹이 자동으로 커지지 만 다시 자동으로 축소되지 않으므로 데이터베이스에서 사용하지 않는 공간이 너무 많이 발생합니다.

데이터베이스의 속성 페이지를 사용하여 이러한 경우인지 확인할 수 있습니다. 축소하고(데이터베이스를 마우스 오른쪽 버튼으로 클릭 > 작업 > 축소) 공간을 다시 확보할 수 있습니다. 그러나 근본적인 이유가 여전히 존재하는 경우 데이터베이스가 다시 커질 것입니다(그리고 다시 커지는 데 추가 시간이 걸리고 충분히 커질 때까지 속도가 느려지므로 이 경우에는 수행하지 마십시오)

K4M

위의 @Mark 답변을 참고하여 @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

Chris Smith

출처 : http:www.stackoverflow.com/questions/7892334/get-size-of-all-tables-in-database