This script is very useful to determine what tables are using the most space,  and also for planning Replication task and figure out how much space is needed for the snapshot folder

/*
DESCRIPTION    List table sizes for a list of tables
Configuration    within script. See comments
Fill in USE for the database
Must know how to compile list of tables


Compatibility list:
MSSQL2005
MSSQL2008

Unknown
MSSQL2000
*/ 
 

/* ********  CONFIGURATION *************
Source of tables
1  Excel Spreadsheet
2  Cursor
3  All tables
*/
declare @source int
SET @SOURCE = 2

---- ************ MUST SET DB HERE

USE CAMPIVCDB


/* ******** END CONFIGURATION ************ */
 
 
DECLARE @tmpTableSizes TABLE
(
    tableName varchar(100),
    numberofRows varchar(100),
    reservedSize varchar(50),
    dataSize varchar(50),
    indexSize varchar(50),
    unusedSize varchar(50)
)

IF (@source = 1)
BEGIN
-- Use MS Excel to generate this list

insert @tmpTableSizes exec sp_spaceused 'vpx_hist_stat1' ;
insert @tmpTableSizes exec sp_spaceused '' ;
insert @tmpTableSizes exec sp_spaceused 'table_3' ;
insert @tmpTableSizes exec sp_spaceused 'table_4' ;
insert @tmpTableSizes exec sp_spaceused 'table_5' ;

END

IF (@source = 2)
BEGIN
-- Or use this cursor

declare @tableName sysname
declare tablelist cursor for

SELECT '['+SCHEMA_NAME(schema_id)+'].['+name+']'
FROM CAMPIVCDB.sys.objects
WHERE name like
'vpx_hist_stat%'

OPEN tablelist
FETCH NEXT FROM tablelist INTO @tableName
WHILE @@FETCH_STATUS = 0
BEGIN
insert @tmpTableSizes exec sp_spaceused @tablename;

FETCH NEXT FROM tablelist INTO @tableName
END
CLOSE tablelist
DEALLOCATE tablelist

END

IF (@source=3)
BEGIN
-- or use this line for ALL tables

EXEC sp_MSforeachtable 'INSERT @tmpTableSizes exec sp_spaceused ''?'''


END

IF (@source NOT IN (1,2,3))
BEGIN 
PRINT 'SOURCE NEEDS T OBE 1, 2, OR 3'
END
ELSE
BEGIN
-- use this section for a summary
-- /*
select
@@servername
,sum(cast(LEFT(reservedSize, LEN(reservedSize) - 3)/1024 as numeric(6,2))) as 'reserved size MB'
,SUM(cast(LEFT(datasize, LEN(datasize) - 3)/1024 as numeric(6,2))) as 'datasize MB'
from @tmpTableSizes

--*/

-- use this section for a detail view

--/*
select 
@@servername
,tableName, numberofrows
, cast(LEFT(reservedSize, LEN(reservedSize) - 4) as int) as 'reserved size KB'
, cast(LEFT(datasize, LEN(datasize) - 4) as int) as 'datasize KB'
from @tmpTableSizes
-- ORDER BY TABLENAME
ORDER BY numberofRows desc
--*/

 


END