To estimate the size or the future size of a DB

USE master
GO

if exists (select * from dbo.sysobjects where id = object_id('dbo.sp__EstimateSize') AND type = 'P')
drop procedure dbo.sp__EstimateSize
GO
CREATE PROC dbo.sp__EstimateSize @UpdateUsage varchar(6) = 'FALSE',@ObjGuidance varchar(128) = NULL, @ProjectedRows int = NULL
AS
/***********************************************************************************************************
** Creation Date: 02/21/2001
** Modified Date: 12-09-2002
** Created By: avigneau
** Database: Run in master so it can be called from any databases
** Description  : A procedure to estimate the size or the future size of a database
  
** Parameters   : 

** Compatibility: SQL Server 6.5,7.0,2000
** Remark: The level of index fragmentation is not taken into consideration. 
  Actual level of fragmentation is assume to be proportionnal over time.
  Only one object can be used as the witness to drive the database size.
  A normally distributed database is needed for this tool to report accurate estimates.
  
** Example: dbo.sp__EstimateSize @UpdateUsage = true ,@ObjGuidance = authors, @ProjectedRows = 10000000

***********************************************************************************************************/SET NOCOUNT ON


DECLARE  @ObjOwner varchar(128),@ObjName varchar(128)
,@rows int, @DBSize int,@Guiderows int, @GuideDBSize int

IF @ObjGuidance IS NULL
SELECT @ObjGuidance = '%'
IF UPPER(@UpdateUsage) <> 'TRUE'
SELECT @UpdateUsage = ''
ELSE
SELECT @UpdateUsage = ',TRUE'


-- =============================================
-- Declare and using a READ_ONLY cursor
-- =============================================

CREATE TABLE #PDG_TABLESIZE (TD_TableName varchar(128) NOT NULL, LastDateRun datetime NOT NULL, rows int NOT NULL, reserved int NOT NULL,data int NOT NULL,index_size int NOT NULL,unused int NOT NULL)

CREATE TABLE #TABLESIZE (name varchar(128) NOT NULL, rows varchar(32) NOT NULL, reserved varchar(32) NOT NULL,data varchar(32) NOT NULL,index_size varchar(32) NOT NULL,unused varchar(32) NOT NULL)

DECLARE crObjName INSENSITIVE CURSOR
FOR SELECT su.name,so.name 
FROM dbo.sysobjects so
JOIN dbo.sysusers su
ON so.uid = su.uid
WHERE so.type = 'U'

OPEN crObjName

FETCH NEXT FROM crObjName INTO @ObjOwner,@ObjName
WHILE (@@fetch_status <> -1)
BEGIN
IF (@@fetch_status <> -2)
BEGIN
INSERT #TABLESIZE EXEC ('EXEC dbo.sp_spaceused '''+@ObjOwner+'.'+@ObjName+''''+@UpdateUsage)
END
FETCH NEXT FROM crObjName INTO @ObjOwner,@ObjName
END

CLOSE crObjName
DEALLOCATE crObjName



INSERT #PDG_TABLESIZE (TD_TableName,LastDateRun,rows,reserved,data,index_size,unused)
SELECT name,GETDATE(),CONVERT(int,rows),CONVERT(int,REVERSE(STUFF(REVERSE(reserved),1,3,''))),CONVERT(int,REVERSE(STUFF(REVERSE(data),1,3,''))),CONVERT(int,REVERSE(STUFF(REVERSE(index_size),1,3,''))),CONVERT(int,REVERSE(STUFF(REVERSE(unused),1,3,'')))
FROM #TABLESIZE
DROP TABLE #TABLESIZE

SELECT @DBSize = SUM(reserved),@rows = SUM(rows)  FROM #PDG_TABLESIZE

IF @ObjGuidance = '%'
BEGIN
SELECT TD_TableName AS TableName,LastDateRun,rows AS Rows,reserved AS 'Total Size (Kb)',data AS 'Data Size (Kb)',index_size AS 'Index Size (Kb)',unused AS 'Un-used Space (Kb)'
,CONVERT(DEC(5,2),((reserved+0.0)/(@DBSize+0.0))*100)  AS 'Size (%) with Total DB Space'
,CONVERT(DEC(5,2),((rows+0.0)/(@rows+0.0))*100)  AS 'Size (%) with Total DB Rows'
FROM #PDG_TABLESIZE
ORDER BY rows DESC
SELECT @DBSize AS 'DATABASE ESTIMATE ACTUAL SIZE'
END
ELSE IF @ProjectedRows IS NULL
BEGIN
SELECT @GuideDBSize = SUM(reserved),@Guiderows = SUM(rows)  FROM #PDG_TABLESIZE WHERE TD_TableName LIKE @ObjGuidance
SELECT TD_TableName AS TableName,LastDateRun,rows AS Rows,reserved AS 'Total Size (Kb)',data AS 'Data Size (Kb)',index_size AS 'Index Size (Kb)',unused AS 'Un-used Space (Kb)'
,CONVERT(DEC(5,2),((reserved+0.0)/(@GuideDBSize+0.0)))  AS 'Size compare to ObjGuide Space'
,CONVERT(DEC(5,2),((rows+0.0)/(@Guiderows+0.0)))  AS '# of Rows compare to ObjGuide'
FROM #PDG_TABLESIZE
ORDER BY rows DESC
SELECT @DBSize AS 'DATABASE ESTIMATE ACTUAL SIZE'
END
ELSE 
BEGIN
SELECT @GuideDBSize = SUM(reserved),@Guiderows = SUM(rows)  FROM #PDG_TABLESIZE WHERE TD_TableName LIKE @ObjGuidance
SELECT TD_TableName AS TableName,LastDateRun
,rows AS 'Actual # Rows'
,CASE WHEN rows <> 0 THEN CONVERT(DEC(16,0),((rows+0.0)/(@Guiderows+0.0)) * @ProjectedRows) ELSE 0 END AS 'Projected # Rows'
,reserved AS 'Actual Total Size (Kb)'
,CASE WHEN rows <> 0 THEN CONVERT(DEC(16,0),((rows+0.0)/(@Guiderows+0.0)) * (@ProjectedRows+0.0) * reserved/(rows+0.0)) ELSE 0 END AS 'Projected Total Size (Kb)'
FROM #PDG_TABLESIZE
ORDER BY rows DESC
SELECT @DBSize AS 'DB ESTIMATE ACTUAL SIZE', SUM(CASE WHEN rows <> 0 THEN CONVERT(DEC(16,0),((rows+0.0)/(@Guiderows+0.0)) * (@ProjectedRows+0.0) * reserved/(rows+0.0)) ELSE 0 END)  AS 'DB ESTIMATE PROJECTED SIZE' FROM #PDG_TABLESIZE
END
DROP TABLE #PDG_TABLESIZE
GO