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
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
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: 236