Query to check Tables row count and space details in SQL Server

The sp_spaceused stored procedure to retrieve the table size information:

EXEC sp_spaceused 'YourTableName';

Replace ‘YourTableName’ with the name of the table you want to check. This will return a result set with information about the table’s total size, data space, index space, and unused space.

The sp_spaceused stored procedure returns a result set with the following column headers:

name: The name of the table or indexed view.
rows: The number of rows in the table or indexed view.
reserved: The total amount of space reserved for the table or indexed view (in KB).
data: The amount of space used by the data in the table or indexed view (in KB).
index_size: The amount of space used by the indexes in the table or indexed view (in KB).
unused: The amount of space reserved for the table or indexed view, but not currently used (in KB).

Here’s an explanation of each column:

name: This column displays the name of the table or indexed view.
rows: This column displays the number of rows in the table or indexed view.
reserved: This column displays the total amount of space reserved for the table or indexed view. This includes the space reserved for data, indexes, and unused space.
data: This column displays the amount of space used by the data in the table or indexed view. This includes the size of all data pages used by the table, including any clustered or non-clustered indexes.
index_size: This column displays the amount of space used by the indexes in the table or indexed view. This includes the size of all index pages used by the table, including any clustered or non-clustered indexes.
unused: This column displays the amount of space reserved for the table or indexed view, but not currently used. This includes any space that has been allocated to the table or indexed view, but has not yet been filled with data.

Note that the space usage reported by sp_spaceused includes all indexes associated with the table or indexed view, as well as any other objects (such as triggers or constraints) that may be defined on the table.