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.

⌛Lets learn more on the data validation side which is the most important part of the data engineering.

Data validation — Data validation is the process of checking the data against predefined rules and standards, such as data types, formats, ranges, and constraints.

💫Schema Validation: Verify data adherence to predefined schemas, checking types, formats, and structures.

💫Integrity Constraints: Enforce rules and constraints to maintain data integrity, preventing inconsistencies.

💫Cross-Field Validation: Validate relationships and dependencies between different fields to ensure logical coherence.

💫Data Quality Metrics: Define and track quality metrics, such as completeness, accuracy, and consistency.

💫Automated Validation Scripts: Develop and run automated scripts to check data against predefined rules and criteria.

 

To learn more, please follow us -
🔊 http://www.sql-datatools.com

To Learn more, please visit our YouTube channel at —
🔊 http://www.youtube.com/c/Sql-datatools

To Learn more, please visit our Instagram account at -
🔊 https://www.instagram.com/asp.mukesh/

To Learn more, please visit our twitter account at -
🔊 https://twitter.com/macxima