(─━┘_└━─)/ 머리만1톤(Head1Ton)

/* 방법1. 기존의 시스템 SP를 단순히 활용한 예제 */

EXEC sp_MSforeachtable 'EXEC sp_spaceused [?], ''TRUE''';

/* 방법2. 기존의 시스템 SP를 활용하여 결과를 테이블에 저장한 예제 */

USE Sample;

GO

CREATE TABLE spaceused_AdventureWorks (

             TableName           sysname,

             Rows                   int,

             Reserved             varchar(20),

             Data                    varchar(20),

             Index_size           varchar(20),

             Unused                varchar(20));

GO

USE AdventureWorks;

GO

INSERT INTO Sample.dbo.spaceused_AdventureWorks

EXEC sp_MSforeachtable 'EXEC sp_spaceused [?], ''TRUE''';

GO

SELECT * FROM Sample.dbo.spaceused_AdventureWorks;

/* 방법3. sp_spaceused의 소스 코드를 수정하여 사용하기 */

CREATE PROCEDURE sys.sp_spaceused_alltable

AS

SET NOCOUNT OFF

SELECT t1.tablename,

rows = convert(char(11),t1.rows),

reserved = ltrim(str((t1.reservedpages+isnull(t2.reservedpages,0)) * 8192 / 1024.,15,0) + ' KB'),

data = ltrim(str(t1.pages * 8192 / 1024.,15,0) + ' KB'),

index_size = ltrim(str(((t1.usedpages+isnull(t2.usedpages,0)) - t1.pages) * 8192 / 1024.,15,0) + ' KB'),

unused = ltrim(str(((t1.reservedpages+isnull(t2.reservedpages,0)) - (t1.usedpages+isnull(t2.usedpages,0))) * 8192 / 1024.,15,0) + ' KB')

FROM (

SELECT tablename = o.name,

             reservedpages = sum(a.total_pages),

             usedpages = sum(a.used_pages),

             pages = sum(

                 CASE

                   When a.type <> 1 Then a.used_pages

                           When p.index_id < 2 Then a.data_pages

                           Else 0

                           END

             ),

             rows = sum(

                 CASE

                           When (p.index_id < 2) and (a.type = 1) Then p.rows

                           Else 0

                           END

             )

FROM sys.partitions p

             JOIN sys.allocation_units a ON p.partition_id = a.container_id

             JOIN sys.objects o ON p.object_id = o.object_id

WHERE o.type_desc = 'USER_TABLE'

             GROUP BY o.name ) t1

LEFT OUTER JOIN (

SELECT tablename = o.name,

             reservedpages = sum(a.total_pages),

             usedpages = sum(a.used_pages)

FROM sys.partitions p

             JOIN sys.allocation_units a ON p.partition_id = a.container_id

             JOIN sys.internal_tables it ON p.object_id = it.object_id and it.internal_type IN (202,204)

             JOIN sys.objects o ON o.object_id = it.parent_id

WHERE o.type_desc = 'USER_TABLE'

GROUP BY o.name) t2 on t1.tablename = t2.tablename

SET NOCOUNT OFF