MSSQL get table size

Problem

Your database is growing and you need to figure what tables are the largest, and how many records/rows there are per table.

 

Solution

Open MSSQL SSMS
Connect to your sql server, select your database
Start a new query with the following

if OBJECT_ID(‘tempdb..#TablesSizes’) IS NOT NULL
drop table #TablesSizescreate table #TablesSizes (TableName sysname, Rows bigint, reserved varchar(100), data varchar(100), index_size varchar(100), unused varchar(100))declare @sql varchar(max)
select @sql = coalesce(@sql,”) + ‘
insert into #TablesSizes execute sp_spaceused ‘ + QUOTENAME(Table_Name,””) from INFORMATION_SCHEMA.TABLES–print (@SQL)
execute (@SQL)select * from #TablesSizes order by TableName

sql_table_size

Feel free to create a stored procedure that you can call easily.

 

Tested Platform

Windows 2012 R2
Windows 7
MSSQL 2014 Standard
MSSQL 2014 Express

Hits: 33

Leave a Reply