Omvang van alle tabellen opvragen met MS-SQL

Soms wordt je database groter zonder dat je er erg in hebt. Om een goed beeld te krijgen wat veel ruimte in beslag neemt is het handig een overzicht te maken hoeveel ruimte welke tabel in beslag neemt. Dit artikel geeft een toelichting hoe je de grootte van alle tabellen in je database kan opvragen. Dit artikel heeft betrekking op Microsoft SQL server 2008.

Het Script

Met behulp van onderstaand T-SQL script krijg je een overzicht van alle tabellen in je database. De output van het script is gesorteerd zodat de grootte tabellen bovenaan in je overzicht staan.

/* 
Script to display the size of all the tables in the database 
*/ 
declare @RowCount int
declare @tablename varchar(100) 
 
--Declare @Table to store the tables 
declare @Tables 
table ( 
PK int IDENTITY(1,1), 
tablename varchar(100), 
processed bit 
) 
 
-- Get all tables and insert into @Tables 
INSERT into @Tables (tablename) 
SELECT TABLE_NAME from INFORMATION_SCHEMA.TABLES where TABLE_TYPE = 'BASE TABLE' and TABLE_NAME not like 'dt%' order by TABLE_NAME asc 
 
--De declare table to store the space 
declare @Space table ( 
name varchar(100), rows nvarchar(100), reserved varchar(100), data varchar(100), index_size varchar(100), unused varchar(100) 
) 
 
--Get Table space for all tables and insert into @space 
select top 1 @tablename = tablename from @Tables where processed is null 
SET @RowCount = 1 
WHILE (@RowCount <> 0) 
BEGIN 
insert into @Space exec sp_spaceused @tablename 
update @Tables set processed = 1 where tablename = @tablename 
select top 1 @tablename = tablename from @Tables where processed is null 
SET @RowCount = @@RowCount 
END 
 
--Calculate space to display human format 
update @Space set data = replace(data, ' KB', '') 
update @Space set data = convert(int, data)/1000 
update @Space set data = data + ' MB' 
update @Space set reserved = replace(reserved, ' KB', '') 
update @Space set reserved = convert(int, reserved)/1000 
update @Space set reserved = reserved + ' MB' 
 
--Display the tables orders by size biggest first. 
select * from @Space order by convert(int, replace(data, ' MB', '')) desc 
 

Stored procedure aanmaken

Je kan van het bovenstaande script ook een “stored procedure” maken. Dit is vooral handig als je vaker een overzicht wilt opvragen van de grootte van de tabellen uit je database.

Download hier de Script GetTableSizes. Je kan dit script uitvoeren om een procedure uitvoeren met Microsoft SQL Management Studio, zorg wel dat je de juiste database gebruikt.

Nadat je het bestand “Procedure_GetTableSizes.zip” hebt gedownload, kan je de volgende stappen doorlopen om de Stored Procedure aan te maken:

1. Pak het bestand “Procedure_GetTableSizes.zip” uit.
2. Open Microsoft SQL database Studio, en login
3. Selecteer je database waar je de Stored Procedure wilt aanmaken, in het venter “Object Explore”
4. Klik in het menu File, Open, File in Microsoft SQL database Studio en open het bestand “Procedure_GetTableSizes.sql” (Welke je in stap 1 hebt uitgepakt.)
5. Klik vervolgens op Execute

Zie ook:

Stored procedure uitvoeren

Nadat de procedure is aangemaakt, kan je met de onderstaande opdracht de grootte van alle tabellen opvragen.

(klik op New Query) ik type de onderstaande opdracht in:

exec GetTableSizes;

Als alles goed is gegaan krijg je het volgende resultaat: