SQL - Query to list tables size columns count and rows count

In our daily professional life, sometimes we get the request to list down all the tables with total columns count, total rows count and occupied size on the disk by that particular table.

SQL Server provides many solutions to get this information. We can use inbuilt stored procedures such as sp_MSForEachTable and sp_SpaceUsed to pull table related information as requested above.

Hence, we are here to use a very simple SQL statement to pull this information as given below-

/* ------ SYS.TABLES returns a row for each user table in SQL Server. SYS.COLUMNS returns a row for each column of an object that has columns, such as views or tables. SYS.INDEXES contains a row per index or heap of a tabular object, such as a table, view, or table-valued function. SYS.PARTITIONS contains a row for each partition of all the tables and most types of indexes in the database. SYS.ALLOCATION_UNITS contains a row for each allocation unit in the database. SYS.SCHEMAS contains a row for each database schema.  */ Use Demo Go SELECT Sch.NAME AS SCHEMA_NAME, Tab.NAME AS TABLE_NAME, COUNT(Col.OBJECT_ID) AS COLUMN_COUNT, Part.ROWS AS ROW_COUNTS, CAST(ROUND((SUM(Aut.USED_PAGES) / 128.00), 2) AS NUMERIC(36, 2)) AS USED_MB, CAST(ROUND((SUM(Aut.TOTAL_PAGES) - SUM(Aut.USED_PAGES)) / 128.00, 2) AS NUMERIC(36, 2)) AS UNUSED_MB, CAST(ROUND((SUM(Aut.TOTAL_PAGES) / 128.00), 2) AS NUMERIC(36, 2)) AS TOTAL_MB FROM SYS.TABLES Tab INNER JOIN SYS.COLUMNS Col                  ON Tab.OBJECT_ID = Col.OBJECT_ID INNER JOIN SYS.INDEXES Ind                 ON Tab.OBJECT_ID = Ind.OBJECT_ID INNER JOIN SYS.PARTITIONS Part                 ON Ind.OBJECT_ID = Part.OBJECT_ID AND Ind.INDEX_ID = Part.INDEX_ID INNER JOIN SYS.ALLOCATION_UNITS Aut                 ON Part.PARTITION_ID = Aut.CONTAINER_ID INNER JOIN SYS.SCHEMAS Sch                 ON Tab.SCHEMA_ID = Sch.SCHEMA_ID GROUP BY                 Tab.NAME, Sch.NAME, Part.ROWS ORDER BY                 Sch.NAME, Tab.NAME

After running the above list, we will get list of all the tables with total columns count, total rows count and occupied size on the disk as given below –

DBName	SchemaName	TableName	ColumnCount	RowCounts	Used_MB	Unused_MB	Total_MB
Demo	dbo	tbl_SalesOrder	133	263	69.62	6.23	75.85
Demo	dbo	tbl_Customers	12565	41724326	30030.71	1305.83	31336.54
Demo	dbo	tbl_SalesPerson	35	1123	18.32	1.64	19.96
Demo	dbo	tbl_Product	12	94	0.28	0.56	0.84

 This query provides results within seconds. If you want to convert size of table in MB, GB or TB then you can do it very easily by modify it according to your requirement. This query is also helpful to provide the information about your data tables also.