How to get the list of tables in a database and also the size of which each table occupies?

Answer:

This is very intresting question where we need to understand on getting the complete list of tables in database is quite easy.

Syntax

USE SAMPLEDATABASE

SELECT * FROM SYS.TABLES

This query will return the complete list of tables in the selected Database.

Now coming to the second part by indentifying the total size of the table that it occupied. Here it is.

Syntax:

EXEC SP_USEDSPACE "Your Table Name goes here"

The above query will return number of rows in the table, Space reserved, actual space occupied and finally the free un-used space in the table.