IntroductionIn Last article, I have explained very known SQL Server Error 25 And 87, here going to fully explain how to get database tables size in SQL server. Tables stores information in rows and columns in database. I am here explaining how to check tables storage in SQL database. This is very important to check database tables storage to optimise our application performance. Explaining all steps to check our database tables storage in single command. Here, you can see how to get all database tables size with detail.
Solution I (Shows usage one by one table)1. Open your SQL Server Management Studio and login into your database.
2. Now navigate to Query Analyser (Ctrl + N).
3. Then now write given T-SQL command select 'exec sp_spaceused ' + TABLE_NAME from INFORMATION_SCHEMA.TABLES where TABLE_TYPE = 'BASE TABLE' in query analyser.
4. Now copy all records from result window.
5. Again now open a New Query (Ctrl + N) Analyser and paste all records copied from result window.
6. Next select option from top toolbar Results to File (Ctrl + Shift + F).
7. Now hit button Execute (F5) from top toolbar to run T-SQL command to store all database tables storage stats into a report file (.rpt).
8. Now open file saved into your machine hard drive to check the database tables storage stats. This file contains given columns name (name of table), rows (total rows in table), reserved (reserved space), data (stored data size), index_size (total indexed size), unused (shows unused data).
Solution II (Consolidated Database Usage)1. Open your SQL Server Management Studio and Right click on Database.
2. Now navigate to Reports >> Standard Reports >> Disk Usage.