Reference sql2012 stored procedures write statistics for all user table size examples _mssql

You can combine sp_msforeachdb to iterate through all the user databases to see the size of all the tables, note that its parameters @sql not exceed nvarchar (2000), where the code is not posted.
You can also run periodically and save the results to see trends in data changes.

To query the size of all user table sizes for a single database:

Copy Code code as follows:

Select @ @servername as Servername,db_name () as dbname, object_id as ObjectID, Schema_name (schema_id) as Schname, name as T Ablename
, rowcnt as Rows,columns,indexes,rowlength
, RESERVEDKB, TABLEUSEDKB
, usedkb-tableusedkb as indexusedkb,reservedkb-usedkb as UNUSEDKB
, create_date as createdate,modify_date as LastModifiedDate, getUTCDate () as Trackingutctime
From
(SELECT
object_id
, schema_id
, name
, (Select Max (row_count) from Sys.dm_db_partition_stats p with (NOLOCK) where p.object_id=t.object_id and p.index_id < 2 ) as Rowcnt
, (Select Count (1) from Dbo.syscolumns with (NOLOCK) where id = t.object_id) as Columns
, (Select Count (distinct index_id) from Sys.dm_db_partition_stats p with (NOLOCK) where p.object_id=t.object_id) as Indexes
, (SELECT SUM (length) from Dbo.syscolumns with (NOLOCK) WHERE id = t.object_id) as Rowlength
, IsNull (Select SUM (reserved_page_count) from Sys.dm_db_partition_stats p with (NOLOCK) where p.object_id=t.object_id) , 0) *8
+ IsNull (Select sum (reserved_page_count)
From Sys.dm_db_partition_stats P2 with (NOLOCK)
INNER JOIN Sys.internal_tables it with (nolock) on p2.object_id = it.object_id
WHERE it.parent_id = t.object_id
and It.internal_type in (202,204,207,211,212,213,214,215,216,221,222,236)), 0 * 8 as RESERVEDKB
, IsNull (Select SUM (in_row_data_page_count + lob_used_page_count + row_overflow_used_page_count)
From Sys.dm_db_partition_stats P with (NOLOCK) where p.object_id=t.object_id and p.index_id < 2), 0) * 8 as TABLEUSEDKB
, IsNull (Select SUM (used_page_count) from Sys.dm_db_partition_stats p with (NOLOCK) where p.object_id=t.object_id), 0) * 8
+ IsNull (Select sum (used_page_count)
From Sys.dm_db_partition_stats P2 with (NOLOCK)
INNER JOIN Sys.internal_tables it with (nolock) on p2.object_id = it.object_id
WHERE it.parent_id = t.object_id
and It.internal_type in (202,204,207,211,212,213,214,215,216,221,222,236)), 0 * 8 as USEDKB
, Create_date
, Modify_date
From Sys.tables T with (NOLOCK)
where type= ' U '
) A
ORDER BY reservedkb Desc