/*         Procedure Created:  XPRIME_XDA_SIZING                       */
/*                Created on:  June 18, 2004                           */
/*                Created by:  XPrime, Inc.                            */
/*                                                                     */
/* Syntax:                                                             */
/*   EXEC XPRIME_XDA_SIZING  <tbl_name>|'*', <db_name>                 */
/*                                                                     */
/*                                                                     */
/*    Where: <tbl_name> = specific user defined table name in db_name  */
/*           '*'        = all user defined tables in db_name           */
/*            <db_name> = target database to be scanned & sized        */
/*                                                                     */
/*                                                                     */
/*  Results: Table_name, data space used, index space used, summary    */
/*           statistics for entire database, recommended minimum       */
/*           size of XPrime Database Accelerator (XDA) system.         */
/*                                                                     */


-- If the SP is already defined, remove it

IF EXISTS (SELECT name 
	   FROM   sysobjects 
	   WHERE  name = N'XPRIME_XDA_SIZING' 
	   AND 	  type = 'P')
DROP PROCEDURE XPRIME_XDA_SIZING
GO 

CREATE PROCEDURE XPRIME_XDA_SIZING
@table_name	sysname,
@database_name  sysname
AS

	PRINT 'Starting Procedure: XPRIME_XDA_SIZING'
	
	/* Declare Variables */
	DECLARE @tblname			sysname 
	DECLARE @XDA_SIZE_PER_NODE		BIGINT
	DECLARE @owner				SYSNAME
	DECLARE @XDA_FREE			BIGINT
	DECLARE @XDA_Data_Space_Used		VARCHAR(50)
	DECLARE @XDA_Index_Space_Used		VARCHAR(50)
	DECLARE @XDA_Data_Space_Used_I		BIGINT
	DECLARE @XDA_Index_Space_Used_I		BIGINT
	DECLARE @XDA_Data_Space_Used_SUM	BIGINT
	DECLARE @XDA_Index_Space_Used_SUM 	BIGINT
	DECLARE @SIZE_SCALE			BIGINT
	DECLARE @NUM_XDAS			BIGINT
	DECLARE @HEAD_ROOM			BIGINT
	DECLARE @TMP_BUFFER			VARCHAR(80)
	DECLARE @TMP_DATA			VARCHAR(20)
	
	DECLARE @FUDGE_SIZE			FLOAT

	SELECT @XDA_SIZE_PER_NODE		= 4096000000
	SELECT @NUM_XDAS			= 0
	SELECT @XDA_Data_Space_Used_SUM 	= 0
	SELECT @XDA_Index_Space_Used_SUM  	= 0
	SELECT @HEAD_ROOM			= 1 --Number of extra nodes
	SELECT @FUDGE_SIZE			= 1.25


	/*Create the TMP table to store the table size info*/
	CREATE TABLE #TempTable 
	(	tname varchar(50),
		Row_Count int,
		Table_Size varchar(50),
		Data_Space_Used varchar(50),
		Index_Space_Used varchar(50),
		Unused_Space varchar(50)
	)
	
	DECLARE XDA_SZ 			CURSOR LOCAL FOR 
		SELECT  Data_Space_Used, Index_Space_Used FROM #TempTable
	OPEN XDA_SZ

	/*Create a TMP View to get the col data*/
	EXEC('CREATE VIEW xprime_syscol_tmp AS SELECT * FROM ' + @database_name + '..sysobjects')

	if(@table_name = '*')
	BEGIN
		PRINT 'Scanning Database: ' + @database_name
		DECLARE XDA_SP		CURSOR LOCAL FOR
			select name from xprime_syscol_tmp where xtype = 'U'
		OPEN XDA_SP
	END
	ELSE
	BEGIN
		DECLARE XDA_SP		CURSOR LOCAL FOR
			select name from xprime_syscol_tmp where xtype = 'U' AND name = @table_name
		OPEN XDA_SP
	END

	FETCH XDA_SP into @tblname

	/* Find out if there's any data */
	IF (@@FETCH_STATUS = -1)
	BEGIN
		PRINT 'NOTE: No Tables Found'
		CLOSE XDA_SP
		DEALLOCATE XDA_SP
		DEALLOCATE XDA_SZ
		DROP TABLE #TempTable
		/*Kill the view*/
		EXEC('DROP VIEW xprime_syscol_tmp')
		return;
	END

	WHILE (@@FETCH_STATUS <> -1)
	BEGIN

		/*Create a TMP View to get the owner data*/
		EXEC('CREATE VIEW sysown_tmp AS select b.name from ' + @database_name + '..sysobjects a, ' + @database_name + '..sysusers b where a.name = ''' + @tblname +''' and a.uid = b.uid')
		DECLARE XDA_OWNER 			CURSOR LOCAL FOR
			SELECT * from sysown_tmp
		OPEN XDA_OWNER
		FETCH XDA_OWNER INTO @owner
		CLOSE XDA_OWNER
		DEALLOCATE XDA_OWNER
		/*Kill the view*/
		EXEC('DROP VIEW sysown_tmp')

		INSERT INTO #TempTable EXEC( 'use ' + @database_name + '; EXEC sp_spaceused ''' + @owner + '.' + @tblname + '''')

		--fetch the size
		FETCH XDA_SZ INTO @XDA_Data_Space_Used, @XDA_Index_Space_Used
	
		--Normalize the table size
		IF (LTRIM(SUBSTRING(@XDA_Data_Space_Used,len(@XDA_Data_Space_Used) -2, len(@XDA_Data_Space_Used))) = 'KB')
			SET @SIZE_SCALE = 1000
		ELSE
		IF (LTRIM(SUBSTRING(@XDA_Data_Space_Used,len(@XDA_Data_Space_Used) -2, len(@XDA_Data_Space_Used))) = 'MB')
			SET @SIZE_SCALE = 1000000
		ELSE
		IF (LTRIM(SUBSTRING(@XDA_Data_Space_Used,len(@XDA_Data_Space_Used) -2, len(@XDA_Data_Space_Used))) = 'GB')
			SET @SIZE_SCALE = 1000000000
		ELSE
		IF (LTRIM(SUBSTRING(@XDA_Data_Space_Used,len(@XDA_Data_Space_Used) -2, len(@XDA_Data_Space_Used))) = 'TB')
			SET @SIZE_SCALE = 1000000000000
			SELECT @XDA_Data_Space_Used_I =  SUBSTRING(@XDA_Data_Space_Used,1, len(@XDA_Data_Space_Used) -2)
		SELECT @XDA_Data_Space_Used_I = @XDA_Data_Space_Used_I * @SIZE_SCALE * @FUDGE_SIZE

		--Normalise the index size
		IF (LTRIM(SUBSTRING(@XDA_Index_Space_Used,len(@XDA_Index_Space_Used) -2, len(@XDA_Index_Space_Used))) = 'KB')
			SET @SIZE_SCALE = 1000
		ELSE
		IF (LTRIM(SUBSTRING(@XDA_Index_Space_Used,len(@XDA_Index_Space_Used) -2, len(@XDA_Index_Space_Used))) = 'MB')
			SET @SIZE_SCALE = 1000000
		ELSE
		IF (LTRIM(SUBSTRING(@XDA_Index_Space_Used,len(@XDA_Index_Space_Used) -2, len(@XDA_Index_Space_Used))) = 'GB')
			SET @SIZE_SCALE = 1000000000
		ELSE
		IF (LTRIM(SUBSTRING(@XDA_Index_Space_Used,len(@XDA_Index_Space_Used) -2, len(@XDA_Index_Space_Used))) = 'TB')
			SET @SIZE_SCALE = 1000000000000
		SELECT @XDA_Index_Space_Used_I =  SUBSTRING(@XDA_Index_Space_Used,1, len(@XDA_Index_Space_Used) -2)
		SELECT @XDA_Index_Space_Used_I = @XDA_Index_Space_Used_I * @SIZE_SCALE * @FUDGE_SIZE
	
		SELECT @TMP_BUFFER = 'Total bytes For Table    ' + @database_name + '.' + @owner + '.' + @tblname + ': ' + @XDA_Data_Space_Used
		PRINT  @TMP_BUFFER

		SELECT @XDA_Data_Space_Used_SUM = @XDA_Data_Space_Used_SUM + @XDA_Data_Space_Used_I

		SELECT @TMP_BUFFER = 'Total bytes For Index on ' + @database_name + '.' + @owner + '.' + @tblname + ': ' + @XDA_Index_Space_Used
		PRINT  @TMP_BUFFER
		PRINT  '  '
	
		SELECT  @XDA_Index_Space_Used_SUM = @XDA_Index_Space_Used_SUM + @XDA_Index_Space_Used_I

		FETCH XDA_SP into @tblname
END

	PRINT  '  '
	SELECT @TMP_DATA = @XDA_Data_Space_Used_SUM/1024000
	SELECT @TMP_DATA = 
	CASE
	     WHEN DATALENGTH(@TMP_DATA) >= 7 
		THEN SUBSTRING(@TMP_DATA , 1, DATALENGTH(@TMP_DATA)-6) +','+ SUBSTRING(@TMP_DATA , DATALENGTH(@TMP_DATA)-5, 3) +','+SUBSTRING(@TMP_DATA, DATALENGTH(@TMP_DATA)-2, 3)
	     WHEN DATALENGTH(@TMP_DATA) >= 4
		THEN SUBSTRING(@TMP_DATA , 1, DATALENGTH(@TMP_DATA)-3) +','+SUBSTRING(@TMP_DATA, DATALENGTH(@TMP_DATA)-2, 3)
	     ELSE @TMP_DATA
	END -- CASE @TMP_DATA
	SELECT @TMP_BUFFER = 'Total Data  Space Required (MBytes)===> ' + @TMP_DATA
	PRINT  @TMP_BUFFER
	PRINT  '  '
	
	SELECT @TMP_DATA = @XDA_Index_Space_Used_SUM/1024000
	SELECT @TMP_DATA = 
	CASE
	     WHEN DATALENGTH(@TMP_DATA) >= 7 
		THEN SUBSTRING(@TMP_DATA , 1, DATALENGTH(@TMP_DATA)-6) +','+ SUBSTRING(@TMP_DATA , DATALENGTH(@TMP_DATA)-5, 3) +','+SUBSTRING(@TMP_DATA, DATALENGTH(@TMP_DATA)-2, 3)
	     WHEN DATALENGTH(@TMP_DATA) >= 4
		THEN SUBSTRING(@TMP_DATA , 1, DATALENGTH(@TMP_DATA)-3) +','+SUBSTRING(@TMP_DATA, DATALENGTH(@TMP_DATA)-2, 3)
	     ELSE @TMP_DATA
	END -- CASE @TMP_DATA
	SELECT @TMP_BUFFER = 'Total Index Space Required (MBytes)===> ' + @TMP_DATA
	PRINT  @TMP_BUFFER
	PRINT  '  '
	
	SELECT @TMP_DATA = (@XDA_Index_Space_Used_SUM + @XDA_Data_Space_Used_SUM)/1024000
	SELECT @TMP_DATA = 
	CASE
	     WHEN DATALENGTH(@TMP_DATA) >= 7 
		THEN SUBSTRING(@TMP_DATA , 1, DATALENGTH(@TMP_DATA)-6) +','+ SUBSTRING(@TMP_DATA , DATALENGTH(@TMP_DATA)-5, 3) +','+SUBSTRING(@TMP_DATA, DATALENGTH(@TMP_DATA)-2, 3)
	     WHEN DATALENGTH(@TMP_DATA) >= 4
		THEN SUBSTRING(@TMP_DATA , 1, DATALENGTH(@TMP_DATA)-3) +','+SUBSTRING(@TMP_DATA, DATALENGTH(@TMP_DATA)-2, 3)
	     ELSE @TMP_DATA
	END -- CASE @TMP_DATA
	SELECT @TMP_BUFFER = 'Total DBMS  Space Required (MBytes)===> ' + @TMP_DATA
	PRINT  @TMP_BUFFER
	PRINT  '  '

	SELECT @NUM_XDAS = CEILING((@XDA_Index_Space_Used_SUM + @XDA_Data_Space_Used_SUM) / @XDA_SIZE_PER_NODE) + @HEAD_ROOM

	SELECT @TMP_DATA = @NUM_XDAS
	SELECT @TMP_BUFFER = 'Minimum Recommended Number of XDA Nodes ====> ' + @TMP_DATA
	PRINT  @TMP_BUFFER
	PRINT  '  '

CLOSE XDA_SP
CLOSE XDA_SZ
DEALLOCATE XDA_SP
DEALLOCATE XDA_SZ

/*Kill the temptable & view*/
DROP TABLE #TempTable
DROP VIEW xprime_syscol_tmp

PRINT '<End Procedure>'

GO