Sizing up your large vision tables.

Below is a script that will sniff out how large your vision tables are. For simplicity sake, I've assumed that the largest tables in your vision database probably are the LD, TKDetail, Audittrail, ReportLog and ledger tables. You can compare the output produced by the sql below to the results of sp_spaceused to understand the magnitude of your largest tables. Enjoy...

SELECT
o.name,
o.Type,
s.name as SchemaName,
SUM(reserved_page_count) * 8/1024 as ReservedPagesMB,
SUM(used_page_count) * 8/1024 as UsedPagesMB,
SUM( CASE
     WHEN (index_id < 2) THEN (in_row_data_page_count +
                               lob_used_page_count +
                               row_overflow_used_page_count)
     ELSE lob_used_page_count + row_overflow_used_page_count
     END)*8/1024 as TotalPagesMB,
SUM ( CASE
      WHEN (index_id < 2) THEN row_count
      ELSE 0
      END) as recordCount,
SUM( CASE 
     WHEN used_page_count > (CASE
                             WHEN (index_id < 2)
                             THEN ( in_row_data_page_count +
                                    lob_used_page_count +
                                    row_overflow_used_page_count)
                             ELSE lob_used_page_count + 
                             row_overflow_used_page_count END)
THEN used_page_count - (CASE
                        WHEN (index_id < 2)
                        THEN ( in_row_data_page_count +
                               lob_used_page_count +
                               row_overflow_used_page_count)
                        ELSE lob_used_page_count + 
                        row_overflow_used_page_count
                        END) ELSE 0 END)*8/1024 as IndexSizeMB,
SUM(CASE 
    WHEN reserved_page_count > used_page_count
    THEN reserved_page_count - used_page_count
    ELSE 0 END) *8/1024 as UnusedMB
FROM sys.dm_db_partition_stats dps
inner join sys.objects o on dps.object_ID = o.object_id
inner join sys.schemas s on o.schema_id = s.schema_id
WHERE o.name IN('LD','TKDetail','LedgerAR','LedgerMISC','LedgerEX','LedgerAP','ReportLog','AuditTrail')
group by o.name,o.Type, s.name