Checking the size of your CRM tables

I'm sure this is posted a few other places, so I definitely cannot take the credit for writing this script, but this is a great way to see what entities in CRM are taking up the most space. If space ever becomes an issue, or you're just looking to clean your data a bit and make querying your data a little faster (due to less data to be sifted through), this is a good way to find where you should focus your attention.

Run this SQL script against the Organization data base (OrgName_MSCRM):

SELECT
sys.schemas.[name] AS [Schema],
sys.tables.name AS [Table],
COALESCE([Row Count].[Count], 0) AS [Rows],COALESCE(8192 * [Data Pages].[Count],0) AS [Data Bytes],
COALESCE(8192 * [Index Pages].[Count],0) AS [Index Bytes]
FROM sys.tables
INNER JOIN sys.schemas ON sys.schemas.schema_id = sys.tables.schema_id
LEFT OUTER JOIN (SELECTobject_id,
SUM(rows) AS [Count]
FROM sys.partitions
WHERE index_id < 2
GROUP BY object_id) AS [Row Count] ON [Row Count].object_id = sys.tables.object_id
LEFT OUTER JOIN (SELECTsys.indexes.object_id,
SUM(CASE WHEN a.type <> 1
THEN a.used_pages
WHEN p.index_id < 2
THEN a.data_pages
ELSE 0 END) AS [Count]
FROM sys.indexes
INNER JOIN sys.partitions AS p
ON p.object_id = sys.indexes.object_id
AND p.index_id = sys.indexes.index_id
INNER JOIN sys.allocation_units AS a ON a.container_id = p.partition_id
GROUP BY sys.indexes.object_id) AS [Data Pages]
ON [Data Pages].object_id = sys.tables.object_id
LEFT OUTER JOIN (SELECTsys.indexes.object_id,
SUM(a.used_pages - CASE
WHEN a.type <> 1
THEN a.used_pages
WHEN p.index_id < 2
THEN a.data_pages
ELSE 0 END) AS [Count]
FROM sys.indexes
INNER JOIN sys.partitions AS p
ON p.object_id = sys.indexes.object_id
AND p.index_id = sys.indexes.index_id
INNER JOIN sys.allocation_units AS a ON a.container_id = p.partition_id
GROUP BY sys.indexes.object_id) AS [Index Pages]
ON [Index Pages].object_id = sys.tables.object_id
ORDER BY sys.tables.[name]

Hope this comes in handy to someone else.

David Fronk
Dynamic Methods Inc.