----------------------------------------------------------------
--                                                              
--  File:   GET_TABLE_SIZES.SQL                                          
--          Microsoft TPC-E Benchmark Kit Ver. 1.14.0-1031      
--          Copyright Microsoft, 2008-2015                  
--                                                              
----------------------------------------------------------------
PRINT	' '
SELECT	convert(char(30), getdate(),9)
PRINT	' '
GO

USE tpce
GO

SET NOCOUNT ON
GO

create table #TABLE_SPACE_WORK
(
	TABLE_NAME  sysname         not null,
	TABLE_ROWS  numeric(18,0)   not null,
	RESERVED    varchar(50)     not null,
	DATA        varchar(50)     not null,
	INDEX_SIZE  varchar(50)     not null,
	UNUSED      varchar(50)     not null,
)

create table #TABLE_SPACE_USED
(
	SEQ         int             not null	
	            identity(1,1)   primary key clustered,
	TABLE_NAME  sysname         not null,
	TABLE_ROWS  numeric(18,0)   not null,
	RESERVED    varchar(50)     not null,
	DATA        varchar(50)     not null,
	INDEX_SIZE  varchar(50)     not null,
	UNUSED      varchar(50)     not null,
)

create table #TABLE_SPACE
(
	SEQ         int             not null
	            identity(1,1)   primary key clustered,
	TABLE_NAME  SYSNAME         not null,
	TABLE_ROWS  bigint          not null,
	RESERVED    bigint          not null,
	DATA        bigint          not null,
	INDEX_SIZE  bigint          not null,
	UNUSED      bigint          not null,
	USED_MB     numeric(18,4)   not null,
	USED_GB     numeric(18,4)   not null,
	AVERAGE_BYTES_PER_ROW           numeric(18,5)	null,
	AVERAGE_DATA_BYTES_PER_ROW      numeric(18,5)	null,
	AVERAGE_INDEX_BYTES_PER_ROW     numeric(18,5)	null,
	AVERAGE_UNUSED_BYTES_PER_ROW    numeric(18,5)	null,
)

DECLARE @proc           varchar(200),
        @TABLE_NAME     varchar(200)

SELECT  @proc   = rtrim(db_name())+'.dbo.usp_spaceused_ext'

DECLARE TableNames CURSOR LOCAL FOR SELECT  TABLE_NAME  = RTRIM(TABLE_SCHEMA)+'.'+RTRIM(TABLE_NAME)
                                    FROM    INFORMATION_SCHEMA.TABLES
                                    WHERE   TABLE_TYPE	= 'BASE TABLE'
                                    ORDER   BY TABLE_NAME

OPEN TableNames

FETCH NEXT FROM TableNames INTO @TABLE_NAME

WHILE (@@FETCH_STATUS = 0)
BEGIN
    TRUNCATE TABLE #TABLE_SPACE_WORK

    INSERT INTO #TABLE_SPACE_WORK
                    (
                        TABLE_NAME,
                        TABLE_ROWS,
                        RESERVED,
                        DATA,
                        INDEX_SIZE,
                        UNUSED
                    )
	                EXEC @proc @objname = @TABLE_NAME ,@updateusage = 'true'

    INSERT INTO #TABLE_SPACE_USED
                    (
                        TABLE_NAME,
                        TABLE_ROWS,
                        RESERVED,
                        DATA,
                        INDEX_SIZE,
                        UNUSED
                    )
                    SELECT  TABLE_NAME,
                            TABLE_ROWS,
                            RESERVED,
                            DATA,
                            INDEX_SIZE,
                            UNUSED
                    FROM    #TABLE_SPACE_WORK
            
    FETCH NEXT FROM TableNames INTO @TABLE_NAME

END --While end

CLOSE       TableNames

DEALLOCATE  TableNames

INSERT INTO #TABLE_SPACE
	            (
                    TABLE_NAME,
                    TABLE_ROWS,
                    RESERVED,
                    DATA,
                    INDEX_SIZE,
                    UNUSED,
                    USED_MB,
                    USED_GB,
                    AVERAGE_BYTES_PER_ROW,
                    AVERAGE_DATA_BYTES_PER_ROW,
                    AVERAGE_INDEX_BYTES_PER_ROW,
                    AVERAGE_UNUSED_BYTES_PER_ROW
                )
                SELECT  TABLE_NAME,
                        TABLE_ROWS,
                        RESERVED,
                        DATA,
                        INDEX_SIZE,
                        UNUSED,
                        USED_MB = ROUND(CONVERT(numeric(25,10),RESERVED)/CONVERT(numeric(25,10),1024),4),
                        USED_GB = ROUND(CONVERT(numeric(25,10),RESERVED)/CONVERT(numeric(25,10),1024*1024),4),
                        AVERAGE_BYTES_PER_ROW           = CASE
		                                                    WHEN TABLE_ROWS <> 0
                                                                THEN ROUND((1024.000000*CONVERT(numeric(25,10),RESERVED))/CONVERT(numeric(25,10),TABLE_ROWS),5)
                                                            ELSE null
                                                          END,
                        AVERAGE_DATA_BYTES_PER_ROW      = CASE
                                                            WHEN TABLE_ROWS <> 0
		                                                        THEN ROUND((1024.000000*CONVERT(numeric(25,10),DATA))/CONVERT(numeric(25,10),TABLE_ROWS),5)
                                                            ELSE null
                                                          END,
                        AVERAGE_INDEX_BYTES_PER_ROW     = CASE
                                                            WHEN TABLE_ROWS <> 0
		                                                        THEN ROUND((1024.000000*CONVERT(numeric(25,10),INDEX_SIZE))/CONVERT(numeric(25,10),TABLE_ROWS),5)
		                                                    ELSE null
                                                          END,
                        AVERAGE_UNUSED_BYTES_PER_ROW    = CASE
                                                            WHEN TABLE_ROWS <> 0
		                                                        THEN ROUND((1024.000000*CONVERT(numeric(25,10),UNUSED))/CONVERT(numeric(25,10),TABLE_ROWS),5)
		                                                    ELSE null
                                                          END
                FROM    (   SELECT  TABLE_NAME,
		                            TABLE_ROWS,
		                            RESERVED    = CONVERT(bigint,RTRIM(REPLACE(RESERVED,'KB',''))),
                                    DATA        = CONVERT(bigint,RTRIM(REPLACE(DATA,'KB',''))),
                                    INDEX_SIZE	= CONVERT(bigint,RTRIM(REPLACE(INDEX_SIZE,'KB',''))),
                                    UNUSED		= CONVERT(bigint,RTRIM(REPLACE(UNUSED,'KB','')))
                            FROM    #TABLE_SPACE_USED AS TSU
                        ) AS TS
                ORDER  BY TABLE_NAME

PRINT 'Show results in ascending order by table name'

SELECT  TABLE_NAME,
	    TABLE_ROWS,
	    RESERVED,
	    DATA,
	    INDEX_SIZE,
	    UNUSED,
	    USED_MB,
	    USED_GB,
	    AVERAGE_BYTES_PER_ROW,
	    AVERAGE_DATA_BYTES_PER_ROW,
	    AVERAGE_INDEX_BYTES_PER_ROW,
	    AVERAGE_UNUSED_BYTES_PER_ROW
FROM    #TABLE_SPACE
ORDER   BY SEQ ASC
GO

DROP TABLE #TABLE_SPACE_WORK
DROP TABLE #TABLE_SPACE_USED 
DROP TABLE #TABLE_SPACE
GO

PRINT	' '
SELECT	convert(char(30), getdate(),9)
PRINT	' '
GO