Customization and programming tricks by Ronny Van der Snickt

Show the number of records and space for each database table.

I created a nice SQL query today to show the number of records in each table in a database and the space it takes in the database in megabytes.

Very handy to see why some MS CRM databases are so big.

declare @tmpTable TABLE ([RowCount] int, [TableName] nvarchar(max))
declare @ResultTable TABLE ([name] nvarchar(max),[rows] int, [reserverd] nvarchar(max),[data] nvarchar(max),[index_size] nvarchar(max),[unused] nvarchar(max))
insert
into @tmpTable ([RowCount], [TableName])
SELECT [RowCount] = MAX(si.rows), [TableName] = so.name
FROM sysobjects so, sysindexes si WHERE
so.xtype = ‘U’ AND si.id = OBJECT_ID(so.name)
GROUP BY so.name
ORDER BY 2 DESC

declare @cursor cursor, @tablename varchar(max)

set @cursor = cursor for
select [TableName] from @tmpTable
open @cursor
while 1=1

begin
fetch from @cursor into @tablename
if @@fetch_status <> 0 break

insert into @ResultTable
([name], [rows], [reserverd], [data], [index_size], [unused])

exec sp_spaceused @tablename

end

select [name], ROUND((CAST(REPLACE(data, ‘ KB’, ) as float) / 1024), 2)
as DataInMb, [rows],[reserverd], [data], [index_size], [unused]

from @ResultTable order by (CAST(REPLACE(data, ‘ KB’, ) as int)) desc

Write a comment