----------------------------------------------------------------
--                                                              
--  File:   SPACEUSED_EXTENDED.SQL                                          
--          Microsoft TPC-E Benchmark Kit Ver. 1.14.0-1031      
--          Copyright Microsoft, 2008-2015                  
--            
-- updated standard sp_spaceused to handle larger table sizes                                                  
----------------------------------------------------------------
USE tpce

Changed database context to 'tpce'.

SET ANSI_NULLS ON

SET QUOTED_IDENTIFIER ON


IF EXISTS ( SELECT name FROM sysobjects WHERE name = 'usp_spaceused_ext' )
    DROP PROCEDURE usp_spaceused_ext


CREATE procedure usp_spaceused_ext
                    @objname        nvarchar(776) = null,   -- The object we want size on.
                    @updateusage    varchar(5) = false,     -- Param. for specifying that usage info. should be updated.
                    @mode           varchar(11) = 'ALL',    -- Param. for specifying whether to calculate space usage for
                                                            -- local data only or remote data archive only or both.
                    @oneresultset   bit = 0                 -- Param. for specifying whether to return only one result set
AS
DECLARE @id             int,            -- The object id that takes up space
        @type           character(2),   -- The object type.
        @pages          bigint,         -- Working variable for size calc.
        @dbname         sysname,
        @dbsize         bigint,
		@logsize        bigint,
        @reservedpages  bigint,
        @usedpages      bigint,
        @rowCount       bigint,
        @isstretched    bit,
        @remotesql      nvarchar(1000),
        @isupdateusage  bit,
        @errormessage   nvarchar(4000),
        @errornumber    int,
        @errorseverity  int,
        @errorstate     int,
        @errorline      int;

DECLARE @summary_tmp_table TABLE(   
                                    database_name       nvarchar(128),
			                        database_size       varchar(18),
                                    unallocated_space   varchar(18),
                                    reserved            varchar(18),
                                    data                varchar(18),
                                    index_size          varchar(18),
                                    unused              varchar(18));

DECLARE @detail_tmp_table TABLE(
                                    name                nvarchar(128),
                                    rows                bigint,
                                    reserved            nvarchar(80),
                                    data                nvarchar(80),
                                    index_size          nvarchar(80),
                                    unused              nvarchar(80));

/*
** Check to see if user wants usages updated.
*/
IF @updateusage IS NOT null
BEGIN
    SELECT  @updateusage    = LOWER(@updateusage)

    IF @updateusage = 'true'
    BEGIN
	    SET @isupdateusage  = 1
    END
    ELSE
    BEGIN
        IF @updateusage = 'false'
        BEGIN
            SET @isupdateusage = 0
        END
        ELSE
        BEGIN
            RAISERROR(15143,-1,-1,@updateusage)
            RETURN (1)
        END
    END
END

/*
** Validate the @mode parameter.
*/
IF UPPER(@mode) NOT IN('ALL', 'LOCAL_ONLY', 'REMOTE_ONLY')
BEGIN
    RAISERROR (14822, -1, -1, @mode);
    RETURN (1)
END
/*
** Check to see that the objname is local.
*/
IF @objname IS NOT NULL
BEGIN
    SELECT  @dbname = parsename(@objname, 3)

    IF @dbname IS NOT null AND @dbname <> db_name()
    BEGIN
        RAISERROR(15250,-1,-1)
        RETURN (1)
    END

    IF @dbname IS null
        SELECT  @dbname = db_name()

	/*
	**  Try to find the object.
	*/
    SELECT  @id     = object_id,
            @type   = type
    FROM    sys.objects
    WHERE   object_id   = object_id(@objname)

	-- Translate @id to internal-table for queue
    IF @type = 'SQ'
        SELECT  @id = object_id
        FROM    sys.internal_tables
        WHERE   parent_id   = @id
                AND internal_type   = 201 --ITT_ServiceQueue

	/*
	**  Does the object exist?
	*/
	IF @id IS null
    BEGIN
        RAISERROR(15009,-1,-1,@objname,@dbname)
        RETURN (1)
    END

	/*
	**  Is the table stretched?
	*/
	--SELECT  @isstretched    = is_remote_data_archive_enabled
    --FROM    sys.tables
    --WHERE   object_id   = @id

	-- Is it a table, view or queue?
	IF @type NOT IN ('U ','S ','V ','SQ','IT')
    BEGIN
        RAISERROR(15234,-1,-1)
        RETURN (1)
    END
END

/*
**  Update usages if user specified to do so.
*/
IF @isupdateusage = 1
BEGIN
    IF @objname IS null
        DBCC updateusage(0) WITH no_infomsgs
    ELSE
        DBCC updateusage(0,@objname) WITH no_infomsgs
		PRINT ' '
END

SET NOCOUNT ON

/*
**  If @id is null, then we want summary data.
*/
IF @id IS null
BEGIN
	/*
	** Calculate local space usage if mode is LOCAL_ONLY or ALL 
	*/
	IF UPPER(@mode) IN ('ALL', 'LOCAL_ONLY')
	BEGIN
        SELECT  @dbsize     = SUM(CONVERT(bigint,CASE WHEN status & 64 = 0 THEN size ELSE 0 END)),
			    @logsize    = SUM(CONVERT(bigint,CASE WHEN status & 64 <> 0 THEN size ELSE 0 END))
        FROM    dbo.sysfiles

        SELECT  @reservedpages  = SUM(a.total_pages),
                @usedpages      = SUM(a.used_pages),
                @pages          = SUM(  CASE -- XML-Index and FT-Index and semantic index internal tables are not considered "data", but is part of "index_size"
						                    WHEN it.internal_type IN (202,204,207,211,212,213,214,215,216,221,222,236) THEN 0
						                    WHEN a.type <> 1 and p.index_id < 2 THEN a.used_pages
						                    WHEN p.index_id < 2 THEN a.data_pages
						                    ELSE 0
                                        END )
        FROM    sys.partitions p JOIN sys.allocation_units a ON p.partition_id = a.container_id
			    LEFT JOIN sys.internal_tables it ON p.object_id = it.object_id

		/*
		** Calculate the summary data and insert them into the cache table
		** reserved: sum(reserved) where indid in (0, 1, 255)
		** data: sum(data_pages) + sum(text_used)
		** index: sum(used) where indid in (0, 1, 255) - data
		** unused: sum(reserved) - sum(used) where indid in (0, 1, 255)
		** note that unallocated space could not be negative
		*/
		INSERT INTO @summary_tmp_table
			SELECT  db_name(),
				    LTRIM(STR((CONVERT (dec (15,2),@dbsize) + CONVERT (dec (15,2),@logsize)) * 8192 / 1048576,15,2) + ' MB'),
                    LTRIM(STR((CASE WHEN @dbsize >= @reservedpages THEN (CONVERT (dec (15,2),@dbsize) - CONVERT (dec (15,2),@reservedpages)) * 8192 / 1048576 ELSE 0 END),15,2) + ' MB'),
                    LTRIM(STR(@reservedpages * 8192 / 1024.,15,0) + ' KB'),
                    LTRIM(STR(@pages * 8192 / 1024.,15,0) + ' KB'),
                    LTRIM(STR((@usedpages - @pages) * 8192 / 1024.,15,0) + ' KB'),
                    LTRIM(STR((@reservedpages - @usedpages) * 8192 / 1024.,15,0) + ' KB')
	END
	
    /*
	** Include remote results if the user specified no arguments , or remote only.
	*/
	IF UPPER(@mode) IN ('ALL', 'REMOTE_ONLY')
	BEGIN
		/*
		** If the database is not stretched, it should not have a remote part, thus skipping remote results.
		*/
		IF NOT EXISTS(SELECT * FROM sys.remote_data_archive_databases)
		BEGIN
			IF UPPER(@mode) = 'REMOTE_ONLY'
			BEGIN
				RAISERROR(14821, 16, 1);
				RETURN (1)
			END
		END
		/*
		** Since the database is stretched and the remote database should exist, we should include remote results
		*/
		ELSE
		BEGIN
			SET @remotesql = CONCAT(N'sys.sp_spaceused_remote_data_archive  ',
				@isupdateusage)
			BEGIN TRY
				INSERT INTO @summary_tmp_table EXECUTE(@remotesql);
			END TRY
			BEGIN CATCH
				SET @errornumber    = ERROR_NUMBER()
				SET @errorseverity  = ERROR_SEVERITY()
				SET @errorstate     = ERROR_STATE()
				SET @errorline      = ERROR_LINE()
				SET @errormessage   = ERROR_MESSAGE()
				RAISERROR(14827, 16, 2, @errornumber, @errorseverity, @errorstate, @errorline, @errormessage)
				IF UPPER(@mode) = 'REMOTE_ONLY'
				BEGIN
					SELECT 1
				END
			END CATCH
		END
	END
	IF @oneresultset = 1
	BEGIN
		SELECT  database_name       = db_name(),
			    database_size       = LTRIM (STR ((SUM  (CONVERT (dec (15, 2), SUBSTRING(s.database_size, 1, CHARINDEX(' ', s.database_size))))),15,2) + ' MB'),
			    'unallocated space' = LTRIM (STR ((SUM  (CONVERT (dec (15, 2), SUBSTRING(s.unallocated_space, 1, CHARINDEX(' ', s.unallocated_space))))),15,2) + ' MB'),
			    reserved            = LTRIM (STR (SUM  (CAST (SUBSTRING(s.reserved, 1, CHARINDEX(' ', s.reserved)) AS bigint))) + ' KB'),
			    data                = LTRIM (STR (SUM( CAST( SUBSTRING(s.data, 1, CHARINDEX(' ', s.data)) AS bigint))) + ' KB'),
			    index_size          = LTRIM (STR (SUM( CAST( SUBSTRING(s.index_size, 1, CHARINDEX(' ', s.index_size)) AS bigint))) + ' KB'),
			    unused              = LTRIM (STR (SUM( CAST( SUBSTRING(s.unused, 1, CHARINDEX(' ', s.unused)) AS bigint))) + ' KB')
        FROM    @summary_tmp_table AS s
	END
	ELSE
	BEGIN
		SELECT  database_name       = db_name(),
			    database_size       = LTRIM (STR ((SUM  (CONVERT (dec (15, 2), SUBSTRING(s.database_size, 1, CHARINDEX(' ', s.database_size))))),15,2) + ' MB'),
			    'unallocated space' = LTRIM (STR ((SUM  (CONVERT (dec (15, 2), SUBSTRING(s.unallocated_space, 1, CHARINDEX(' ', s.unallocated_space))))),15,2) + ' MB')
		FROM    @summary_tmp_table AS s
		SELECT  reserved    = LTRIM (STR (SUM  (CAST (SUBSTRING(s.reserved, 1, CHARINDEX(' ', s.reserved)) AS bigint))) + ' KB'),
			    data        = LTRIM (STR (SUM( CAST( SUBSTRING(s.data, 1, CHARINDEX(' ', s.data)) AS bigint))) + ' KB'),
			    index_size  = LTRIM (STR (SUM( CAST( SUBSTRING(s.index_size, 1, CHARINDEX(' ', s.index_size)) AS bigint))) + ' KB'),
			    unused      = LTRIM (STR (SUM( CAST( SUBSTRING(s.unused, 1, CHARINDEX(' ', s.unused)) AS bigint))) + ' KB')
		FROM    @summary_tmp_table AS s
	END
END

/*
** We want a particular object.
*/
ELSE
BEGIN
	/*
	** Calculate local space usage if mode is LOCAL_ONLY or ALL 
	*/
	IF UPPER(@mode) IN ('ALL', 'LOCAL_ONLY')
	BEGIN
		/*
		** Now calculate the summary data. 
		*  Note that LOB Data and Row-overflow Data are counted as Data Pages for the base table
		*  For non-clustered indices they are counted towards the index pages
		*/
		SELECT  @reservedpages  = SUM(reserved_page_count),
			    @usedpages      = SUM(used_page_count),
			    @pages          = SUM(  CASE
					                        WHEN (index_id < 2) THEN (in_row_data_page_count + lob_used_page_count + row_overflow_used_page_count)
					                        ELSE 0
				                        END	),
                @rowCount       = SUM ( CASE
					                        WHEN (index_id < 2) THEN row_count
					                        ELSE 0
				                        END )
		FROM    sys.dm_db_partition_stats
		WHERE   object_id   = @id;

		/*
		** Check if table has XML Indexes or Fulltext Indexes which use internal tables tied to this table
		*/
		IF (SELECT COUNT(*) FROM sys.internal_tables WHERE parent_id = @id AND internal_type IN (202,204,207,211,212,213,214,215,216,221,222,236)) > 0 
		BEGIN
			/*
			**  Now calculate the summary data. Row counts in these internal tables don't 
			**  contribute towards row count of original table.
			*/
			SELECT  @reservedpages  = @reservedpages + SUM(reserved_page_count),
				    @usedpages      = @usedpages + SUM(used_page_count)
			FROM    sys.dm_db_partition_stats p, sys.internal_tables it
			WHERE   it.parent_id    = @id
                    AND it.internal_type IN (202,204,207,211,212,213,214,215,216,221,222,236)
                    AND p.object_id = it.object_id;
		END

		INSERT INTO @detail_tmp_table
			SELECT 
				OBJECT_NAME (@id),
				CONVERT (char(20), @rowCount),
				LTRIM (STR (@reservedpages * 8, 15, 0) + ' KB'),
				LTRIM (STR (@pages * 8, 15, 0) + ' KB'),
				LTRIM (STR ((CASE WHEN @usedpages > @pages THEN (@usedpages - @pages) ELSE 0 END) * 8, 15, 0) + ' KB'),
				LTRIM (STR ((CASE WHEN @reservedpages > @usedpages THEN (@reservedpages - @usedpages) ELSE 0 END) * 8, 15, 0) + ' KB')
	END
	/*
	** Include remote results if the user expects remote space usage.
	*/
	IF UPPER(@mode) = 'ALL' or UPPER(@mode) = 'REMOTE_ONLY'
	BEGIN
		/*
		** If the object is stretched, we should include remote space usage into the result.
		*/
		IF @isstretched = 1
		BEGIN
			SET @remotesql = CONCAT(
				N'sys.sp_spaceused_remote_data_archive  ',
				@isupdateusage,
				N' , ',
				@id)
			BEGIN TRY
				INSERT INTO @detail_tmp_table EXECUTE(@remotesql)
			END TRY
			BEGIN CATCH
				SET @errornumber = ERROR_NUMBER()
				SET @errorseverity = ERROR_SEVERITY()
				SET @errorstate = ERROR_STATE()
				SET @errorline = ERROR_LINE()
				SET @errormessage = ERROR_MESSAGE()
				RAISERROR(14827, 16, 3, @errornumber, @errorseverity, @errorstate, @errorline, @errormessage)
				IF UPPER(@mode) = 'REMOTE_ONLY'
				BEGIN
					RETURN (1)
				END
			END CATCH
		END
		/*
		** If the object is not stretched, the object shouldn't have a remote part, thus skipping remote results.
		*/
		ELSE
		BEGIN
			IF UPPER(@mode) = 'REMOTE_ONLY'
			BEGIN
				RAISERROR(14821, 16, 2);
				RETURN (1)
			END
		END
	END

	SELECT  @objname AS name,
	        CONVERT (char(20), SUM(rows)) AS rows,
	        LTRIM (STR (SUM (CAST (SUBSTRING(reserved, 1, CHARINDEX(' ', reserved)) AS bigint))) + ' KB' ) AS reserved,
	        LTRIM (STR (SUM (CAST (SUBSTRING(data, 1, CHARINDEX(' ', data)) AS bigint))) + ' KB') AS data,
	        LTRIM (STR (SUM (CAST (SUBSTRING(index_size, 1, CHARINDEX(' ', index_size)) AS bigint))) + ' KB') AS index_size,
	        LTRIM (STR (SUM (CAST (SUBSTRING(unused, 1, CHARINDEX(' ', unused)) AS bigint))) + ' KB') AS unused
	FROM    @detail_tmp_table
END

RETURN (0)

